OraFAQ Forum: SQL & PL/SQL » How do I return recordset from Oracle

Home » SQL & PL/SQL » SQL & PL/SQL » How do I return recordset from Oracle

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How do I return recordset from Oracle [message #37260]

Mon, 28 January 2002 07:23

rau
Messages: 4
Registered: January 2002

Junior Member

I tried to return a recordset(Select statement) using cursor, but I couldn't display all the records. What I am trying to do is as we do SQL Server writing a simple select query in a procedure which will return more than 1 row.

Thanks,
Rau

Report message to a moderator

Re: How do I return recordset from Oracle [message #37261 is a reply to message #37260]

Mon, 28 January 2002 07:26

pratap kumar tripathy
Messages: 660
Registered: January 2002

Senior Member

use a ref cursor

Report message to a moderator

Re: How do I return recordset from Oracle [message #37263 is a reply to message #37261]

Mon, 28 January 2002 08:45

Suresh Vemulapalli
Messages: 624
Registered: August 2000

Senior Member

step 1) declare ref cursor type in package header

create or replace package pkg1 is
type r is ref cursor;
end;

Step 2) use that packaged cursor as parameter to procedure

Create or replace procedure p1(r1 out pkg1.r) is

begin

open r1 for select * from tab1;

end;

Step 3)

in VB, use result set to get values returned by stored procedure.

HTH
Suresh

Report message to a moderator

Re: How do I return recordset from Oracle [message #37265 is a reply to message #37261]

Mon, 28 January 2002 08:59

andrew again
Messages: 2577
Registered: March 2000

Senior Member

http://osi.oracle.com/~tkyte/ResultSets/index.html

Report message to a moderator

Re: How do I return recordset from Oracle [message #37269 is a reply to message #37261]

Mon, 28 January 2002 09:30

rau
Messages: 4
Registered: January 2002

Junior Member

In VB there is parameter to return recordset for Command parameter. And one more problem is VB code should support Sybase,Oracle,SQL Server. Sybase and SQL Server doesn't have package concept I can not give the same name for the procedure.

Thanks,
Rau

Report message to a moderator

Re: How do I return recordset from Oracle [message #37272 is a reply to message #37261]

Mon, 28 January 2002 10:12

Tim
Messages: 49
Registered: October 2000

Member

In VB you set your Recordset variable equal to the Command.Execute. I dont know Sybase or SQL Server, but the code Suresh gave you should work for you. The package is internal, you then call a stand alone procedure that simply references the package, so the out side program should have no idea that a package was even involved. That way, you should be able to use the same procedure name.

Report message to a moderator

Re: How do I return recordset from Oracle [message #37274 is a reply to message #37261]

Mon, 28 January 2002 10:33

Suresh Vemulapalli
Messages: 624
Registered: August 2000

Senior Member

you dont need to specify package name in your VB code.(package i used just to declare ref cursor) did you see my earlier posting?.

Report message to a moderator

Previous Topic:	Re: Problem with a procedure
Next Topic:	not a vaild month error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 13 20:20:17 CDT 2025