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.