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;
Author by
user3364656
Updated on May 13, 2020Comments
-
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 over 9 yearsThanks.. 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 over 9 yearsI'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 declarei
inFOR i IN 1..n
). -
user3364656 over 9 yearsok i understand that. But why do i get an error 'CUR_DIMENSIONS_KEY' is not declarated? (PLS-00201)
-
Vincent Malgrat over 9 yearsProbably because there was a typo in my code
CUR_DIMENSIONS_KEY
was not declared, butCUR_DIMENSION_KEY
was ! -
user3364656 over 9 yearsohh thanks.. i should have seen that for my own. You helped me realy a lot!
-
user3364656 over 9 yearsis it possible to nest even more cursors? So i would have a third one which uses the second one?
-
Vincent Malgrat over 9 yearsYes, you can define a second
SYS_REFCURSOR
variable and open it dynamically inside the loop. -
Emil G over 7 yearsAccording 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.