Re: Temporary Table and Ref Cursor

From: Todd <todd.bush_at_canopysystems.com>
Date: 2 Apr 2002 10:34:23 -0800
Message-ID: <602cd8a.0204021034.7e8642da_at_posting.google.com>


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

Original text of this message