468,101 Members | 1,319 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,101 developers. It's quick & easy.

Stored procedure that returns a recordset?

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

Jul 19 '05 #1
5 13695
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

Jul 19 '05 #2
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

Jul 19 '05 #3
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

Jul 19 '05 #4
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

Jun 27 '08 #5
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

Jun 27 '08 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

15 posts views Thread by Jarrod Morrison | last post: by
4 posts views Thread by Jarrod Morrison | last post: by
1 post views Thread by Eugene Anthony | last post: by
6 posts views Thread by Wojciech Wendrychowicz | last post: by
2 posts views Thread by =?Utf-8?B?YW5vb3A=?= | last post: by
1 post views Thread by Solo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.