I would like to know if there is a component to pause the job while waiting for a line to be inserted in the database?
Indeed, I have developed two jobs. Between the execution of these two jobs, there is a PL/SQL processing that is done in the database. Once this processing is finished, I execute the second job. This is the way the process is currently done.
I was just wondering if it was possible to optimize this process by avoiding the back and forth between the two jobs.
You may need to tweak that a little to ensure you get the outputs that you want. You could also wrap the whole thing in a stored procedure and use a tOracleSP component. But this knowledge base article should give you an idea as to how to do this with the tOracleRow....
You can certainly build this in to your job. I am curious as to why this would be needed, but I know that I do not know everybody's requirements 🙂
Since I don't fully understand the requirement here, can I ask precisely what you require? Is it.....
a) To be able to run a job with 100 rows of data (for example), that needs to process each row independently and wait for the SQL process and then the second job to do something before the next row is run in the first job?
b) To be able to run a job with 100 rows of data (for example), that needs to be run completely, wait for the SQL to process all data, then run the second job?
Whichever way it is, you can do this. But you may not need two separate jobs OR you may be able to do it with a child job as your second job.
Hello @Richard Hall, thank you for your interest in my question.
The current process is as follows :
1) Integration of data from one or more files (contained in a folder) into a database. Each one has an identifier. This is done by my Talend job #1.
2) Data processing (in PL/SQL) of each file in the database. The result of the processing is a return code to indicate if a print is to be done (in Talend) or not. The return code is stored in the database for the Talend job to retrieve.
3) The files whose return code (to be recovered by the Talend job in the database) indicates that a summary of the processing should be printed in XML. This is done by my Talend job #2.
So I would say that my situation corresponds to your point b).
So the process requires the execution of two separate jobs. One to integrate the data, the other to print the summary of a data processing.
Even if the current process works, I'm curious if there was a component or a way to do it that allows me to integrate data, pause the job until it detects a line in the database (the one that contains the return code) to execute the next job. This is a scenario I can imagine, but if there are other methods, I'm open.
There is the tSleep component, but I would advise against thinking about this in this way. Leaving an arbitrary amount of time between two events may work in some situations sometimes, but is not guaranteed to work. Sometimes you will be leaving it longer than required (granted, not so much of an issue), but other times you will not leave enough time. The best way of doing this is to run the PL/SQL via Talend. Doing this will mean that the next part of the job will not run until the PL/SQL has completed.
It sounds like a good flow for your job may be something like this.....
Thanks for your answer, it's a very good idea to test. However, I have never tested the launch of a procedure contained in a package from a tDBRow. I have already tested this component with all DML queries, with success.
But can a PL/SQL block work? For example to run my PL/SQL processing in Oracle :
You may need to tweak that a little to ensure you get the outputs that you want. You could also wrap the whole thing in a stored procedure and use a tOracleSP component. But this knowledge base article should give you an idea as to how to do this with the tOracleRow....
