One Star

tOracleConnection commits and closes before tOracleCommit

Hi,
i am trying to insert fields from text file to database tables. In the job (screenshot attached) I get error at "DISTRICT" tOracleOutput componenet that my connection is closed. Maybe there is something wrong with my architecture, or is there something that i dont know about other components that might close a tOracleConnection? After first tOracleOutput ("ELDERSHIP") the database table should not be commited, as i asume, but when i check the database it has records inserted to it, even though my job didnt get to tOracleCommit component yet. Do ytou have any ideas what might cause the commit and closing of my connection?
7 REPLIES
Four Stars

Re: tOracleConnection commits and closes before tOracleCommit

There could be reasons but based on your job flow. Usually I use and recommend following structure
- Use tPreJob - create database connection (disable autocommit)
- Your main job goes in between
- use tPostJob - Commit and close connection
Vaibhav
One Star

Re: tOracleConnection commits and closes before tOracleCommit

Did that, but i get the same problem. the thing is that talend somehow has infinite loop on the "writing to eldership table" block, and it keeps on repeating forever if i remove commit and close connection. Any ideas on why it loops through this last block? if i add another identical block that write to another table, it then loops the last block. What may cause this? i dont have any itterate rows in my job so i dont really get why it does that
Four Stars

Re: tOracleConnection commits and closes before tOracleCommit

I think in your job, you are missing OnSubJobOk links... OnComponentOk links are not used much during the job execution...
Please review your design and consider option of OnSubJobOk link when one particular activity is completed. then proceed to another block for job execution.
Vaibhav
One Star

Re: tOracleConnection commits and closes before tOracleCommit

Thank you for a quick reply. I changed my job structure a bit, according to your comment, but it still loops through last subjob. here is some screenshots. is my job design correct? am i using the "OnSubJobOk" link properly?
Four Stars

Re: tOracleConnection commits and closes before tOracleCommit

Verify your close connections... have you used tPreJob and tPostJob for connection and commit - close respectively?
Remove close connection from all the places... and have it only in tPostJob section.
Vaibhav
One Star

Re: tOracleConnection commits and closes before tOracleCommit

I removed all the checkboxes from rollback components, and added pre and post jobs as you described. yet the same problem persists. The problem is not in the closed connection, but ithe fact that the last block that writes the streets is repeating.
Four Stars

Re: tOracleConnection commits and closes before tOracleCommit

Try use the debug run with traces, and try to identify why it is happening...
Write down flow of expectations of data movement and see logically if it is happening... I find two flows are connected to single component with oncomponent link...
Put your updated screenshot and try to identify why it is repeating
Where is street block in diagram..?
- you can think of adding tJava onsubjobok or on some condition to identify completion of one stage and moving to another..