Re: Temporary Table and Ref Cursor

I'm having the same problem. ON COMMIT PRESERVE ROWS works fine, but the data will still be in the table until the session ends. Its is acceptable to issue a delete from tempTable after getting my rows?

Thanks

pwalessi1_at_hotmail.com (Pat Alessi) wrote in message news:<5de67752.0204020651.3c9936d4_at_posting.google.com>...
> 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 - 20:34:23 CEST