4 Replies Latest reply: Mar 18, 2013 8:42 AM by Paul Horth RSS

    Display records using Sys_Refcursor

    user1676630
      Hi All,

      I have a function returns results set i.e. sys_refcursor

      CREATE OR REPLACE
      FUNCTION fn_emp RETURN SYS_REFCURSOR
      AS
      lc_Cursor SYS_REFCURSOR;
      BEGIN
      OPEN lc_Cursor FOR
      SELECT empno,
      ename,
      sal,
      deptno
      FROM emp;

      RETURN lc_Cursor;

      END fn_emp;

      Here is procedure wants to display records using sys_refcursor(Through procedure only)

      CREATE OR REPLACE
      PROCEDURE test831
      AS

      lc_cur SYS_REFCURSOR;

      BEGIN
      lc_cur := fn_emp ;

      FOR i IN lc_cur
      LOOP

      DBMS_OUTPUT.PUT_LINE('i.empno');
      DBMS_OUTPUT.PUT_LINE('i.ename');

      END LOOP;

      END test831;


      but the procedure is not compiled.

      Can you please check and solve the this requirement.

      Advance thanks .
        • 1. Re: Display records using Sys_Refcursor
          BluShadow
          Your function doesn't actually return anything.

          I think you're missing the:
          --RETURN lc_Cursor;--
          from it.

          just seen it, that's the problem if you don't format your code on the forums


          You will also need to fix your dbms_output statements are you are outputting literal strings, not the cursor attributes/columns.

          Edited by: BluShadow on 18-Mar-2013 11:11
          • 2. Re: Display records using Sys_Refcursor
            BluShadow
            The problem with your procedure is that you are using a FOR loop which tries to implicitly open the cursor, which it cannot do as the cursor is already open.
            You just need to loop and fetch rows from it with your own exit condition...
            SQL> ed
            Wrote file afiedt.buf
            
              1  CREATE OR REPLACE PROCEDURE test831 AS
              2    lc_cur SYS_REFCURSOR;
              3    lv_empno number;
              4    lv_ename varchar2(10);
              5    lv_sal number;
              6    lv_deptno number;
              7  BEGIN
              8    lc_cur := fn_emp();
              9    LOOP
             10      fetch lc_cur into lv_empno, lv_ename, lv_sal, lv_deptno;
             11      exit when lc_cur%NOTFOUND;
             12      DBMS_OUTPUT.PUT_LINE(to_char(lv_empno)||','||lv_ename);
             13    END LOOP;
             14    CLOSE lc_cur;
             15* END test831;
            SQL> /
            
            Procedure created.
            
            SQL> set serverout on
            SQL> exec test831;
            7369,SMITH
            7499,ALLEN
            7521,WARD
            7566,JONES
            7654,MARTIN
            7698,BLAKE
            7782,CLARK
            7788,SCOTT
            7839,KING
            7844,TURNER
            7876,ADAMS
            7900,JAMES
            7902,FORD
            7934,MILLER
            
            PL/SQL procedure successfully completed.
            However, why are you using a sys_refcursor in PL/SQL code?

            ref cursors are typically used to be passed back to 3rd party application layers where they fetch the data. You shouldn't need to do this in PL/SQL as PL/SQL supports cursors in more friendly ways.
            • 3. Re: Display records using Sys_Refcursor
              user1676630
              Thank you for the solution.
              • 4. Re: Display records using Sys_Refcursor
                Paul  Horth
                user1676630 wrote:
                Thank you for the solution.
                It is good forum etiquette to mark the thread as answered.