Home » SQL & PL/SQL » SQL & PL/SQL » Using Return Cursor as out parameter in procedure
Using Return Cursor as out parameter in procedure [message #35911] Tue, 23 October 2001 09:01 Go to next message
Poonamb
Messages: 16
Registered: October 2001
Junior Member
Hello,

First time user of return cursor. I am unable to compile the below code and cannot identify the problem. Error in declaration of the OUT parameter of return cursor.

My purpose is to send back the query results via this OUT parameter cursor.

Any additional info about return cursor will help!

create or replace procedure TESTPARAMETERS(UserID IN char,
Days IN number,
Sortby IN varchar2,
MyResultCur OUT Ref Cursor
return MyTable%ROWTYPE) is

begin

open MyResultCur for
select *
from MyTable
where user_id = UserID
and rownum <= Days
order by load_id, load_dt desc;

end TESTPARAMETERS;

----------------------------------------------------------------------
Re: Using Return Cursor as out parameter in procedure [message #35914 is a reply to message #35911] Tue, 23 October 2001 09:55 Go to previous message
Milan Kumar Barui
Messages: 16
Registered: October 2001
Junior Member
Hi Poonam,
For using ref cursor in standalone procedure, you have to define ref cursor type in different package and refer that to your procedure. I am giving example below :

CREATE PACKAGE dept_types AS
TYPE DeptType IS REF CURSOR RETURN dept%ROWTYPE;
END dept_types;

CREATE PROCEDURE test1 (dept_var OUT dept_types.DeptType) AS
BEGIN
OPEN dept_var FOR SELECT * FROM dept;
END test1;

I think it will solve your problem.
Give me feedback.
Thanks
Milan

----------------------------------------------------------------------
Previous Topic: Executing An Generated sql statement from a procedure
Next Topic: Rownum with an order by clause
Goto Forum:
  


Current Time: Fri Apr 03 06:57:35 CDT 2020