Probably I ask for the impossible, but I'll ask anyway.

Is there an easy way to select from one Oracle session and then insert/commit into another?

(I guess, technically it could be done with pl/sql procedure calls and PRAGMA AUTONOMUS Transactions, but it would be a hassle)

I have the following scenario:

I run some heavy calculations and update / insert into some tables. After the process is completed I would like to 'backup' the results (create table as select or insert into another temp table) and then rollback my current session without loosing the backups.

Here is desired/expected behavior:

Oracle 11g

insert into TableA (A,B,C) values (1,2,3);

select * from TableA 

Result:    1,2,3

create table [in another session] TempA 
as select * from TableA [in this session];

rollback;

select * from TableA;

Result null
select * from TempA;

Result 1,2,3

Is this possible?

CodePudding user response：

Is there an easy way to select from one Oracle session and then insert/commit into another?

Create a program in a third-party language (C , Java, PHP, etc.) that opens two connections to the database; they will have different sessions regardless of whether you connect as different users or both the same user. Read from one connection and write to the other connection.

CodePudding user response：

you can insert your "heavy calculation" into a Oracle temp Table .

CREATE GLOBAL TEMPORARY TABLE HeavyCalc (
  id           NUMBER,
  description  VARCHAR2(20)
)
ON COMMIT DELETE ROWS;

the trick is that when you commit the transaction all rows are deleted from temporary table.

Then you first insert data into the temp table, copy the result to you backup table and commit the transaction.