Return result set from Oracle?

Return result set from Oracle?

Post by Tom Bellme » Mon, 22 Nov 1999 04:00:00



How can I return a 100 row cursor result set from an Oracle 8
stored proc or package?  I know this is very easy using SQL Server
but does not appear to be the case with Oracle.

Looks like I may need to use a REF cursor in Oracle, but I sure
could use an example to make it all clear.

Tom Bellmer

 
 
 

Return result set from Oracle?

Post by tjm.. » Tue, 23 Nov 1999 04:00:00


Here's an example...
But I haven't made it work with VFP yet..
I.E. You don't get a cursor...

create or replace package types
as
 type cursortype is ref cursor;
end;
/

create or replace function sp_listemp return types.cursortype
as
 l_cursor types.cursortype;
begin
open l_cursor for select recno,mainkey from emp;
return l_cursor;
end;
/

SQL Plus code
************
variable c refcursor
exec :c :=sp_listemp

print c


> How can I return a 100 row cursor result set from an Oracle 8
> stored proc or package?  I know this is very easy using SQL Server
> but does not appear to be the case with Oracle.

> Looks like I may need to use a REF cursor in Oracle, but I sure
> could use an example to make it all clear.

> Tom Bellmer


Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Return result set from Oracle?

Post by Anders Altber » Tue, 23 Nov 1999 04:00:00


create table Sample1 (pk Integer not null primary key, lname Char(20))
insert into Sample1 values (11, 'Bill')
insert into Sample1 values (12, 'Tom')
insert into Sample1 values (13, 'Harry')
insert into Sample1 values (14, 'John')
select S1.pk,S1.lname, count(s2.pk);
 from Sample1 as S1 join Sample1 as S2 ;
on S1.pk<=S2.pk ;
group by s2.pk having count(*) between n1 and n2

Set n1=1, n2=100. The next batch, n1=101, n2=200
-Anders


| How can I return a 100 row cursor result set from an Oracle 8
| stored proc or package?  I know this is very easy using SQL Server
| but does not appear to be the case with Oracle.
|
| Looks like I may need to use a REF cursor in Oracle, but I sure
| could use an example to make it all clear.
|
| Tom Bellmer

|
|
|

 
 
 

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

2. DBA opportunity! - SQL Server/ORACLE

3. Returning result set from Oracle

4. HOW DO I: Delete data from multiple tables?

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

6. how to use General Timeout of Connection in DTS

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

8. I have a problem with CHARSET

9. Return result set and return value/output parameter

10. Oracle 8.05i / RDO / and truncated returned values in result sets

11. does Oracle stored proc return a result set?

12. DatabaseMetaData.getColumns() returns a result set with 13 (jdbcodbc) or 18 columns (oracle)

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