Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

bulk insert and delete

Sevdimali IsayevApr 5 2018 — edited Apr 11 2018

Hi, I have two tables: employees and emp_arch.

My requirement is insert rows from employees to emp_arch and delete these inserted rows from employees.

I created this procedure but row count is really big (about one million). And I have to do this operation periodically.

CREATE OR REPLACE procedure SI_EMP_DEL_INSERT1 IS

   CURSOR si_c_emp

   IS

      SELECT m1.ROWID rrid, m1.*

        FROM employees m1

       WHERE salary < 5000;

BEGIN

   FOR c2 IN si_c_emp

   LOOP

  

      EXECUTE IMMEDIATE 'INSERT INTO emp_arch

         SELECT *

           FROM employees

           WHERE ROWID = '''||c2.rrid||'''';

                       COMMIT;

          

           DELETE FROM EMPLOYEES   WHERE rowid =c2.rrid;

            commit;

          COMMIT;

   END LOOP;

END;

/

I wonder is it possible to do it with bulk insert and delete.

I try to write it with using bulk option like this:

But here I confused how to delete these inserted rows.

create or replace procedure SI_EMP_DEL_INSERT2 is

cursor si_c_emp is

select employee_id, first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id from employees where salary<5000;

type si_t_emp_table is table of employees%rowtype;

si_t_emp si_t_emp_table;

begin

open si_c_emp;

loop

fetch si_c_emp  bulk collect  into si_t_emp;

forall si_index in 1..si_t_emp.count save exceptions

insert into emp_arch values si_t_emp(si_index);

exit when si_c_emp%notfound;

end loop;

close si_c_emp;

EXCEPTION WHEN OTHERS THEN

if SQLCODE=-24381 then

    for si_index_2 in 1..SQL%BULK_EXCEPTIONS.count

        loop

            begin

                dbms_output.put_line(SQL%BULK_EXCEPTIONS(si_index_2).error_index);

            end;

         end loop;  

else RAISE;

end if;

end;

This post has been answered by unknown-7404 on Apr 6 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 4 2018
Added on Apr 5 2018
6 comments
1,457 views