I wrote 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;
/
I used 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.
>Solution :
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 limit clause).
As of dynamic SQL you mentioned (execute immediate): why would you use it? There’s nothing dynamic in code you wrote.