How to capture ORA- errors into a file or table

I am trying to find out how to capture Oracle error, like Unique Key constraint violation error eg: ORA-00001: unique constraint (SHICKL.ROW_UID_PK) violated.
I need to capture above mentioned error in to a file. Right now the above error can be visible in RUN area or execution area.
Please suggest me how do catch the ORA error in Talend in a file or table
Thanks,
Vivek Jayapalan
11 REPLIES
One Star

Re: How to capture ORA- errors into a file or table

We can use the Row Rejects link on the tOracleOutput component to capture the row errors. This will work only in a non-batch mode.
tFileInputDelimited_1 --(row1:Main)--> tOracleOutput_1 --(row2:Rejects)--> tFileOutputDelimited_1
Here row2 is a rejects (Row: Rejects) link from tOracleOutput_1 to tFileOutputDelimited_1.
Regards,
Abhi

Re: How to capture ORA- errors into a file or table

Hello Abhi,
There is no option for reject link from toracleoutput.
tfileinputdelimited ---> row(Main)---> toracleoutput --->row(Main)<Only row main is available> --> tfileoutputdelimited

What i could fina an option was the below method, But error_code and error message is coming null, but i can see the ORA error in the run screen.
tfileinputdelimited ---> row(Main)---> toracleoutput
|
|
|------> row(Reject)---> tfileoutputdelimited
Thanks,
Vivek Jayapalan

Re: How to capture ORA- errors into a file or table

Hello Abhi,
There is no option for reject link from toracleoutput.
tfileinputdelimited ---> row(Main)---> toracleoutput --->row(Main)<Only row main is available> --> tfileoutputdelimited

What i could fina an option was the below method, But error_code and error message is coming null, but i can see the ORA error in the run screen.
tfileinputdelimited ---> row(Main)---> toracleoutput
|
|
|------> row(Reject)---> tfileoutputdelimited
Thanks,
Vivek Jayapalan

Re: How to capture ORA- errors into a file or table

Hello Abhi,
Your Idea is working fine, i have removed the Batch option from the tOracleoutput, then i could see the Reject row link.

Thanks a lot...
Vivek Jayapalan
One Star

Re: How to capture ORA- errors into a file or table

Hi,
I am working Talend DI version 5.0.1 and i want to capture the rejected rows from tOracleOutPut to a tFileOutPutDelimited.
But i am not able to see the reject option on tOracleOutPut. Can anyone please help me on this?
Many Thanks,
Manju
One Star

Re: How to capture ORA- errors into a file or table

Go to advanced settings and deselect Use Batch Size.
One Star

Re: How to capture ORA- errors into a file or table

Thanks a lot , it worked !!!
One Star

Re: How to capture ORA- errors into a file or table

Thanks - that s what I need
We can use the Row Rejects link on the tOracleOutput component to capture the row errors. This will work only in a non-batch mode.
Go to advanced settings and deselect Use Batch Size.

ps ? about speed - seems it will be slowly
One Star

Re: How to capture ORA- errors into a file or table

Hi !
Can we use this logic for tgreenplumrow ?
tgreenplumrow->reject
|
oncomponentok
|
tgreenplumcommit

Re: How to capture ORA- errors into a file or table

Hi Team,
Still rowreject count is zero. after advanced settings and deselect Use Batch Size..
Please any suggestion.

Thanks
shridhar
Three Stars

Re: How to capture ORA- errors into a file or table

Create a error table with the same structure of the table with datatype of all columns as varchar2(2000) and then add 2 more columns ERRORCODE and ERRORMESSAGE again same datatype.

 

Add this into Talend job and then from the main table link this table with reject connector.

 

This works even in batch mode, I tested it. It will capture all error records including the error code and error message into the newly created error table.

 

Hope this helps.