CODEWITHSUNDEEP
sqloracle-databasevariablesfor-loop
user3197733
user3197733

Reputation: 1

How can I use table name as a variable in nested for loops in oracle pl/sql?

I have a query like this:

 v_sql:= 'select count(1) from '||v_tbl||' where col1 = ' || month_var || ' and ds =''T''';
 execute immediate v_sql into v_row_cnt;

   for j in 1..v_row_cnt
    loop
      for i in (select b.* from
               (select a.*, rownum as rn
                from (select * from MY_TABLE where col1 = month_var and DS = 'T') a
                 ) b
               where rn=j)
      loop

          do_something;
      end loop;
     end loop;

Now, my problem is, I can't hard code MY_TABLE here. I need to use a variable. I am currently doing it this way because I need to process data row by row.

Any ideas how to do this?

Thanks. Ronn

Upvotes: 0

Views: 1751

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

You would use dynamic SQL to build a cursor.

There is an inefficiency in your logic here: you start by counting the number of rows in a given table, then you execute the same query once for each row (another example of Schlemiel the Painter's algorithm).

You don't need to do that, just loop over the cursor, this will execute the query once only. For example:

SQL> DECLARE
  2     l_cursor SYS_REFCURSOR;
  3     l_table VARCHAR2(32) := 'ALL_OBJECTS';
  4     l_name VARCHAR2(32);
  5  BEGIN
  6     OPEN l_cursor FOR 'SELECT object_name
  7                          FROM ' || l_table
  8                    || ' WHERE rownum <= 5 ';
  9     LOOP
 10        FETCH l_cursor INTO l_name;
 11        EXIT WHEN l_cursor%NOTFOUND;
 12        -- do_something
 13        dbms_output.put_line(l_name);
 14     END LOOP;
 15     CLOSE l_cursor;
 16  END;
 17  /

C_OBJ#
I_OBJ#
TAB$
CLU$
C_TS#

You don't need to count the number of rows beforehand, if the cursor is empty the loop with exit immediately.

Upvotes: 2

Related Questions