Ref cursor variable declaration
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Ref cursor variable declaration

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    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(
    FIRST_DATE DATE,
    LAST_DATE DATE,
    TOTAL_STUDENTS INTEGER);
    END read_refcursor;

    create or replace package body read_refcursor AS
    r1 r;
    outrefcursor generic_ref_cur;
    BEGIN
    reporting.quiz_score_report('M125Xassess-1::AS', null, 13087, null, outrefcursor);
    LOOP
    fetch outrefcursor into r1;
    exit when outrefcursor%notfound;
    dbms_output.put_line(r1.FIRST_DATE);
    END LOOP;
    END read_refcursor;


    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.
    Shiva.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I would create reporting.quiz_score_report as a function and return the cursor to the calling block.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    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 ?

    Shiva.

  4. #4
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90
    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.

  5. #5
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    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

    Thanks,
    Shiva.

  6. #6
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90
    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?
    d

  7. #7
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Yup. I did realize it after my reply. Thanks.
    This works :

    declare
    type s is ref cursor;
    c s;
    type r is record(
    FIRST_DATE DATE,
    LAST_DATE DATE,
    TOTAL_STUDENTS INTEGER,
    COMPLETED_STUDENTS integer,
    CLASS_NAME varchar2(100));
    r1 r;

    begin
    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');
    dbms_output.put_line('----------------------------------------------------------------------------');
    loop
    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);
    end loop;
    end;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width