CODEWITHSUNDEEP
sqloracle-databaseplsql
Deepak Ranjan
Deepak Ranjan

Reputation: 31

Insert into global temporary table operation failed

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!

Upvotes: 1

Views: 2090

Answers (1)

Florin Ghita
Florin Ghita

Reputation: 17643

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

Upvotes: 2

Related Questions