Returning result sets in oracle stored procedure (OLEDB)

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 - 16:11:33 CDT