Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Hyderabad
    Posts
    4

    Thumbs up Unanswered: printing a ref cursor...Oracle Gurus.....

    Hi pls check the code below and let me know how to check values of ref cursor....
    Thanx in advance ....for ny suggestion and help

    Code:-----------

    procedure abc(P_col1 IN VARCHAR2,P_col2 OUT VARCHAR2,P_col3_rc IN OUT ref_cur_Pkg.ref_cur_tp,P_comments1 OUT VARCHAR2)
    IS
    BEGIN
    ---
    IN CODE REF CURSOR P_col3_rc is opened and values are selected.
    eg .SELECT A,B,C FROM XYZ;
    END;
    /
    Now while executing the procedure...it works ..i wanna check the values of ref cursor..also
    ie abc(P_col1,P_col2,P_col3_rc,P_comments1);
    open P_col3_rc
    loop
    fetch .............???.............?
    dbms_output.put_line(?????????);
    end loop;
    ie i wanna see the values of A,B,C from XYZ. from this ref cursor after executing procedure ABC..

    It urgent.... really apprieciate to recv quick response...
    CHEERS...HAPPY CODING

  2. #2
    Join Date
    Dec 2003
    Location
    India
    Posts
    12
    What u can do is write a PL/SQL block and execute the stored procedure in this block---fetch the ref cursor values into the record XXX then print them using DBMS_OUTPUT....

    DECLARE
    --note the varchar length can be changed as per the column lengths of A /B/C
    comments varchar2(100);
    TYPE XXX IS RECORD(
    A VARCHAR2(12)
    ,B VARCHAR2(1)
    ,C VARCHAR2(100)
    );
    XXX1 XXX;
    cv ref_cur_Pkg.ref_cur_tp;
    BEGIN
    abc(
    'ABCD', --p_col1
    'ABCD',-- p_col2
    ,cv ---p_col3_rc
    ,comments --p_comments1
    );

    LOOP
    FETCH cv INTO XXX1;
    EXIT WHEN cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (XXX1.A
    || ' '||XXX1.B
    || ' '||XXX1.C
    );
    END LOOP;
    CLOSE cv;
    END;


    Hope this helps

    Sbdash
    Last edited by sbdash; 12-23-03 at 04:30.

  3. #3
    Join Date
    Dec 2003
    Location
    Hyderabad
    Posts
    4
    tell me what is XXX1 XXX;
    this is the main catch line which u incyrpted... :O
    any how i run the stub it gives an error :
    ERROR at line 1:
    ORA-01007: variable not in select list
    ORA-06512: at line 42

    Originally posted by sbdash
    What u can do is write a PL/SQL block and execute the stored procedure in this block---fetch the ref cursor values into the record XXX then print them using DBMS_OUTPUT....

    DECLARE
    --note the varchar length can be changed as per the column lengths of A /B/C
    comments varchar2(100);
    TYPE XXX IS RECORD(
    A VARCHAR2(12)
    ,B VARCHAR2(1)
    ,C VARCHAR2(100)
    );
    XXX1 XXX;
    cv ref_cur_Pkg.ref_cur_tp;
    BEGIN
    abc(
    'ABCD', --p_col1
    'ABCD',-- p_col2
    ,cv ---p_col3_rc
    ,comments --p_comments1
    );

    LOOP
    FETCH cv INTO XXX1;
    EXIT WHEN cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE (XXX1.A
    || ' '||XXX1.B
    || ' '||XXX1.C
    );
    END LOOP;
    CLOSE cv;
    END;


    Hope this helps

    Sbdash

  4. #4
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171
    XXX is a type declared.
    XXX1 is variable declared of type XXX
    You fetch the ref cursor received from proc into XXX1 and the display it from XXX1.

    What you are missing is - there is a difference in your select and XXX type declaration. These should have same columns.

    I could execute the following w/o any errors:

    create or replace package ref_cur_Pkg as
    TYPE ref_cur_tp IS REF CURSOR;
    procedure abc(out_ref_cursor out ref_cur_Pkg.ref_cur_tp);
    end ref_cur_Pkg;
    /

    create or replace package body ref_cur_Pkg as
    procedure abc(out_ref_cursor OUT ref_cur_Pkg.ref_cur_tp) IS
    BEGIN
    open out_ref_cursor for 'select A from B';
    end;
    end ref_cur_Pkg;
    /

    set serveroutput on
    declare
    retCur ref_cur_Pkg.ref_cur_tp;
    type XXX is record(
    A B.A%TYPE);

    XXX1 XXX;

    begin
    ref_cur_Pkg.abc(retCur);
    loop
    fetch retCur into XXX1;
    exit when retCur%NOTFOUND;
    dbms_output.put_line(XXX1.A);
    end loop;
    end;
    /
    Oracle can do wonders !

Posting Permissions

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