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

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

"Steffen Ramlow" <s.ramlow_at_gmx.de> wrote in message news:9g507j$71efc$1_at_ID-54600.news.dfncis.de...
> 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:16:04 CDT