Re: Temporary Table and Ref Cursor

I can't find a more elegant way to do what I'm trying to do, without using temp tables. Basically I need to be able to add records from a bunch of different cursors at run time. I did it using the Oracle table data structure in PL/SQL, but it was a pain in the butt to get that data back to VB.... So, I had to come up with a way to get the data into a ref cursor...which VB handles well.

What I ended up doing was using on commit delete rows, and I just issue a commit at the beginning of the proc to clear the temp table before the proc is run. I also include a commit in my VB code after I retrieve the ref cursor and disconnect the VB recordset. This should also be clearing the temp table.

This solution appears to work in testing thus far.

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<p9wq8.159389$ZR2.74249_at_rwcrnsc52.ops.asp.att.net>...
> 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 - 15:29:09 CEST