create or replace
PROCEDURE refcursor1
AS
TYPE r_cursor IS REF CURSOR;
rcv_emp r_cursor;
TYPE rec_emp IS record
(
empno NUMBER,
ename VARCHAR2(20 CHAR),
deptno number
);
recv_emp rec_emp;
recv_emp2 rec_emp;
-------------------------------------------------------
PROCEDURE printemployeedetails AS
BEGIN
loop
fetch rcv_emp INTO recv_emp;
exit WHEN rcv_emp%notfound;
dbms_output.put_line(recv_emp.empno||'-'||recv_emp.ename||'-'||recv_emp.deptno);
END loop;
END;
-------------------------------------------------------
PROCEDURE printemployeedetails2(p_emp r_cursor) IS
BEGIN
loop
fetch p_emp INTO recv_emp2;
exit WHEN p_emp%notfound;
dbms_output.put_line(recv_emp2.empno||'-'||recv_emp2.ename||'-'||recv_emp2.deptno);
end loop;
END;
-------------------------------------------------------
BEGIN
FOR i IN (SELECT deptno FROM dept order by deptno)
loop
OPEN rcv_emp FOR SELECT empno,ename,deptno FROM emp WHERE deptno=i.deptno;
dbms_output.put_line(i.deptno);
dbms_output.put_line('--------------------');
dbms_output.put_line('calling printemployeedetails');
printemployeedetails;
dbms_output.put_line(' ');
dbms_output.put_line('calling printemployeedetails2');
dbms_output.put_line(' ');
printemployeedetails2(rcv_emp);
CLOSE rcv_emp;
END loop;
end;
Output:
10
--------------------
calling printemployeedetails
7839-KING-10
7782-CLARK-10
7934-MILLER-10
calling printemployeedetails2
20
--------------------
calling printemployeedetails
7566-JONES-20
7788-SCOTT-20
7902-FORD-20
7369-SMITH-20
7876-ADAMS-20
calling printemployeedetails2
30
--------------------
calling printemployeedetails
7698-BLAKE-30
7499-ALLEN-30
7521-WARD-30
7654-MARTIN-30
7844-TURNER-30
7900-JAMES-30
calling printemployeedetails2
40
--------------------
calling printemployeedetails
calling printemployeedetails2
Hello All,
If i open a cursor once can i fetch the elements of a cursor n times like above? i see only either one of those procedures are printing the details but not both.
Wonder why as i am passing the same ref cursor to a second procedure.
It's neither throwing me an error saying the elements of ref cursor are already fetched.
Thank you.