Maintaining a oracle session across different OCI stage/Job

naveen_kumar · Post by **naveen_kumar** » Tue Oct 25, 2005 6:39 am

Hi all
Can anyone guide me how to maintain a single oracle session across the OCI stages in different jobs, In other words I want to handle a single transaction across different sessions. I am doing transaction handling of 1000 records but my jobs writes only 500 records I don't want those records to be reflected in database till all the 1000 records are written, but what happens is that after completion of job iam able to see those 500 records in database.
What i feel is that after completion of job the transaction is committed.

______
Naveen

chulett · Post by **chulett** » Tue Oct 25, 2005 6:49 am

naveen_kumar wrote:Can anyone guide me how to maintain a single oracle session across the OCI stages in different jobs

Can't be done. When the job finishes, any uncomitted records at that point are comitted. Period.

You'll either need to find a way to do what you are doing all in one job or live with the 'intermediate' commits between jobs.

rleishman · Post by **rleishman** » Tue Oct 25, 2005 6:06 pm

I don't think it's a good idea in your case, but with Oracle 10g you can perform point-in-time SELECTs (I think they are called FLASHBACK). They rely on the Archive/Redo log functionality in Oracle that supports backups.

Or, you could write all of your results to files and then load them afterwards.

kcbland · Post by **kcbland** » Tue Oct 25, 2005 7:28 pm

You can't span transactions across separate OCI stages, much less OCI stages across jobs in one single commit. The best method left to you is to load work tables and then use an SP call to merge the data and commit.