Oracle/PLSQL: Cursor within a cursor

Question:  In PSQL, I want to declare a cursor within cursor. The second cursor should use a value from the first cursor in the "where clause". How can I do this?

Answer:  Below is an example of how to declare a cursor within a cursor.

In this example, we have a cursor called get_tables that retrieves the owner and table_name values. These values are then used in a second cursor called get_columns.

create or replace procedure MULTIPLE_CURSORS_PROC is
    v_owner varchar2(40);
    v_table_name varchar2(40);
    v_column_name varchar2(100);

    /* First cursor */
    cursor get_tables is
    select distinct tbl.owner, tbl.table_name
    from all_tables tbl
    where tbl.owner = 'SYSTEM';

    /* Second cursor */
    cursor get_columns is
    select distinct col.column_name
    from all_tab_columns col
    where col.owner = v_owner
    and col.table_name = v_table_name;

   begin

      -- Open first cursor
      open get_tables;
      loop
          fetch get_tables into v_owner, v_table_name;

          open get_columns;
          loop
              fetch get_columns into v_column_name;

          end loop;
          close get_columns;

       end loop;
       close get_tables;

EXCEPTION
WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
end MULTIPLE_CURSORS_PROC;

The trick to declaring a cursor within a cursor is that you need to continue to open and close the second cursor each time a new record is retrieved from the first cursor. That way, the second cursor will use the new variable values from the first cursor.