0

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.

Kailash
  • 53
  • 8
  • Why don't you use nested tables instead of temporary tables? This way you don't have to worry about transactions and also might be faster as there is no context switch between pl/sql and sql engine? – Radagast81 Aug 19 '19 at 12:27
  • Temporary table is used as there are multiple sessions (instances I mentioned above) all operating on same set of tables. With temporary tables, I don't have to worry about data getting affected by other session. To handle this in nested tables, I will have to add extra clauses, no? – Kailash Aug 19 '19 at 12:54
  • @Radagast81 - by *"nested tables"* I presume you mean PL/SQL collections. Those are session memory objects and so it seems no more likely to permanently persist the data as the OP requires. – APC Aug 19 '19 at 12:55
  • Yeah i mean PL/SQL collections. They persist in the package even after commit/rollback as long as the session is alive and restrict to the current session so you don't have to worry about being affected by another session. I don't know how perminent the data is needed but usually till the session terminates is sufficient. – Radagast81 Aug 19 '19 at 13:01
  • @Kailash - please clarify. Are your GTTs defined as ON COMMIT PRESERVE or ON COMMIT DELETE? If PRESERVE why do you lose the data? Is it because the failure of your process terminates the sessions? – APC Aug 19 '19 at 13:16
  • @APC , rollback will cause gtt data to be flushed. – Popeye Aug 19 '19 at 13:59
  • 2
    @tejash - if the GTTs are defined as ON COMMIT PRESERVE ROWS *and* the process issues commits immediately after populating the GTTs before starting any transactional work, then I believe the GTTs will still have data in them after the transaction abends and issues a rollback. That's why we need to know the details of what the OP is doing. – APC Aug 19 '19 at 14:07
  • Gtt are defined as on commit preserve rows. Also, these are used multiple C functions of same executable and db procedures and there are loads of sqls executed through both C codee and procedures. So having a single begin-end block is not feasible. – Kailash Aug 19 '19 at 14:51
  • Once rollback is issued, data is flushed from gtt.. as Tejash has mentioned. I need to check in code if commit is issued after gtt is populated or not. – Kailash Aug 19 '19 at 15:03

2 Answers2

0

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
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • I think gtt will not show any data in autonomous transaction. – Popeye Aug 19 '19 at 14:06
  • 1
    @Tejash - not necessarily. The transaction could select the contents of the GTTs into PL/SQL collections and pass arrays to a procedure which populates a permanent table within an autonomous transaction. Although I'm not a big fan of such an approach: it feels like too many moving parts in what is basically an exception handler. But I suppose that's because the OP's architecture is somewhat brittle to begin with. – APC Aug 19 '19 at 14:10
  • Nice!! Seems good workaround but answer must include it and remove the `select * from gtt` – Popeye Aug 19 '19 at 14:12
  • Referring back to APC's comment about needing to know the details: the GTT data would be visible to the autonomous procedure if its insert was committed (prior to whatever caused the rollback, obviously) too. Even after the rollback is then issued. Slightly less convoluted, but still might not be practical for the OP. – Alex Poole Aug 19 '19 at 14:16
  • I'm tempted to try this autonomous procedure approach, but that will only happen tomorrow. I'll update if it worked or not. I was initially sceptical whether autonomous transaction will be able to see data in gtt, but it looks like it would be.. No harm in trying a sample code to see if it works or not. Thanks – Kailash Aug 19 '19 at 14:57
  • Ok, so I see 2 approaches - 1) Session table’s data is visible to autonomous transaction if session is committed post insert/update in session table. 2) Session table populated in an autonomous transaction is visible to job's session if autonomous transaction is committed. First point is difficult to achieve as it will mean partial commits are issued and will incur overhead of reverting back changes in case business transaction is to be rolled back. Second one is tedious but achievable.. I'll can progress on those lines.. Thanks all for your response. – Kailash Aug 20 '19 at 07:22
  • @Kailash - you only have to have committed the GTT data; if you can populate and commit that before you make any changes to your real table data then the autonomous transaction will see the GTT (but not other changes) and you can still roll back the real-table changes from your exception handler.. – Alex Poole Aug 20 '19 at 08:06
  • Yes, I understand that. But to populate and commit GTT before changes to real table data is not feasible as there is mix of insert/update of GTT and other tables. Hence I thought it would be better to have autonomous transaction to insert/update GTT. Autonomous transaction will issue commits after GTT is populated/updated which will not hamper changes to other tables, hence I thought it would be preferable approach for the task at hand. – Kailash Aug 20 '19 at 08:28
0

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!!

Popeye
  • 35,427
  • 4
  • 10
  • 31
  • Hhmmm.. I guess that will be bit of an overkill.. let me see if other approach works or not. Thanks. – Kailash Aug 19 '19 at 14:58