topic Re: [resolved] tOracleSP throws error and job terminates in Talend Studio

[resolved] tOracleSP throws error and job terminates

Anonymous — Sat, 16 Nov 2024 12:49:44 GMT

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

Re: [resolved] tOracleSP throws error and job terminates

Anonymous — Tue, 05 Jul 2011 10:31:42 GMT

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

Re: [resolved] tOracleSP throws error and job terminates

Anonymous — Tue, 05 Jul 2011 14:47:29 GMT

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

Re: [resolved] tOracleSP throws error and job terminates

Anonymous — Wed, 06 Jul 2011 02:47:48 GMT

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/66305
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

Re: [resolved] tOracleSP throws error and job terminates

Anonymous — Wed, 06 Jul 2011 06:48:18 GMT

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

Re: [resolved] tOracleSP throws error and job terminates

Anonymous — Wed, 06 Jul 2011 09:49:01 GMT

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

Re: [resolved] tOracleSP throws error and job terminates

Anonymous — Wed, 06 Jul 2011 10:14:06 GMT

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

Re: [resolved] tOracleSP throws error and job terminates

Anonymous — Wed, 06 Jul 2011 10:38:20 GMT

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

Re: [resolved] tOracleSP throws error and job terminates

Anonymous — Wed, 06 Jul 2011 12:53:06 GMT

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

Re: [resolved] tOracleSP throws error and job terminates

Anonymous — Wed, 06 Jul 2011 13:17:52 GMT

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

Re: [resolved] tOracleSP throws error and job terminates

Anonymous — Tue, 13 Nov 2018 06:30:17 GMT

I tried the solution but i am not getting the "DieOnError" option to either check or uncheck. Please suggest me how I can disable the DieOnError option

Re: [resolved] tOracleSP throws error and job terminates

MPatidar1649990424 — Sat, 07 Jan 2023 06:39:03 GMT

Owesome!! it worked, thanks to your post