Draft

[Lomanu4]

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
-- 1. Build and run the SELECT to get ROWIDs to purge
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 for row-by-row processing
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;

-- 2. Delete the row and capture the primary key of the deleted row
v_sql := 'DELETE FROM ' || p_schema || '.' || p_table || ' ' ||
'WHERE ROWID = :1 ' ||
'RETURNING TO_CHAR(' || p_pk || ') INTO :2';

EXECUTE IMMEDIATE v_sql
INTO v_deleted_pk
USING v_rowid;

-- 3. Log the successfully deleted record
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;

COMMIT; -- Commit each row deletion
END LOOP;
CLOSE v_cursor;

-- Output the total number of deleted rows
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
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
IF v_cursor%ISOPEN THEN
CLOSE v_cursor;
END IF;
RAISE;
END;
/

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.