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

in oracle it is not so simple as in mssql to return a resultset from a sp

search in MSDN for :

HOWTO: Return a Resultset from Oracle Stored Procedures into an ADO Recordset

Q239771

"Raju Matta" <matta_at_metratech.com> 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
>
> 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 Sat Jul 21 2001 - 22:15:57 CDT