Handling data in global temporary tables in case of transaction rollback

Question

I've a job which runs with multiple instances i.e. the code base for all instances is same, but each instance works on set of data allocated to it so as to achieve parallelism and better throughput for the application. These jobs use global temporary table for working through the data as there are multiple complex operations performed before final output is computed. In case of failure, the transaction is rolled back (as it should), but with this I'm also losing the data in gtt.

Is there a way that the records in gtt can be copied over to another permanent table while rolling back the transaction. I know it sounds weird, but this is a practical problem I'm facing. I need to somehow store data in session table in case of failure of any sql, while rolling back the transaction as one of the sql has failed.

Thanks.

Answer 1

Hm, maybe something like this:

• create a permanent table which will hold GTT data in case of failure
• create an autonomous transaction procedure which would insert into permanent select * from gtt and commit
• in exception handler section call that procedure and then rollback

Answer 2

The only way is printing the required data before your rollback.

You can use UTL_FILE to store data in the file. Later, you can use external table concept of oracle to retrieve data in the table.

Cheers!!