PL/SQL nested loop (loop within a loop)

Question

Below is a PL/SQL I'm working on

declare
v_sql varchar2(500);
BEGIN
for t in (
    SELECT distinct ID 
    FROM TABLEB
    ) loop
    for c in (
        select * from (
            select 'delete from ' as test 
                from  dual
            union all
            select 'TABLEA'||' where ' as test 
                from dual
            union all
            select 'ID='||t.ID 
                from dual
            )
        ) loop
        v_sql := v_sql || c.test;
        end loop;
    dbms_output.put_line(v_sql);
    end loop; 
END;
/

The result I'm getting is this

delete from TABLEA where ID=1
delete from TABLEA where ID=1delete from TABLEA where ID=2

I want

delete from TABLEA where ID=1
delete from TABLEA where ID=2

Any help on the PLSQL will be appreciated

Littlefoot · Accepted Answer · 2018-03-01 18:47:04Z

What is the purpose of the inner FOR loop? It does nothing that requires a loop, and can be simply rewritten like this:

declare
  v_sql varchar2(500);
begin
  for t in (select distinct id from tableb) loop
    v_sql := 'delete from tablea where id = ' || t.id ||';';
    dbms_output.put_line(v_sql);
  end loop; 
end;
/

BTW, it seems that you're missing the terminating semicolon in line v_sql := ...

Demonstration on HR's DEPARTMENTS table:

SQL> declare
  2    v_sql varchar2(500);
  3  begin
  4    for t in (select distinct department_id id from departments) loop
  5      v_sql := 'delete from tablea where id = ' || t.id ||';';
  6      dbms_output.put_line(v_sql);
  7    end loop;
  8  end;
  9  /
delete from tablea where id = 10;
delete from tablea where id = 20;
delete from tablea where id = 30;
delete from tablea where id = 40;
delete from tablea where id = 50;
delete from tablea where id = 60;
<snip>

Bob Jarvis - Слава Україні · Accepted Answer · 2018-03-01 18:40:04Z

1

You're not clearing the buffer after you've printed the statement, so you're appending the next statement to the first one. To clear the buffer, add

v_sql := NULL;

after the line which reads

dbms_output.put_line(v_sql);

Best of luck.

answered Mar 1, 2018 at 18:40

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Collectives™ on Stack Overflow

PL/SQL nested loop (loop within a loop)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related