CODEWITHSUNDEEP
oracle-databasefor-loopplsqlexecute-immediate
NablaDelta
NablaDelta

Reputation: 105

Loop over execute immediate

I want to loop over a dynamic query that was concatenated before. However, even with this simple example

begin
  for rec in (execute immediate 'select dummy from dual') loop
    dbms_output.put_line(rec.dummy);
  end loop;
end;

I get the error

encountered the symbol immediate when expecting one of the following

How to achieve such a loop?

Upvotes: 0

Views: 47

Answers (2)

MT0
MT0

Reputation: 168623

You can use EXECUTE IMMEDIATE and BULK COLLECT the results into a collection variable and then loop through that:

DECLARE
  TYPE string_list IS TABLE OF VARCHAR2(1);
  v_strings string_list;
BEGIN
  EXECUTE IMMEDIATE 'select dummy from dual'
    BULK COLLECT INTO v_strings;

  FOR i IN 1 .. v_strings.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_strings(i));
  END LOOP;
END;
/

Which outputs:

X

If you have multiple columns you can use:

DECLARE
  TYPE number_list IS TABLE OF NUMBER;
  TYPE string_list IS TABLE OF VARCHAR2(1);
  v_numbers number_list;
  v_strings string_list;
BEGIN
  EXECUTE IMMEDIATE 'select dummy, level from dual connect by level <= 5'
    BULK COLLECT INTO v_strings, v_numbers;

  FOR i IN 1 .. v_strings.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_strings(i) || ', ' || v_numbers(i));
  END LOOP;
END;
/

or you can create an object type:

CREATE TYPE data_obj IS OBJECT( dummy VARCHAR2(1), lvl NUMBER );

and then use:

DECLARE
  TYPE data_list IS TABLE OF data_obj;
  v_data data_list;
BEGIN
  EXECUTE IMMEDIATE 'select data_obj(dummy, level) from dual connect by level <= 5'
    BULK COLLECT INTO v_data;

  FOR i IN 1 .. v_data.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_data(i).dummy || ', ' || v_data(i).lvl);
  END LOOP;
END;
/

Both of which output:

X, 1
X, 2
X, 3
X, 4
X, 5

fiddle

Upvotes: 1

cengiz sevimli
cengiz sevimli

Reputation: 2105

You cannot directly use Execute immediate for looping in a cursor. Instead, you need a "REF CURSOR":

DECLARE
    v_cur SYS_REFCURSOR;  -- Declare a cursor variable
    rec   VARCHAR2(100);  -- Variable to hold the result of each row
BEGIN
    -- Open the cursor for the dynamic SQL query
    OPEN v_cur FOR 'SELECT dummy FROM dual';
    
    -- Fetch each row from the cursor
    LOOP
        FETCH v_cur INTO rec;  -- Fetch each row into 'rec'
        EXIT WHEN v_cur%NOTFOUND;  -- Exit the loop when no more rows
        --DO STUFF YOU NEED FOR EACH INDEX HERE
    END LOOP;
    
    CLOSE v_cur;  -- Close the cursor
END;

Upvotes: 1

Related Questions