SQL> SQL> select * from a1; C1 C2 C3 -- ---------- ------ G 111 G_tag O 121 O_tag Q 131 Q_tag a 141 a_tag b 151 b_tag c 161 c_tag d 171 d_tag e 181 e_tag f 191 f_tag g 201 g_tag h 211 h_tag 11 rows selected. SQL> select * from a2; C1 C2 -- ---------- G 110 O 120 Q 130 a 140 b 150 c 160 d 170 e 180 f 190 g 200 h 210 11 rows selected. SQL> SQL> set serveroutput on SQL> declare 2 type c1_type is table of A2.c1%type; 3 type c2_type is table of A2.c2%type; 4 type c3_type is table of A1.c3%type; 5 x c1_type; 6 y c2_type; 7 w c3_type; 8 begin 9 select c1,c2 bulk collect into x,y from A2; 10 FORALL i in 1..x.count 11 update A1 set c2=y(i) where c1=x(i) 12 RETURNING c3 BULK COLLECT INTO w; 13 14 For i in 1..w.count loop 15 DBMS_OUTPUT.PUT_LINE(w(i)); 16 end loop; 17 18 end; 19 / G_tag O_tag Q_tag a_tag b_tag c_tag d_tag e_tag f_tag g_tag h_tag PL/SQL procedure successfully completed. SQL> select * from a1; C1 C2 C3 -- ---------- ------ G 110 G_tag O 120 O_tag Q 130 Q_tag a 140 a_tag b 150 b_tag c 160 c_tag d 170 d_tag e 180 e_tag f 190 f_tag g 200 g_tag h 210 h_tag 11 rows selected. SQL> rollback; Rollback complete. SQL> SQL> -- select within FORALL SQL> declare 2 type c1_type is table of A2.c1%type; 3 type c2_type is table of A2.c2%type; 4 type c3_type is table of A1.c3%type; 5 x c1_type; 6 y c2_type; 7 w c3_type; 8 begin 9 select c1,c2 bulk collect into x,y from A2; 10 FORALL i in 1..x.count 11 update A1 set c2=(select c2 from a3 where c1=x(i)) where c1=x(i) 12 RETURNING c3 BULK COLLECT INTO w; 13 14 For i in 1..w.count loop 15 DBMS_OUTPUT.PUT_LINE(w(i)); 16 end loop; 17 18 end; 19 / G_tag O_tag Q_tag a_tag b_tag c_tag d_tag e_tag f_tag g_tag h_tag PL/SQL procedure successfully completed. SQL> select * from a1; C1 C2 C3 -- ---------- ------ G 111 G_tag O 121 O_tag Q 131 Q_tag a 141 a_tag b 151 b_tag c 161 c_tag d 171 d_tag e 181 e_tag f 191 f_tag g 201 g_tag h 211 h_tag 11 rows selected. SQL> rollback; Rollback complete. SQL> SQL> spool off