Returning result sets in oracle stored procedure (OLEDB)
Author |
Message |
Raju Mat #1 / 7
|
 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 |
|
 |
Steffen Ramlo #2 / 7
|
 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 |
|
 |
Raju Matt #3 / 7
|
 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 |
|
 |
John Go #4 / 7
|
 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 |
|
 |
Justin Ca #5 / 7
|
 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 |
|
 |
Galen Boye #6 / 7
|
 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 |
|
 |
Keith Jamieso #7 / 7
|
 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 |
|
|
|