Hi
i have a requirement here where the old rows need to be deleted from a list of tables(non partitioned) based on a date column value which determines if the record is older than 730 days.. the column name can be different for different tables.( column name, table name are configured in the config table ,one row for each record)
i am trying to do it using 2 cursors where cursor 1 gives the table name and cursor 2 fetches all teh rowids which qualify the deletion criteria and dynamic sql deletes it with a limited no: of rows (using ROWID) at a time until all rows are deleted.. i am able to do it when i have single table but not for set of tables fetched at runtime and also not able to change column name at run time. not sure how to make cursor 2 read the data returned by cursor 1. Below is my code:
SET SERVEROUTPUT ON;
declare
v_schema varchar2(10) := 'sample_schema';
v_table_names VARCHAR2(100):= 'employee' ;
v_column_names VARCHAR2(100):= 'cob_dt' ;
v_limit PLS_INTEGER :=2000;
v_rowid VARCHAR2 (200) := '';
v_rowid_str VARCHAR2 (200) := '';
V_QUERY VARCHAR2 (200) := '';
CURSOR C_ACT_PART IS--DEFINE CURSOR1 TO FETCH ALL TABLE NAMES
SELECT table_name,column_name INTO v_table_names,v_column_names
FROM config_table
WHERE APPLICATION_ID = 'sample_app'
AND CONFIG_STRING = 'TABLES_CONFIGURED_TO_DELETE';
CURSOR c_delete_rows -- --DEFINE CURSOR2 FOR SELECTING TEH ROWS OLDER THAN RETENTION PERIOD
IS
SELECT rowid as row_id
FROM
employee ----<HOW TO USE dynamic TABLE name HERE RETURNED FROM ABOVE CURSOR>??
where 1=1
AND cob_dt < (SYSDATE - 730) --<<HOW TO USE dynamic COULMN NAME HERE RETURNED FROM ABOVE CURSOR>??
TYPE rows_deleted_nt IS TABLE OF c_delete_rows%ROWTYPE
INDEX BY PLS_INTEGER;
rows_deleted rows_deleted_nt;
BEGIN
DBMS_OUTPUT.PUT_LINE ('inside main blck ');
--OPEN AND FETCH CURSOR1
OPEN C_ACT_PART;
LOOP
FETCH C_ACT_PART INTO v_table_names;
DBMS_OUTPUT.PUT_LINE ('table name from cursor is ' || v_table_names );
--open second cursor
OPEN c_delete_rows;
LOOP
FETCH c_delete_rows BULK COLLECT INTO rows_deleted LIMIT v_limit; --FETCH ROWS TO BE DELETED USING ROWID
--close c_delete_rows;
DBMS_OUTPUT.PUT_LINE ('inside first blck ');
DBMS_OUTPUT.PUT_LINE ('row count for deletion is ' || rows_deleted.COUNT );
FOR indx IN 1 .. rows_deleted.COUNT
loop
v_rowid := rows_deleted (indx).row_id;
v_rowid_str := ''''||v_rowid||'''';
DBMS_OUTPUT.PUT_LINE ('String row id for deletion is: ' || v_rowid_str );
V_QUERY :=
'DELETE FROM ' || v_table_names || 'WHERE rowid ='
|| v_rowid_str;
EXECUTE IMMEDIATE V_QUERY; --dynamic query
end loop;
DBMS_OUTPUT.PUT_LINE ('delete executed successfully for:' || rows_deleted.COUNT );
COMMIT;
EXIT WHEN c_delete_rows%NOTFOUND;
END LOOP;
CLOSE c_delete_rows;
EXIT WHEN C_ACT_PART%NOTFOUND;
END LOOP;
CLOSE C_ACT_PART;
END;