Ref cursor variable declaration
Hi, Here is my package where I want to read the RefCursor returned by another PL/SQL Package :
create or replace package read_refcursor AS
type generic_ref_cur is ref cursor;
type r is record(
create or replace package body read_refcursor AS
reporting.quiz_score_report('M125Xassess-1::AS', null, 13087, null, outrefcursor);
fetch outrefcursor into r1;
exit when outrefcursor%notfound;
I get the error 'PLS-00994: Cursor Variables cannot be declared as part of a package' on the line 'outrefcursor generic_ref_cur;'.
Procedure reporting.quiz_score_report expects a RefCursor as an OUT parameter.
What is the right way to do this ? Thanks.
I would create reporting.quiz_score_report as a function and return the cursor to the calling block.
reporting.quiz_score_report is used in a lot of places in the App and I cannot really change anything there. Is there any other way to achieve this ?
rshivagami, you are confused. What are you trying to do anyway?
Is this homework?
You need to open your refcursor for some kind of a select statement before trying to fetch.
Did reporting.quiz_score_report open the refcursor?
Create a function in the package like Jeff advised. If you do it in the package initialization section then how will you call this code? The package initialization runs once only and you have to call some kind of packaged object to get it to run.
Last edited by ddrozdov; 04-15-2004 at 12:10 PM.
Ofcourse RefCursor needs to be opened for the SELECT statement. As I had mentioned, reporting.quiz_score_report is in another package that returns REFCURSOR as an OUT paramter. That query is pretty complicated and I want to see the resultset of that query.
I used the thread as a guidline: http://www.dbasupport.com/forums/sho...threadid=38317
You did not follow the example to which you are referring.
The refcursor type cust_cursor is declared in the package speck.
Two procedures get_records and get_it are in the package body.
get_records opens the refcursor for a select statement and returns it as an out argument. get_it fetches rows and puts it to dbms_output buffer.
You need to use refcursor type that is declared in package reporting.
create a function in the package that you are creating that accepts an in argument of reporting.refcursor type. Got it?
Yup. I did realize it after my reply. Thanks.
This works :
type s is ref cursor;
type r is record(
reporting.quiz_score_report('M125Xassess-1::AS', null, 13087, null, c);
dbms_output.put_line('first_date last_date total_students completed_students class_name');
fetch c into r1;
exit when c%NOTFOUND;
dbms_output.put_line(r1.first_date || ' ' || r1.last_date || ' ' || r1.total_students || ' ' || r1.completed_students || ' ' || r1.class_name);
Click Here to Expand Forum to Full Width