Returning result sets in oracle stored procedure (OLEDB) 
Author Message
 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

Thanks

-Raju



Sat, 29 Nov 2003 18:35:52 GMT
 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


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



Sat, 29 Nov 2003 20:00:20 GMT
 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


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



Sun, 30 Nov 2003 03:51:47 GMT
 Returning result sets in oracle stored procedure (OLEDB)
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
| >
| >
|
|
|



Sun, 30 Nov 2003 06:49:04 GMT
 Returning result sets in oracle stored procedure (OLEDB)

Quote:

> 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

Quote:

> 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



Fri, 09 Jan 2004 21:17:17 GMT
 Returning result sets in oracle stored procedure (OLEDB)
First, refrain from the large crosspostin

Quote:
> 800a0cb3 (Current provider does not support returning multiple
> recordsets from a single execution.)

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.



Tue, 13 Jan 2004 11:04:06 GMT
 Returning result sets in oracle stored procedure (OLEDB)
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.

Quote:

>First, refrain from the large crosspostin

>> 800a0cb3 (Current provider does not support returning multiple
>> recordsets from a single execution.)

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

>and search.  You should find the answer you need.

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



Tue, 13 Jan 2004 16:04:18 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. Returning result sets in oracle stored procedure (OLEDB)

2. Using oracle stored procedures/functions to return result sets

3. Oracle stored procedure returning a result set

4. OLE ,Oracle, ans Stored Procedures returning result sets

5. MSSQLServer->Oracle: Identity and returning a result set using oracle procedure or function

6. returning result sets from stored procedures

7. return multiple result sets from a stored procedure

8. Can't update result set returned from stored procedure using RDO

9. Returning result set from function to stored procedure is very slooooooooow

10. best way to return a paged result set from a stored procedure

11. Returning a result set from a Stored Procedure

12. returning result sets from stored procedures


 
Powered by phpBB® Forum Software