| Using Return Cursor as out parameter in procedure [message #35911] |
Tue, 23 October 2001 09:01  |
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  |
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
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
