Insert into global temporary table operation failed

1
votes

A select statement to store my temporary table data to a plsql table type:

select * from Global_temporary_table
bulk collect into plsql_table_type;

After doing some conversion, when i try to insert the data back to the Global_temporary_table table using below insert statement

INSERT INTO Global_temporary_table
VALUES v_rbct_tbl(i);
COMMIT;

it fails to complete the insert transaction. However, if i try to insert same data into non-global temporary table, it doesn't create any problem.

Help me out please!

1
sqloracleplsql
"it fails to complete the insert transaction" : do you mean the transaction fails explicitely (please share the error message you may receive), or is it that your data is not inserted? Or corrupted? – RandomSeed
I didnt get any error as such but as explained by Florin, the commit was the mistake i was doing, because of that all the data from GTT was being deleted. Anyways, Thanks for your concern. – Deepak Ranjan

1 Answers

2
votes

Almost sure your commit deletes the data. The data in a GTT is volatile and dissapears either after commit(ON COMMIT DELETE ROWS) or after the session ends(ON COMMIT PRESERVE ROWS).

If you need the data to stay after commit, create the table with ON COMMIT PRESERVE ROWS.

See the docs