Draft drop

CREATE OR REPLACE PROCEDURE 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
    TYPE t_pk_tab IS TABLE OF VARCHAR2(4000);
    TYPE t_rowid_tab IS TABLE OF ROWID;

    v_pks     t_pk_tab;
    v_rowids  t_rowid_tab;
    v_sql     CLOB;
    v_cursor  SYS_REFCURSOR;
BEGIN
    -- Build dynamic SQL to select PK and ROWID
    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';

    DBMS_OUTPUT.PUT_LINE('SQL to fetch rows: ' || v_sql);

    -- Fetch PKs and ROWIDs to delete
    OPEN v_cursor FOR v_sql USING p_purge_dt, p_start_id, p_end_id;
    FETCH v_cursor BULK COLLECT INTO v_pks, v_rowids;
    CLOSE v_cursor;

    IF v_pks.COUNT > 0 THEN
        SAVEPOINT before_insert;

        BEGIN
            -- Insert into deleted_records table
            FORALL i IN v_pks.FIRST .. v_pks.LAST
                INSERT INTO deleted_records (TABLE_NAME, TABLE_PK_VALUE, DELETION_STATUS, BATCH_NO)
                VALUES (p_table, v_pks(i), 'DEL', l_batch_no);

            -- Build delete SQL using ROWID
            v_sql := 'DELETE FROM ' || p_schema || '.' || p_table || ' WHERE ROWID = :1';

            -- Delete rows using FORALL
            FORALL i IN v_rowids.FIRST .. v_rowids.LAST
                EXECUTE IMMEDIATE v_sql USING v_rowids(i);

            COMMIT;
            DBMS_OUTPUT.PUT_LINE('Deleted ' || v_pks.COUNT || ' rows.');

        EXCEPTION
            WHEN OTHERS THEN
                ROLLBACK TO before_insert;
                DBMS_OUTPUT.PUT_LINE('Error occurred, rolled back: ' || SQLERRM);
        END;

    ELSE
        DBMS_OUTPUT.PUT_LINE('No rows found for deletion.');
    END IF;
END;
/