Home » SQL & PL/SQL » SQL & PL/SQL » How do I return recordset from Oracle
How do I return recordset from Oracle [message #37260] Mon, 28 January 2002 07:23 Go to next message
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
Re: How do I return recordset from Oracle [message #37261 is a reply to message #37260] Mon, 28 January 2002 07:26 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
use a ref cursor
Re: How do I return recordset from Oracle [message #37263 is a reply to message #37261] Mon, 28 January 2002 08:45 Go to previous messageGo to next message
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
Re: How do I return recordset from Oracle [message #37265 is a reply to message #37261] Mon, 28 January 2002 08:59 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
http://osi.oracle.com/~tkyte/ResultSets/index.html
Re: How do I return recordset from Oracle [message #37269 is a reply to message #37261] Mon, 28 January 2002 09:30 Go to previous messageGo to next message
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
Re: How do I return recordset from Oracle [message #37272 is a reply to message #37261] Mon, 28 January 2002 10:12 Go to previous messageGo to next message
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.
Re: How do I return recordset from Oracle [message #37274 is a reply to message #37261] Mon, 28 January 2002 10:33 Go to previous message
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?.
Previous Topic: Re: Problem with a procedure
Next Topic: not a vaild month error
Goto Forum:
  


Current Time: Thu Aug 06 09:05:18 CDT 2020