Re: Returning result sets in oracle stored procedure (OLEDB)

matta_at_metratech.com (Raju Matta) wrote in message news:<f839fd01.0106120235.1ea7340f_at_posting.google.com>...
> Folks
>
> Our environment: Win2K SP2, Oracle 8i (8.1.6), MDAC 2.6, Using
> Microsoft ODBC driver for Oracle, OLEDB

Is there a reason that you cannot use the Oracle OLE DB provider or the Oracle ODBC driver? I believe that both have support for returning multiple resultsets from a stored procedure.

I assume that you're able to return single resultsets from stored procedures successfully, so your problem is solely with multiple resultsets.

Justin Cave

>
> We have a stored procedure that is a part of a package. This stored
> procedure does some work in a temporary table and then returns result
> sets as part of a "select" statement that happens at the end of the
> proc. We are using OLEDB as the data access layer. OLEDB interprets
> this as multiple result sets - two empty ones and one with data in it.
>
> So, it appears as if there are 0 rows returned. ADO supports
> NextRecordset that should walk to the next one, but the driver
> returns:
>
> 800a0cb3 (Current provider does not support returning multiple
> recordsets from a single execution.)
>
> We were able to fix this problem on the SQL/Server side by using "SET
> NOCOUNT ON". This eliminates the "1 rows affected" messages returning
> to the client that confuse OLEDB. The closest there is to this
> statement in Oracle is "SET FEEDBACK OFF" but that's a SQL Plus
> specific statement and can't be embedded into a sproc like we need it
> to be.
>
> Please let me know the solution or anything close to the solution, if
> you do, either by responding to this post of sending me email at
> matta_at_metratech.com
>
> Thanks
>
> -Raju
Received on Mon Jul 23 2001 - 15:17:17 CEST