Oracle Procedure to insert all records from one staging table into main table-CodePudding

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.

CodePudding user response：

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.