Global Temp Tables, REF CURSORs and COMMIT
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Global Temp Tables, REF CURSORs and COMMIT

  1. #1
    Join Date
    Nov 2001
    Posts
    15
    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 ...
    ...

    OPEN po_resultcur
    FOR
    select * from temp_table;

    ...

    COMMIT;

    end;

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.


  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.

    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Nov 2001
    Posts
    15
    Yes, its a Transaction GTT. Thanks for the explanation and resolution!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width