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;