Nested Cursors in PL/SQL

Static cursors can only access static objects. In other words, static cursors work only if all tables and columns are known at compile time.

If you need to access a table whose name will only be known during execution, you'll have to use dynamic SQL. For instance, you could use a REF CURSOR in your case:

DECLARE   var_table_name    VARCHAR2(30);   var_dimension_key VARCHAR2(30);   cur_dimension_key SYS_REFCURSOR;BEGIN   FOR cur_all_dim IN (SELECT table_name                         FROM dba_tables                        WHERE dba_tables.tablespace_name = 'USERS'                          AND dba_tables.owner = 'DWH_CORE'                          AND UPPER(dba_tables.table_name) LIKE ('%DIM%%')                          AND UPPER(dba_tables.table_name) NOT LIKE ('%TEMP%')                          AND UPPER(dba_tables.table_name) NOT LIKE ('%DEBUG%')                          AND UPPER(dba_tables.table_name) NOT LIKE ('%LOG%'))    LOOP            OPEN cur_dimension_key        FOR 'SELECT dimention_key               FROM ' || cur_all_dim.table_name;      LOOP         FETCH cur_dimensions_key INTO var_dimension_key;         EXIT WHEN cur_dimensions_key%NOTFOUND;         dbms_output.put_line(cur_all_dim.table_name);         dbms_output.put_line(var_dimension_key);            END LOOP;      CLOSE cur_dimension_key;   END LOOP;END;

    create or replace PROCEDURE PROC_NESTED_CURSOR     AS     CUR1 SYS_REFCURSOR;    CUR2 SYS_REFCURSOR;    LV_DEPTID NUMBER;    LV_DEPTNAME VARCHAR2(200);    LV_EMPID NUMBER;    LV_FSTNAME VARCHAR2(200);    LV_SALARY NUMBER(8,2);    LV_JOBID VARCHAR2(20);    BEGIN      OPEN CUR1 FOR         select department_id, department_name from departments;      LOOP         FETCH CUR1 INTO LV_DEPTID,LV_DEPTNAME;         EXIT WHEN CUR1%NOTFOUND;            dbms_output.put_line('                                                                                                   ');        dbms_output.put_line('DEPARTMENT ID  '||LV_DEPTID ||'                               '|| 'DEPARTMENT NAME  '||LV_DEPTNAME);        dbms_output.put_line('---------------------------------------------------------------------------------------------------');        dbms_output.put_line(RPAD('EMPLOYEE NUMBER',20)||RPAD('EMPLOYEE NAME',20) ||RPAD('SALARY',20)||RPAD('JOB',20) );          OPEN CUR2 FOR SELECT EMPLOYEE_ID,FIRST_NAME,SALARY,JOB_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = LV_DEPTID;          LOOP             FETCH CUR2 INTO LV_EMPID,LV_FSTNAME,LV_SALARY,LV_JOBID;            EXIT WHEN CUR2%NOTFOUND;             dbms_output.put_line(RPAD( LV_EMPID,20)|| RPAD(LV_FSTNAME,20)||RPAD(LV_SALARY,20)||LV_JOBID);            END LOOP;       END LOOP;     END PROC_NESTED_CURSOR;/**This will print like this:-    Department Number :10               Department Name : XXXX    ________________________________________________________    EMPLOYEE NUMBER    EMPLOYEE NAME      SALARY         JOB    XXXXXX                  XXXXXX                  XXXX            XXXXX    XXXXXX                  XXXXXX                  XXXX            XXXXX    .........**/