Oracle / PLSQL: Cursor within a cursor

 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 second cursor
      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.