Re: Oracle8i temporary tables / stored procedures / visual basic

Sounds to me like ADO is autocommitting. You can either set autocommit to false (I have no idea how to do this in ADO) or use the on commit preserve rows clause and have the last statement of you stored proc delete the rows out of your table.

In article <8tkvq8$ef0$1_at_nnrp1.deja.com>,   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).
>
> If I drop the temporary table and create a permanent table of the same
> structure, then the stored procedure works from both SQL*Plus and VB.
> But with the permanent table, the code has to contend with avoiding
> conflict between concurrent transactions. Is there something about
 the
> way that VB interacts with the database that could be causing the
> Oracle8i temporary tables to not function as I would expect?
>
> Thanks,
> Brian Gastineau
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Oct 31 2000 - 11:30:05 CST