Stored Procedure returning Multiple records

[regisacc]

Hi ,
I have created a procedure to return multiple record set but I am unable to execute it. Can any one help me with this.
I get the error when executing this procedure saying that variable 'CURSOR ' must be declared.

Here is and example code:

CREATE OR REPLACE package reftest as
cursor c1 is select ename from emp;
type empCur is ref cursor return c1%ROWTYPE;
Procedure GetEmpData(en in varchar2,EmpCursor in out empCur);
END;
CREATE OR REPLACE package body reftest as
Procedure GetEmpData
(en in varchar2,EmpCursor in out empCur) is
begin
open EmpCursor for select ename from emp where ename
LIKE en;
end;
end;

 

[sem]

There is nothing wrong with your procedure. The error is probably in your call. Try this one:

declare
mEn varchar2(100);
mCur reftest.empCur;
mEmp reftest.c1%rowtype;
begin
reftest.GetEmpData(mEn, mCur);
fetch mcur into mEmp;
..........................
end;

 

[regisacc]

Thank YOu. I appreciate your response.
My problem is to create a Crystal report using this stored procedure returning multiple rows. Do you have any ideas?
By the way wher are you passing the in parameter in the above call.

 

[Turkbear]

Crystal reports can only use a stored procedure that returns a REFCURSOR - Look in the PlSql docs for details on how to create this type...

hth

 

[gap2000]

for REFCURSOR, check these 2 links:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q255043

http://www.learnasp.com/learn/oraclerecordsetsfromsp.asp

 

[regisacc]

My problem was with drivers . I had to use CR Oracle v3.6 drivers to get multiple records from Stored procedure.

Thank You.

 

[higuys]

when i press add in the crystal reports data explorer window, i get the error saying, " the identifier 'procedure name' not declared". but, this procedure is in the database. how do i retrieve the fields? and make this work?
thank you.

 

[higuys]

i am using oracle 8i database.