Hello all, I need help with a pl/sql code.
The purpose is to delete the data from a number of tables (75+). All these tables have a common column and can have millions of rows.
The column value for row deletion will be configured in a "delete value config" table that gets populated by some other routine (can be in thousands).
The order in which data can be deleted from these table can be defined in a "delete order config" table.
The deletion can happen after hours.
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0
Code -
Procedure will cycle through the "delete order config" table in delete order and bulk delete rows from each table (input will be the list of column values for delete filter).
Procedure will read delete filter values from "delete value config" table in a collection using LIMIT clause and call other procedure to do the deletion.
Below tables and data is an overly simplified demo.
"delete value config" table = name_to_del
"delete order config" table = del_order_config
Various data tables = myemp, myemp_2, myemp_3
CREATE TABLE name_to_del (ename VARCHAR2(30));
INSERT INTO name_to_del VALUES ('X');
INSERT INTO name_to_del VALUES ('Y');
INSERT INTO name_to_del VALUES ('Z');
CREATE TABLE myemp (eid NUMBER, ename VARCHAR2(30), etype VARCHAR2(30));
INSERT INTO myemp VALUES (1,'A','FULL');
INSERT INTO myemp VALUES (2,'B','FULL');
INSERT INTO myemp VALUES (3,'C','FULL');
INSERT INTO myemp VALUES (4,'X','TEMP');
CREATE TABLE myemp_2 (eid NUMBER, ename VARCHAR2(30), etype VARCHAR2(30));
INSERT INTO myemp_2 VALUES (1,'A','FULL');
INSERT INTO myemp_2 VALUES (2,'B','FULL');
INSERT INTO myemp_2 VALUES (3,'C','FULL');
INSERT INTO myemp_2 VALUES (4,'X','TEMP');
CREATE TABLE myemp_3 (eid NUMBER, ename VARCHAR2(30), etype VARCHAR2(30));
INSERT INTO myemp_3 VALUES (1,'A','FULL');
INSERT INTO myemp_3 VALUES (2,'B','FULL');
INSERT INTO myemp_3 VALUES (3,'C','FULL');
INSERT INTO myemp_3 VALUES (4,'X','TEMP');
CREATE TABLE del_order_config (table_nm VARCHAR2(30), del_order NUMBER);
INSERT INTO del_order_config VALUES ('myemp_3',1);
INSERT INTO del_order_config VALUES ('myemp_2',2);
INSERT INTO del_order_config VALUES ('myemp',3);
CREATE or REPLACE TYPE t_emp_list IS TABLE OF VARCHAR2(30);
---------------------------------------------------------------------------
--Procedure will cycle through the "delete order config" table in delete order and bulk delete rows from each table (input will be the list of column values for delete filter).
CREATE OR REPLACE PROCEDURE del_emp (lv_emp_list IN t_emp_list)
IS
CURSOR cur_stg_table_order IS
SELECT table_nm
FROM del_order_config
ORDER BY del_order;
BEGIN
FOR rec IN cur_stg_table_order
LOOP
EXECUTE IMMEDIATE 'DELETE FROM ' --> [Error] PLS-00306 (48: 29): PLS-00306: wrong number or types of arguments in call to '||' -- How to resolve this?
|| rec.table_nm
|| ' WHERE ename IN (select * from table('
|| lv_emp_list
|| '))';
END LOOP;
END del_emp;
/
----------------------------------------------------------------------------
--Procedure will read delete filter values from "delete value config" table in a collection using LIMIT clause and call other procedure to do the deletion.
CREATE OR REPLACE PROCEDURE del_emp_wrapper
IS
CURSOR stg_emp_list IS SELECT ename FROM name_to_del;
v_emp_list t_emp_list;
l_limit NUMBER := 1;
BEGIN
OPEN stg_emp_list;
LOOP
FETCH stg_emp_list BULK COLLECT INTO v_emp_list LIMIT l_limit;
EXIT WHEN v_emp_list.COUNT = 0;
del_emp (v_emp_list);
END LOOP;
END del_emp_wrapper;
/
Thank you!