1  declare
  2   v varchar2(40);
  3   rc sys_refcursor;
  4   cursor c is select cursor(select dname from dept) c from dual;
  5  begin
  6    open c;
  7    fetch c into rc;
  8    fetch rc into v;
  9    dbms_output.put_line(v);
 10    close rc;
 11    close c;
 12* end;
SQL> /
ACCOUNTING

PL/SQL procedure successfully completed.

FeedbackDownload
Share to:
Share to FacebookShare to TwitterShare to EmailShare to PrintShare to GmailShare to More2
Categories
Home
Master Index
Master Glossary
CURSOR Expressions
A CURSOR expression returns a nested cursor. This form of expression is equivalent to the PL/SQL REF CURSOR and can be passed as a REF CURSOR argument to a function.

  1  declare
  2   v varchar2(40);
  3   rc number; --sys_refcursor;
  4   cursor c is select cursor(select dname from dept) c from dual;
  5  begin
  6    open c;
  7    fetch c into rc;
  8  --  fetch rc into v;
  9    dbms_output.put_line(v);
 10  --  close rc;
 11    close c;
 12* end;
SQL> /
  fetch c into rc;
               *
ERROR at line 7:
ORA-06550: line 7, column 16:
PLS-00386: type mismatch found at 'RC' between FETCH cursor and INTO variables
ORA-06550: line 7, column 3:
PL/SQL: SQL Statement ignored

SQL> select dump(cursor(select * from dual)) d from dual;
select dump(cursor(select * from dual)) d from dual
            *
ERROR at line 1:
ORA-22902: CURSOR expression not allowed

SQL> ed
Wrote file afiedt.buf

  1  declare
  2   v varchar2(100);
  3   sr sys_refcursor;
  4   cursor c is select dname,(cursor(select ename
  5               from emp e where e.deptno=d.deptno)) r from dept d;
  6  begin
  7   open c;
  8   loop
  9    fetch c into v,sr;
 10    EXIT WHEN c%NOTFOUND;
 11    dbms_output.put_line(v);
 12     loop
 13        FETCH sr INTO v;
 14        EXIT WHEN sr%NOTFOUND;
 15        dbms_output.put_line('--- '||v);
 16     end loop;
 17     close sr;
 18   end loop;
 19   close c;
 20* end;
SQL> /
ACCOUNTING
--- CLARK
--- KING
--- MILLER
RESEARCH
--- SMITH
--- JONES
--- SCOTT
--- ADAMS
--- FORD
SALES
--- ALLEN
--- WARD
--- MARTIN
--- BLAKE
--- TURNER
--- JAMES
OPERATIONS

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2   e sys_refcursor;
  3   cursor c1 is select e from dual;
  4  begin
  5   open c1;
  6   close c1;
  7* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2   e boolean;
  3   cursor c1 is select e from dual;
  4  begin
  5   open c1;
  6   close c1;
  7* end;
SQL> /
 cursor c1 is select e from dual;
                     *
ERROR at line 3:
ORA-06550: line 3, column 22:
PLS-00382: expression is of wrong type