Re: Stored procedure that returns a recordset?

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
> _at_ClientDB varchar (30) = NULL,
> _at_LevelID1 varchar (8) = NULL
>
> AS
> BEGIN
> Select * from tblLevel1
> where active='Y' and ClientDB=_at_ClientDB
> order by DisplayOrder
> END
>
> return (0)
>
> END
Received on Sat Aug 16 2003 - 21:08:34 CEST