Re: Recordset and Stored Procedures - URGENT

I just called tech support
and that is what they mailed me. With very minor changes you can make them to work in your schema.

Alex

TestCursor.pkg
::::::::::::::
-- create a generic cursor
create or replace package TestCur as
  type GenericCur is ref cursor;
  type SpecificCur is ref cursor return emp%ROWTYPE; end TestCur;
::::::::::::::
 TestSpecific.sql --- a rountine that returns a REF cursor ::::::::::::::
create or replace procedure TestSpecific

   (SqlString in varchar2, RetCursor in out TestCur.SpecificCur) as x number :=1;
c_sql INTEGER := dbms_sql.open_cursor;
f_dbms INTEGER;
begin

   OPEN RetCursor FOR select * from emp;    dbms_sql.parse(c_sql, 'OPEN :RetCursor FOR select * from emp',dbms_sql.native);

   dbms_sql.bind_variable(c_sql,'RetCursor',x);    f_dbms := dbms_sql.execute(c_sql);

• OPEN RetCursor FOR SqlString; --- gives PLS0445 : cursor cannot be used in dynamic sql open.
• OPEN RetCursor FOR 'select * from emp'; -- ditto end; :::::::::::::: spec.sql - an anonymous blovk to test the procedure :::::::::::::: declare specific_cursor TestCur.SpecificCur; emp_rec specific_cursor%ROWTYPE; select_stmt VARCHAR2(40) := 'select * from emp'; begin TestSpecific(select_stmt, specific_cursor); dbms_output.put_line(' CHECK RETURN ... '); LOOP fetch specific_cursor into emp_rec; exit when specific_cursor%NOTFOUND; dbms_output.put_line(' specific_cursor.ename '|| emp_rec.ename); end loop; end;

"These statements and opinions are not necessarily my

    nor do they necessarily represent those of Oracle"

In article <3934b9bc$0$234_at_hades.is.co.za>,   "George" <george_at_is.co.za> wrote:
> Hi there
>
> I am sure this has been answered before.
>
> How do I return a record set from a Oracle Stored Procedure.
>
> An example would be much appreciated.
>
> thx
>
> George
> Please eMail reply also. georg_at_is.co.za
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jun 02 2000 - 00:00:00 CDT