Cursor within a cursor : Nested Cursor « Cursor « Oracle PL / SQL

1. Oracle PL / SQL
2. Cursor
3. Nested Cursor

Cursor within a cursor

 
SQL> -- Cursor within a cursor
SQL>
SQL>     create or replace procedure MULTIPLE_CURSORS_PROC is
  2          v_owner varchar2(40);
  3          v_table_name varchar2(40);
  4          v_column_name varchar2(100);
  5
  6          
  7          cursor firstCursor is
  8          select distinct tbl.owner, tbl.table_name
  9          from all_tables tbl
 10          where tbl.owner = 'SYSTEM';
 11
 12          
 13          cursor secondCursor is
 14          select distinct col.column_name
 15          from all_tab_columns col
 16          where col.owner = v_owner
 17          and col.table_name = v_table_name;
 18
 19         begin
 20
 21            
 22            open firstCursor;
 23            loop
 24                fetch firstCursor into v_owner, v_table_name;
 25
 26                dbms_output.put_line('v_owner: '||v_owner);
 27                dbms_output.put_line('v_table_name: '||v_table_name);
 28
 29                open secondCursor;
 30                loop
 31                    fetch secondCursor into v_column_name;
 32                    dbms_output.put_line('v_column_name: '||v_column_name);
 33                end loop;
 34                close secondCursor;
 35
 36             end loop;
 37             close firstCursor;
 38
 39      EXCEPTION
 40      WHEN OTHERS THEN
 41            raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
 42      end MULTIPLE_CURSORS_PROC;
 43      /

Procedure created.

SQL>
SQL>     exec MULTIPLE_CURSORS_PROC();

           
         
  

Related examples in the same category