This content has been marked as final. Show 4 replies
Your function doesn't actually return anything.
I think you're missing the:
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
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...
However, why are you using a sys_refcursor in PL/SQL code?
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.
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.