Temporary Table and Ref Cursor

Ok. This is the first time I've tried to use temporary tables and I am having a problem with "On Commit Delete Rows".

I am creating my temporary table like so:

    CREATE GLOBAL TEMPORARY TABLE pattest (     name_test VARCHAR(20))
    ON COMMIT DELETE ROWS; My package looks like this:

create or replace package pattestpack is

  type retCursor is REF CURSOR;   

  PROCEDURE getRows (in_val IN varchar2, curOut OUT retCursor, out_name OUT VARCHAR2);

end pattestpack;

create or replace package body pattestpack is   PROCEDURE getRows (in_val IN varchar2, curOut OUT retCursor, out_name OUT VARCHAR2) IS

  BEGIN            INSERT INTO pattest (name_test)
    VALUES (in_val);     

    SELECT name_test INTO out_name FROM pattest;     

    OPEN curOut FOR
      SELECT * FROM pattest;

    COMMIT;   END getRows;   

end pattestpack;

The problem is that if I commit after opening the ref cursor, the cursor is wiped out by the commit. If I put the commit at the beginning of the procedure, everything works great. Is this typical?

In my specific situation, putting the commit at the begninning is not good because I am using the proc from VB.Net and I think that it is autocommiting after calling the proc, thus, blowing away the cursor before I have a chance to do anything with it.

Any help would be appreciated!

Temporarily, I am just using on commit preserve rows and then I am just doing a delete from pattest. Received on Tue Apr 02 2002 - 16:51:31 CEST