Stored procedure that returns a recordset?

[Gene Frassetto]

I have a VB program that calls stored procedures in MS SQL Server that
deliver a recordset. I need to do the same thing with Oracle. Following is
a simple SQL Server procedure that does this. How do I do that with Oracle?
CREATE PROCEDURE dbo.sp_Level1
@ClientDB varchar (30) = NULL,
@LevelID1 varchar (8) = NULL

AS
BEGIN
Select * from tblLevel1
where active='Y' and ClientDB=@ClientDB
order by DisplayOrder
END

return (0)

END

[Robert C]

Based on 0040 (Oracle Object for OLE)(i only use M$ stuff for GUI
things...and only when I have to)

You will have VB call the stored procedure to pass a "ref cursor"
parametr back.

--- The st. procedure need to be in a package. ---

create or replace package foo
as
type rcur is ref cursor ;
procure rctest (p_rc in out rcur, p_table in varchar2 default 'emp') ;
end;

create or replace package body foo
as
procure rctest (p_rc in out rcur, p_table in varchar2 default 'emp')
is
l_dynamic_sql varchar2(1000) := 'select * from ' || p_table ;
begin
open p_rc for select * from emp; --< Open a static sql
/*
open p_rc for l_dynamic_sql ; --< Open a dynamic sql build on the
fly

*/
end rctest;

end;

I have a VB program that calls stored procedures in MS SQL Server that
deliver a recordset. I need to do the same thing with Oracle. Following is
a simple SQL Server procedure that does this. How do I do that with Oracle?
CREATE PROCEDURE dbo.sp_Level1
@ClientDB varchar (30) = NULL,
@LevelID1 varchar (8) = NULL

AS
BEGIN
Select * from tblLevel1
where active='Y' and ClientDB=@ClientDB
order by DisplayOrder
END

return (0)

END

[Ross McKay]

On 16 Aug 2003 12:08:34 -0700, rc***@panix.com (Robert C) wrote:

procure rctest (p_rc in out rcur, p_table in varchar2 default 'emp') ;

Pretty much, but I'd recommend making the output cursor the last
parameter in the procedure. IIRC, ADO or OLE-DB has some problem with
procedure calls where there are more than two parameters, where one of
those is an output ref cursor, and it isn't the last one.

Also note, if you call the procedure by creating an ADO.Command object
and appending Parameter objects, don't create one for the output ref
cursor.

cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Since when were you so generously inarticulate?" - Elvis Costello

[Ross McKay]

On 16 Aug 2003 12:08:34 -0700, rc***@panix.com (Robert C) wrote:

--- The st. procedure need to be in a package. ---

Oh, and whilst I reckon that packages are the way to go in Oracle, if
you prefer, you can define your stored procedures as normal. You will
need to create (at least) one package, however, to define a type for the
reference cursor.

Personally, I'd put all procedures and functions into packages. But one
reason I can think of for not doing that, is if you are using Microsoft
tools like Visual Studio .NET, Visual Interdev or (shudder) the Data
environment in VB to edit your stored procedures - MS tools don't know
what Oracle packages are.

cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Since when were you so generously inarticulate?" - Elvis Costello

[Ross McKay]

On 16 Aug 2003 12:08:34 -0700, rc***@panix.com (Robert C) wrote:

>procure rctest (p_rc in out rcur, p_table in varchar2 default 'emp') ;

Pretty much, but I'd recommend making the output cursor the last
parameter in the procedure. IIRC, ADO or OLE-DB has some problem with
procedure calls where there are more than two parameters, where one of
those is an output ref cursor, and it isn't the last one.

Also note, if you call the procedure by creating an ADO.Command object
and appending Parameter objects, don't create one for the output ref
cursor.

cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Since when were you so generously inarticulate?" - Elvis Costello

[Ross McKay]

On 16 Aug 2003 12:08:34 -0700, rc***@panix.com (Robert C) wrote:

>--- The st. procedure need to be in a package. ---

Oh, and whilst I reckon that packages are the way to go in Oracle, if
you prefer, you can define your stored procedures as normal. You will
need to create (at least) one package, however, to define a type for the
reference cursor.

Personally, I'd put all procedures and functions into packages. But one
reason I can think of for not doing that, is if you are using Microsoft
tools like Visual Studio .NET, Visual Interdev or (shudder) the Data
environment in VB to edit your stored procedures - MS tools don't know
what Oracle packages are.

cheers,
Ross.
--
Ross McKay, WebAware Pty Ltd
"Since when were you so generously inarticulate?" - Elvis Costello