Draft drop
CREATE OR REPLACE PROCEDURE CR_ARCHIVE_REGISTRY_PURGE_WORKER_PROC ( p_start_id IN ROWID, p_end_id IN ROWID, p_table IN VARCHAR2, p_pk IN VARCHAR2, p_schema IN VARCHAR2, p_purge_dt IN DATE, LAST_UPDATE_COL IN VARCHAR2, CREATE_DATE_COL IN VARCHAR2, l_batch_no IN NUMBER ) AS v_sql CLOB; v_cursor SYS_REFCURSOR; v_pk VARCHAR2(4000); v_rowid ROWID; v_deleted_pk VARCHAR2(4000); v_total_deleted NUMBER := 0; BEGIN -- Validate inputs BEGIN v_sql := DBMS_ASSERT.SCHEMA_NAME(p_schema) || '.' || DBMS_ASSERT.SQL_OBJECT_NAME(p_table); v_sql := DBMS_ASSERT.SQL_OBJECT_NAME(p_pk); v_sql := DBMS_ASSERT.SQL_OBJECT_NAME(LAST_UPDATE_COL); v_sql := DBMS_ASSERT.SQL_OBJECT_NAME(CREATE_DATE_COL); EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001, 'Invalid input parameter: ' || SQLERRM); END; -- Build SELECT query v_sql := 'SELECT TO_CHAR(' || p_pk || '), ROWID ' || 'FROM ' || p_schema || '.' || p_table || ' ' || 'WHERE NVL(' || LAST_UPDATE_COL || ', ' || CREATE_DATE_COL || ') < :purge_dt ' || 'AND ROWID BETWEEN :start_id AND :end_id'; -- Open cursor OPEN v_cursor FOR v_sql USING p_purge_dt, p_start_id, p_end_id; LOOP FETCH v_cursor INTO v_pk, v_rowid; EXIT WHEN v_cursor%NOTFOUND; -- Delete row v_sql := 'DELETE FROM ' || p_schema || '.' || p_table || ' ' || 'WHERE ROWID = :1 ' || 'RETURNING TO_CHAR(' || p_pk || ') INTO :2'; BEGIN EXECUTE IMMEDIATE v_sql INTO v_deleted_pk USING v_rowid; -- Log successful deletion INSERT INTO t_deleted_records (TABLE_NAME, TABLE_PK_VALUE, DELETION_STATUS, BATCH_NO) VALUES (p_table, v_deleted_pk, 'SUCCESS', l_batch_no); v_total_deleted := v_total_deleted + 1; EXCEPTION WHEN NO_DATA_FOUND THEN -- Log non-existent row INSERT INTO t_deleted_records (TABLE_NAME, TABLE_PK_VALUE, DELETION_STATUS, BATCH_NO) VALUES (p_table, v_pk, 'NOT_FOUND', l_batch_no); CONTINUE; WHEN OTHERS THEN -- Log error INSERT INTO t_deleted_records (TABLE_NAME, TABLE_PK_VALUE, DELETION_STATUS, BATCH_NO) VALUES (p_table, v_pk, 'ERROR: ' || SQLERRM, l_batch_no); CONTINUE; END; END LOOP; CLOSE v_cursor; COMMIT; -- Commit once at the end -- Output results IF v_total_deleted > 0 THEN DBMS_OUTPUT.PUT_LINE('Deleted ' || v_total_deleted || ' rows.'); ELSE DBMS_OUTPUT.PUT_LINE('No rows found for deletion.'); END IF; EXCEPTION WHEN OTHERS THEN IF v_cursor%ISOPEN THEN CLOSE v_cursor; END IF; -- Log procedure-level error INSERT INTO t_deleted_records (TABLE_NAME, TABLE_PK_VALUE, DELETION_STATUS, BATCH_NO) VALUES (p_table, NULL, 'PROCEDURE ERROR: ' || SQLERRM, l_batch_no); COMMIT; RAISE; END; /
CREATE OR REPLACE PROCEDURE CR_ARCHIVE_REGISTRY_PURGE_WORKER_PROC (
p_start_id IN ROWID,
p_end_id IN ROWID,
p_table IN VARCHAR2,
p_pk IN VARCHAR2,
p_schema IN VARCHAR2,
p_purge_dt IN DATE,
LAST_UPDATE_COL IN VARCHAR2,
CREATE_DATE_COL IN VARCHAR2,
l_batch_no IN NUMBER
)
AS
v_sql CLOB;
v_cursor SYS_REFCURSOR;
v_pk VARCHAR2(4000);
v_rowid ROWID;
v_deleted_pk VARCHAR2(4000);
v_total_deleted NUMBER := 0;
BEGIN
-- Validate inputs
BEGIN
v_sql := DBMS_ASSERT.SCHEMA_NAME(p_schema) || '.' || DBMS_ASSERT.SQL_OBJECT_NAME(p_table);
v_sql := DBMS_ASSERT.SQL_OBJECT_NAME(p_pk);
v_sql := DBMS_ASSERT.SQL_OBJECT_NAME(LAST_UPDATE_COL);
v_sql := DBMS_ASSERT.SQL_OBJECT_NAME(CREATE_DATE_COL);
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, 'Invalid input parameter: ' || SQLERRM);
END;
-- Build SELECT query
v_sql := 'SELECT TO_CHAR(' || p_pk || '), ROWID ' ||
'FROM ' || p_schema || '.' || p_table || ' ' ||
'WHERE NVL(' || LAST_UPDATE_COL || ', ' || CREATE_DATE_COL || ') < :purge_dt ' ||
'AND ROWID BETWEEN :start_id AND :end_id';
-- Open cursor
OPEN v_cursor FOR v_sql USING p_purge_dt, p_start_id, p_end_id;
LOOP
FETCH v_cursor INTO v_pk, v_rowid;
EXIT WHEN v_cursor%NOTFOUND;
-- Delete row
v_sql := 'DELETE FROM ' || p_schema || '.' || p_table || ' ' ||
'WHERE ROWID = :1 ' ||
'RETURNING TO_CHAR(' || p_pk || ') INTO :2';
BEGIN
EXECUTE IMMEDIATE v_sql
INTO v_deleted_pk
USING v_rowid;
-- Log successful deletion
INSERT INTO t_deleted_records (TABLE_NAME, TABLE_PK_VALUE, DELETION_STATUS, BATCH_NO)
VALUES (p_table, v_deleted_pk, 'SUCCESS', l_batch_no);
v_total_deleted := v_total_deleted + 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- Log non-existent row
INSERT INTO t_deleted_records (TABLE_NAME, TABLE_PK_VALUE, DELETION_STATUS, BATCH_NO)
VALUES (p_table, v_pk, 'NOT_FOUND', l_batch_no);
CONTINUE;
WHEN OTHERS THEN
-- Log error
INSERT INTO t_deleted_records (TABLE_NAME, TABLE_PK_VALUE, DELETION_STATUS, BATCH_NO)
VALUES (p_table, v_pk, 'ERROR: ' || SQLERRM, l_batch_no);
CONTINUE;
END;
END LOOP;
CLOSE v_cursor;
COMMIT; -- Commit once at the end
-- Output results
IF v_total_deleted > 0 THEN
DBMS_OUTPUT.PUT_LINE('Deleted ' || v_total_deleted || ' rows.');
ELSE
DBMS_OUTPUT.PUT_LINE('No rows found for deletion.');
END IF;
EXCEPTION
WHEN OTHERS THEN
IF v_cursor%ISOPEN THEN
CLOSE v_cursor;
END IF;
-- Log procedure-level error
INSERT INTO t_deleted_records (TABLE_NAME, TABLE_PK_VALUE, DELETION_STATUS, BATCH_NO)
VALUES (p_table, NULL, 'PROCEDURE ERROR: ' || SQLERRM, l_batch_no);
COMMIT;
RAISE;
END;
/
