Draft

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