3 Replies Latest reply: Nov 15, 2012 9:40 AM by Kim Berg Hansen RSS

    Ref Cursor as out parameter.

    BS2012
      Hi All,
      Please help me with this.

      This is a small procedure where ref cursor is used as out parameter.
      create or replace
      PROCEDURE P_REFCURSOR_OUT 
      (P_DEPTNO    IN  EMP.DEPTNO%TYPE,
      P_RECORDSET OUT SYS_REFCURSOR) AS 
      BEGIN 
            OPEN P_RECORDSET FOR
            SELECT EMPNO,
            EMPNM,
            DEPTNO
            FROM   EMP
            WHERE  DEPTNO = P_DEPTNO
            ORDER BY EMPNM;
      END P_REFCURSOR_OUT;
      This is another procedure which is calling the above procedure. But while calling it's throwing an exception that ORA-01722. What's wrong here? Please help.
      CREATE OR REPLACE PROCEDURE P_CALL_REFCURSOR_OUT AS
        L_CURSOR  SYS_REFCURSOR;
        L_EMPNM   EMP.EMPNM%TYPE;
        L_EMPNO   EMP.EMPNO%TYPE;
        L_DEPTNO  EMP.DEPTNO%TYPE;
      BEGIN
        P_REFCURSOR_OUT (P_DEPTNO    => 1000,
                    P_RECORDSET => L_CURSOR);
                  
        LOOP 
          FETCH L_CURSOR
          INTO  L_EMPNM, L_EMPNO, L_DEPTNO;
          EXIT WHEN L_CURSOR%NOTFOUND;
          DBMS_OUTPUT.PUT_LINE(L_EMPNM || ' | ' || L_EMPNO || ' | ' || L_DEPTNO);
        END LOOP;
        CLOSE L_CURSOR;
      END P_CALL_REFCURSOR_OUT;
      Regards,
      BS2012
        • 1. Re: Ref Cursor as out parameter.
          JustinCave
          The cursor that you are opening selects the EMPNO, EMPNM, and DEPTNO in that order.
          When you fetch from the cursor, you are fetching into L_EMPNM, L_EMPNO, L_DEPTNO.

          It would appear that you have reversed the first two columns in your FETCH statement. Assuming that EMPNO is a NUMBER and EMPNM is a VARCHAR2, that means that Oracle is trying to implicitly cast the EMPNM into a NUMBER which causes the error.

          If you change the order of the variables in your FETCH statement, the error should disappear
              FETCH L_CURSOR
              INTO   L_EMPNO, L_EMPNM, L_DEPTNO;
          Justin
          • 2. Re: Ref Cursor as out parameter.
            kendenny
            Your fetch into columns are in the wrong order. They must be in the same order as the select in the ref cursor. Your second column in the fetch into list is empno, but the second column in the select is empnm, so your trying to fetch the name into a number column. That's why you're getting the error.
            • 3. Re: Ref Cursor as out parameter.
              Kim Berg Hansen
              Your SELECT column list is EMPNO, EMPNM, DEPTNO.
              Your FETCH column list is L_EMPNM, L_EMPNO, L_DEPTNO.

              So your code implicitly converts EMPNO into a string for L_EMPNM.
              And then it implicitly tries to convert EMPNM into a number for L_EMPNM.
              And that fails with an invalid number exception ;-)

              (PS. Please cut and paste the error next time. Not everybody remembers all the error codes in their head...)