Test del

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;
    v_pk_val  VARCHAR2(4000);
    v_sql_del VARCHAR2(1000);
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
    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
        -- Loop row by row
        FOR i IN v_rowids.FIRST .. v_rowids.LAST LOOP
            BEGIN
                -- Delete with RETURNING INTO
                v_sql_del := 'DELETE FROM ' || p_schema || '.' || p_table || 
                             ' WHERE ROWID = :1 RETURNING ' || p_pk || ' INTO :2';

                EXECUTE IMMEDIATE v_sql_del USING v_rowids(i), OUT v_pk_val;

                -- Insert only if delete succeeded
                INSERT INTO deleted_records (TABLE_NAME, TABLE_PK_VALUE, DELETION_STATUS, BATCH_NO)
                VALUES (p_table, v_pk_val, 'DEL', l_batch_no);

            EXCEPTION
                WHEN OTHERS THEN
                    DBMS_OUTPUT.PUT_LINE('Skipping row due to error: ' || SQLERRM);
            END;
        END LOOP;

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

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