[resolved] tOracleSP throws error and job terminates

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Hi
Using the context variables to pass the parameters from parent job to child job. see
https://community.talend.com/t5/Design-and-Development/Passing-context-parameters-in-a-tRunJob/td-p/...
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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous · Accepted solution]

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

[Anonymous]

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.
Best regards
Shong

[Anonymous]

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