CODEWITHSUNDEEP
sqloracle-databaseplsql
ZAX
ZAX

Reputation: 1006

Dynamically Refer To Table In FOR loop

I'm developing a script in an Oracle database that will end up emailing users in question. I would like to only email users one time and will need to dynamically refer to some materialized views in order to do so or will have to write around 40 different versions where I simply change the table name of the loop below.

I have a loop that works currently that looks like:

for response in (select result.* from mv_table1 result) loop   
    --for response in (select * from v_dynamic_database) loop   
         UTL_SMTP.write_data(response.data);
    end loop;

You can see where I commented out a place where I tried using a variable (varchar that I assign dynamically in another loop above this one) instead of an official table name, however the procedure stops working when I do this.

Is there any way to dynamically mondify the database name in a for loop?

Upvotes: 0

Views: 61

Answers (1)

GolezTrol
GolezTrol

Reputation: 116190

You can open a query string as a cursor and loop through that. In the string, you can easily use a variable for the table name.

Something like this:

DECLARE
    v_cur sys_refcursor;
    v_data varchar2(4000);
BEGIN
    OPEN v_cur FOR 'select result.data from ' || mv_table1 || ' result';
    loop
      FETCH v_cur
      INTO v_data;
      EXIT WHEN v_cur%notfound;
      UTL_SMTP.write_data(v_data);
    END LOOP;
    CLOSE v_cur;
END;

Based on answer by Solomon Yakobson on Oracle Community forum.

Upvotes: 4

Related Questions