By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t1 (col1, col2) as
select 1, 'one' from dual
union all
select 2, 'two' from dual
2 rows affected
create table t2 (pk number primary key, col1 number, col2 varchar2(10));
declare
l_col1 t1.col1%type;
cursor l_cursor is
select col1 from t1 order by col1;
begin
open l_cursor;
loop
fetch l_cursor into l_col1;
exit when l_cursor%notfound;
dbms_output.put_line('l_col1: ' || l_col1);
for r in (
select rownum as rn, col1, col2 from t1 where col1 = l_col1
)
loop
dbms_output.put_line(' r.rn ' || r.rn || ' col1 ' || r.col1 || ' col2 ' || r.col2);
end loop;
end loop;
close l_cursor;
end;
/
1 rows affected
dbms_output:
l_col1: 1
r.rn 1 col1 1 col2 one
l_col1: 2
r.rn 1 col1 2 col2 two
declare
l_col1 t1.col1%type;
cursor l_cursor is
select col1 from t1 order by col1;
begin
open l_cursor;
loop
fetch l_cursor into l_col1;
exit when l_cursor%notfound;
dbms_output.put_line('l_col1: ' || l_col1);
for r in (
select rownum as rn, col1, col2 from t1 where col1 = l_col1
)
loop
dbms_output.put_line(' r.rn ' || r.rn || ' col1 ' || r.col1 || ' col2 ' || r.col2);
insert into t2 (pk, col1, col2)
values (r.rn, r.col1, r.col2);
dbms_output.put_line('inserted ' || sql%rowcount || ' rows');
end loop;
end loop;
close l_cursor;
end;
/
dbms_output:
l_col1: 1
r.rn 1 col1 1 col2 one
inserted 1 rows
l_col1: 2
r.rn 1 col1 2 col2 two
ORA-00001: unique constraint (FIDDLE_MMRTNCSWTDQLIXBBNSCV.SYS_C00257071) violated
ORA-06512: at line 17
ORA-06512: at line 17