Oracle PL/SQL EXECUTE IMMEDIATE in FOR loop

Question

I want to do something like the following.

I need a loop for all the IDs from dynamic table,

FOR ID_ROW IN (EXECUTE IMMEDIATE 'SELECT ID FROM ' || SRC_TABLE) LOOP
    -- some SP calling with ID_ROW.ID
END LOOP;

It is not working, how can I make it run?

Or if following works then also I can work out a solution,

EXECUTE IMMEDIATE 'SELECT ID FROM ' || SRC_TABLE INTO ID_ROW

Where ID_ROW would be of type type CUSTOM_ARRAY is table of VARCHAR2(64);

How many rows are you expecting? If that isn't too many, using a bulk collect to store all ids in a collection and then iterating over the collection is most probably going to be faster (but it probably won't work with millions of rows) — user330315
– user330315, Commented Dec 17, 2015 at 12:01
It is not working, that's why I have put the question, Please see edited question. — Akshay
– Akshay, Commented Dec 17, 2015 at 12:02

Avrajit Roy · Accepted Answer · 2015-12-17 12:24:01Z

2

If you have collection to be fetched out try this.

DECLARE
TYPE num_tab IS TABLE OF NUMBER;
num num_tab;
BEGIN
EXECUTE IMMEDIATE ' SELECT NUM  FROM NUMBER_TAB 'BULK COLLECT INTO NUM;
END;

answered Dec 17, 2015 at 12:24

Avrajit Roy

3,3131 gold badge15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1