Nested Cursors in PL/SQL

38,829

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;
Share:
38,829
user3364656
Author by

user3364656

Updated on May 13, 2020

Comments

  • user3364656
    user3364656 over 3 years

    I am working with Oracle PL/SQL. I´m trying to define Nested Cursors, which means that the output from the first Cursor should be the input for the second. To be more specific: the first one should store tables with a specific prefix in it. The second one should store all values from an attribute from all the tables which are in the first one.

    Here is my code snippet. I hope it makes my problem a little more clear:

    DECLARE
        var_table_name  VARCHAR2(30);
        var_dimension_key  VARCHAR2(30);
    
    CURSOR cur_all_dim IS  
        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%');
    
    CURSOR cur_dimension_key IS
        SELECT dimension_key FROM var_table_name;
    
    
    BEGIN
    OPEN cur_all_dim;
    
    LOOP
    EXIT WHEN cur_all_dim%NOTFOUND;
    
        FETCH cur_all_dim INTO var_table_name;
    
        OPEN cur_dimensions_key;
        LOOP
        EXIT WHEN cur_dimensions_key%NOTFOUND;
        FETCH cur_dimensions_key INTO var_dimension_key;
        dbms_output.put_line (var_table_name);
        dbms_output.put_line (var_dimension_key);
    
    
        END LOOP;
        CLOSE cur_dimension_key;
    END LOOP;
    CLOSE cur_all_dim;
    END;
    
  • user3364656
    user3364656 over 9 years
    Thanks.. i think i am beginning to understand how this should work. But where is "cur_all_dim" defined? The definition is gone in your code but it is already used. So i am getting the error it is not declarated.
  • Vincent Malgrat
    Vincent Malgrat over 9 years
    I've used an implicit cursor. The cursor variable is local to the loop, in a similar way as in standard procedural FOR loop (you don't need to declare i in FOR i IN 1..n).
  • user3364656
    user3364656 over 9 years
    ok i understand that. But why do i get an error 'CUR_DIMENSIONS_KEY' is not declarated? (PLS-00201)
  • Vincent Malgrat
    Vincent Malgrat over 9 years
    Probably because there was a typo in my code CUR_DIMENSIONS_KEY was not declared, but CUR_DIMENSION_KEY was !
  • user3364656
    user3364656 over 9 years
    ohh thanks.. i should have seen that for my own. You helped me realy a lot!
  • user3364656
    user3364656 over 9 years
    is it possible to nest even more cursors? So i would have a third one which uses the second one?
  • Vincent Malgrat
    Vincent Malgrat over 9 years
    Yes, you can define a second SYS_REFCURSOR variable and open it dynamically inside the loop.
  • Emil G
    Emil G over 7 years
    According to the examples I have found, the order of EXIT WHEN and FETCH should be switched (first FETCH, then EXIT WHEN). This might not be a problem but when I used this technique, I ran into a problem where the last post in the result set was processed twice.