Draft drop

[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
-- 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;
/

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