Re: Oracle8i temporary tables / stored procedures / visual basic

When I recreated the temporary table with an explicit "ON COMMIT PRESERVE" clause, the stored procedure worked as expected. Although I haven't found it specifically (little effort was made), I gather that ON COMMIT DELETE" is the default.

I'm a little surprised that this distinction comes into play for a single stored procedure that inserts then immediately queries (through a ref cursor) the temporary table. It is like an autocommit is occurring between those two sections of PL/SQL code.

By the way, ADO is being used.

Thanks,
Brian Gastineau

In article <aJL+OYgP54CwKCoAWt1upjUhP7Bg_at_4ax.com>,   Paul Moore <paul.moore_at_uk.origin-it.com> wrote:
> On Tue, 31 Oct 2000 00:21:51 +0100, gastineau_at_my-deja.com wrote:
>
> >I'm attempting to use a temporary table in a complicated stored
> >procedure. The stored procedure populates the temporary table then
> >immediately uses it as the central table in a ref cursor query (all
> >other tables are outer joined to the temporary table). The results
 are
> >returned through a stored procedure parameter.
> >
> >The procedure works when run from SQL*Plus, but when run from Visual
> >Basic, it appears that the temporary table is empty when the cursor
> >query is executed. I say this because replacing the temporary table
> >with "dual" in the query causes one record to be returned (it was
 empty
> >before).
>
> You don't say how you are connecting to the database in VB, but it
> sounds like the procedure is being run in a session which is either no
> longer available, or not the same as the one VB is using. Rereading
> that statement, it sounds dreadfully vague, but maybe you can see what
> I mean :-)
>
> Please describe what method you are using to connect from VB. If it is
> ADO/OLE DB for instance, I know that ADO pools connections for use by
> clients - it may be that this is affecting you.
>
> Paul.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Oct 31 2000 - 08:32:10 CST