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

Thanks

-Raju

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

BTW, the article you mentioned applied to using FoxPro.  I already have the
stuff working
as described in KB article
http://support.microsoft.com/support/kb/articles/Q255/0/43.ASP

The use of package and embedding a stored procedure is already working for
me.  It is
in this case that I mentioned earlier that I have a stored procedure which
creates a temp table,
does a few  selects and becasue of that, the output is interpreted as
multiple result sets.

-Raju

Raju,

You are not going to be able to return multiple result sets using the OLEDB
driver.  Unfortunately, as the error message states, the driver does not
currently support this feature (NextRecordset ).  Sorry.  Perhaps someone
in the NG can give you some pointers on how to design the package so that
it will do what you wish.

John Gose
Microsoft SQL Server Support
--------------------




| Subject: Re: Returning result sets in oracle stored procedure (OLEDB)
| Date: Tue, 12 Jun 2001 15:51:47 -0400
| Lines: 65
|
<Snip of Header Stuff>
|
| BTW, the article you mentioned applied to using FoxPro.  I already have
the
| stuff working
| as described in KB article
| http://support.microsoft.com/support/kb/articles/Q255/0/43.ASP
|
| The use of package and embedding a stored procedure is already working for
| me.  It is
| in this case that I mentioned earlier that I have a stored procedure which
| creates a temp table,
| does a few  selects and becasue of that, the output is interpreted as
| multiple result sets.
|
| -Raju
|
|


| > 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
| >
| >


| > > 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

| > >
| > > Thanks
| > >
| > > -Raju
| >
| >
|
|
|

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

First, refrain from the large crosspostin

Go here
http://groups.google.com/groups?hl=en&lr=&safe=off&group=comp.databas...

and search.  You should find the answer you need.

--
Galen Boyer
It seems to me, I remember every single thing I know.

I am currently writing an answer on this topic for the co-operative FAQ.  If
you use the standard microsoft ADO driver for Oracle, you must use ADO 2.5
or above.

There are a whole host of issues, but I hope to have something sent to
Jonathan by the end of today.