Re: Temporary Table and Ref Cursor

Try it without the temp table. I have yet to find a place in Oracle where I needed a temp table and I've been at this awhile. Jim
"Todd" <todd.bush_at_canopysystems.com> wrote in message news: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 Wed Apr 03 2002 - 07:10:45 CEST