Stored Procedure (SP) where inside SP, 2 SP separated for 2 insertion from table. Both table contains more than 25 columns in each temp & main table. Below is query-
create or replace procedure sp_main as procedure tbl1_ld as cursor c1 is select * from tmp1; type t_rec1 is table of c1%rowtype; v_rec1 t_rec1; begin open c1; loop fetch c1 bulk collect into v_rec1 limit 1000; exit when v_rec1.count=0; insert into tbl1 values v_rec1; end loop; end tbl1_ld; procedure tbl2_ld as cursor c2 is select * from tmp2; type t_rec2 is table of c2%rowtype; v_rec2 t_rec2; begin open c2; loop fetch c2 bulk collect into v_rec2 limit 1000; exit when v_rec2.count=0; insert into tbl2 values v_rec2; end loop; end tbl2_ld; begin null; end sp_main; /
EXECUTE IMMEDIATE 'insert into tbl1 select * from tmp1'; for insertion inside both SP
tbl1_ld & tbl2_ld instead of using
cursor, SP compiled but no record has been inserted.
Well, you didn’t actually run any of these procedures. The last few lines of your code should be
<snip> end tbl2_ld; begin tbl1_ld; --> this tbl2_ld --> this end sp_main; /
On the other hand, I prefer avoiding
insert into ... select * from because it just loves to fail when you modify tables’ description and don’t fix code that uses those tables.
Yes, I know – it is just boring to name all 25 columns, but – in my opinion – it’s worth it. Therefore, I’d just
begin insert into tbl1 (id, name, address, phone, ... all 25 columns) select id, name, address, phone, ... all 25 columns from tmp1; insert into tbl2 (id, name, address, phone, ... all 25 columns) select id, name, address, phone, ... all 25 columns from tmp2; end;
In other words, no cursors, types, loops, … nothing. Could have been pure SQL (i.e. no PL/SQL). If you want to restrict number of rows inserted, use e.g.
... where rownum <= 1000 (if that’s why you used the
As of dynamic SQL you mentioned (
execute immediate): why would you use it? There’s nothing dynamic in code you wrote.