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