One Star

Database transactions in oracle

Hello!
In a job, i need to insert into three oracle database tables using a tMap. At the moment I'm using tOracleOutput - components which are independent from each other (without tOracleConnection) and a "commit every" set to 1000 for each component.
But when I try to run the job, at least before the third table the job "hangs" (without any error message) until I stop it manually. I guess this is because evry component opens a new connection to the database and these connections are locking each other.
I don't want to use tOracleConnection/ tOracleCommit/tOracleRollback because the component manual says: "
"Allows to commit a whole job data in one go to the output database as one transaction when validated."
I don't want to commit the whole job data in one go, because each csv input file could be up to 500 megabytes big. I would prefer a commit to be performed for each input line.
Any ideas?
Thank you in advance,
Stefan
3 REPLIES
One Star

Re: Database transactions in oracle

Hi Stefan,
I would say you should use a tOracleConnection and a tOracleRow in tOracleRow you could make a COMMIT. Depending on your job design you could execute the COMMIT for each input line.
Hope this helps.
Bye
Volker
Employee

Re: Database transactions in oracle

Hello All,
tOracleCommit can simply be used as an usual component, just after tOracleOutput.
Regards,
One Star

Re: Database transactions in oracle

Hi,
I have got a similar kind of problem.
I am fetching data from a database and want to move the data to other two different databases. I need to start a transaction before the the data movement starts and complete the tranction after it is successfull in both of the output databases.
The size of data is not big.
Can any body suggest how to accomplish it.

Thanks and Regards,
Pravu Mishra.