Akshay
Akshay

Reputation: 3866

Oracle PL/SQL EXECUTE IMMEDIATE in FOR loop

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);

Upvotes: 1

Views: 3414

Answers (1)

Avrajit Roy
Avrajit Roy

Reputation: 3303

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;

Upvotes: 2

Related Questions