Using a Ref Cursor in Oracle procedure to return results, seleced from a "global temp table" makes the cursor INVALID if a COMMIT is issued before the procedure returns. Any suggestions?
create procedure ...
select * from temp_table;
What kind of "global temporary table" u use:
-- session or transaction specific.
May be problem in type of GTT.
A transaction-specific temporary table allows only one transaction at a time. If there are several autonomous transactions in a single transaction scope, each autonomous transaction can use the table only as soon as the previous one commits.
In other words, there are two kinds of global temporary tables, depending on how the GTT is created.
If you create GTT table with the command
CREATE GLOBAL TEMPORARY TABLE ..... ON COMMIT DELETE ROWS;
then your rows in that table will be visible only untill your transaction lasts. As soon as you perform a COMMIT, your rows will "dissapear" from that GTT.
On the other hand if you use
CREATE GLOBAL TEMPORARY TABLE ..... ON COMMIT PRESERVE ROWS;
then your rows in the GTT will be visible to you for the duration of your session. They will "disapear" only when your session ends.
If you dont specify "ON COMMIT DELETE/PRESERVE ROWS", then the default is "ON COMMIT DELETE ROWS".
You can check what kind of GTT you have from DBA/ALL/USER_TABLES.DURATION. If the value in that column is 'SYS$TRANSACTION', then the scope of the changes is transaction. If it is 'SYS$SESSION', then the scope is session.
In your case, I belive you have a GTT defined as "ON COMMIT DELETE ROWS". So as soon as you perform a COMMIT the rows from GTT dissapear, so it is natural that your ref cursor becomes invalid. Change the duration of the rows in your GTT by recreating it with"ON COMMIT PRESERVE ROWS" and your ref cursor will beghave normally.
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Yes, its a Transaction GTT. Thanks for the explanation and resolution!!!
Click Here to Expand Forum to Full Width