[resolved] tOracleSP throws error and job terminates

One Star

[resolved] tOracleSP throws error and job terminates

Hi,
When an Oracle stored procedure is called from a job and the stored procedure returns an error (a java.sql.SQLException) the job is terminated.
There is no option to prevent this "Die on error" behavior, like there is on Oracle output and other components.
Is there any other way to log these errors and just continue the job "as if nothing happened" ?
Regards,
Arno

Accepted Solutions
One Star

Re: [resolved] tOracleSP throws error and job terminates

Hi Shong,
Yes, I know I have to migrate the component. Therefore I made a copy of the component and gave it a new name. I have a shared custom components folder where all our custom components reside and I've placed this component in this folder.
So now it's available to all other co-workers and I don't have to do anything on migration to a new version.
Regards,
Arno

All Replies
Community Manager

Re: [resolved] tOracleSP throws error and job terminates

Hi Arno
Yes, there is no 'die on error' option on tOracleRow component, to get your request works, you need move tOracleRow to a child job, and use a tRunJob to call the child job, uncheck the box 'die on error' on tRunJob.
In the child job, use tLogCather component to capture the exception and log them into a file or database.
Let me know if you have any doubts!
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] tOracleSP throws error and job terminates

Hi Shong,
Thanks for your quick reply.
I'm passing a couple of parameters in the Oracle Stored Procedure. These parameters are on a row (from a tMap). This row can be connected to a tRunJob, but in this subjob I do not see the input row (as I would in a joblet).
Can you explain to me how I should read these parameters from the parent job?
Thanks!
Best regards,
Arno
Community Manager

Re: [resolved] tOracleSP throws error and job terminates

Hi
Using the context variables to pass the parameters from parent job to child job. see
http://www.talendforge.org/forum/viewtopic.php?id=1654
The job looks like:
parent job:
....tMap---tFlowToIterate---iterate--tRunJob
child job:
tFixedFlowInput--main--tOracleSP
tLogcather--main--tFileOutputDelimited
on tRunJob: uncheck the box 'die on error', pass the each row to child job using context variables.
on tFixedFlowInput: generate each row sent by parent job.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] tOracleSP throws error and job terminates

Hi,
I haven't tried this option yet (I am about to do so), but it seems to me that this solution will not be a very fast one, as my job has to process a couple of thousand records.
Is there a technical reason that the tOracleSP doesn't support the "Die on error" functionality, or is it just not there for no specific reason?
Regards,
Arno
One Star

Re: [resolved] tOracleSP throws error and job terminates

Hi,
Just to let you know, and for all other users who come across the same issue:
I've decided not to use the subjob solution. Instead I changed the tOracleSP component to support a "die on error" functionality.
This was in fact a quite easy job. I just added the parameter in the xml file for the component (the same way it is done with a tOracleOutput) and changed the statement_<%=cid%>.execute() statement on line 238 of tOracleSP_main.javajet, into the following code:
try {
statement_<%=cid%>.execute();
} catch(Exception e) {
<%
if (("true").equals(dieOnError)) {
%>
throw(e);
<%
} else {
%>
System.err.print(e.getMessage());
<%
}
%>
}

Oh, and at line 42 of the same file (tOracleSP_main.javajet) I added
    String dieOnError = ElementParameterParser.getValue(node, "__DIE_ON_ERROR__");

Regards,
Arno
Community Manager

Re: [resolved] tOracleSP throws error and job terminates

Hi Arno
Yes, I agree with you that iterating each row and pass it to a child job has a bad performace for a large of data. Of course, it is better if you can modify the component by yourself and add the 'die on error' option, good job! One thing need to note is you have to migrate also this component to new studio if you will upgrade to new version in future.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] tOracleSP throws error and job terminates

Hi Shong,
Yes, I know I have to migrate the component. Therefore I made a copy of the component and gave it a new name. I have a shared custom components folder where all our custom components reside and I've placed this component in this folder.
So now it's available to all other co-workers and I don't have to do anything on migration to a new version.
Regards,
Arno
Community Manager

Re: [resolved] tOracleSP throws error and job terminates

Hi
Glad to see the clever solution, make a copy of the component and give it a new name, regard it as a custom component. Smiley Wink
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] tOracleSP throws error and job terminates

Hi,
Yeah, I tried to keep the same name, and hoped it would override the original component, but unfortunately this didn't work Smiley Wink
Regards,
Arno