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();