Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Posts
    1

    Unanswered: " PLS-00221: 'V_REFCUR' is not a procedure or is undefined " error message

    Hi Everybody,

    My objective is to print out all the rows of one or more columns from the ref cusor .
    Accordingly
    I have created a package and procedure with the following code
    and tested the same using TEST.SQL script
    " PLS-00221: 'V_REFCUR' is not a procedure or is undefined " error message was obtained after running TEST.SQL

    Please suggest..



    CREATE OR REPLACE PACKAGE INFO_PKG IS

    TYPE REF_CURSOR IS REF CURSOR;


    PROCEDURE PROC_A ( P_INFO IN VARCHAR2,
    P_NUMBER IN NUMBER,
    O_OUTFLAG OUT VARCHAR2,
    O_DETAILS OUT REF_CURSOR);

    END;

    CREATE OR REPLACE PACKAGE BODY INFO_PKG AS

    PROCEDURE PROC_A (P_INFO IN VARCHAR2,
    P_NUMBER IN NUMBER,
    O_OUTFLAG OUT VARCHAR2,
    O_DETAILS OUT REF_CURSOR) IS




    BEGIN

    dbms_output.put_line('Into PROC_A proc...');

    IF upper( P_INFO ) = 'TEMP'

    then

    OPEN O_DETAILS FOR
    SELECT A.COULUMN_1 c1 , A.COULUMN_2 c2 ,A.COULUMN_3 c3 ,B.COULUMN_X c4 ,B.COULUMN_y c5
    FROM A, B
    WHERE A.COULUMN_4 = B.COULUMN_K
    AND A.COULUMN_5 = P_NUMBER;


    o_outflag := 'S';

    END IF;

    END; -- PROC_A
    END; -- INFO_PKG


    TEST.SQL

    declare

    v_refcur INFO_PKG.REF_CURSOR;

    v_out varchar2(10);

    BEGIN

    INFO_PKG.PROC_A ('TEMP',204,v_out,v_refcur);


    /* The objective is to print out all the rows of one or more columns from the ref cusor */

    for l_temp in v_refcur

    loop

    exit when l_temp%notfound;

    dbms_output.put_line (l_temp.c2);

    end loop;

    end;

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    In TEST.SQL, change the loop like this:
    Code:
       LOOP
          FETCH v_refcur INTO l_temp;
          EXIT WHEN v_refcur%NOTFOUND;
          DBMS_OUTPUT.put_line (l_temp.c2);
       END LOOP;

  3. #3
    Join Date
    Mar 2010
    Posts
    1
    Hi...

    Please update me that....Whether we can run the REF CURSOR looping using the For Statement? Because I don't want to type the statements Fetch...Exit When and so on.

    Please do the update.

Posting Permissions

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