Test1

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
) AS
    TYPE t_pk_tab    IS TABLE OF VARCHAR2(4000) INDEX BY PLS_INTEGER;
    TYPE t_rowid_tab IS TABLE OF ROWID INDEX BY PLS_INTEGER;

    v_pks       t_pk_tab;
    v_rowids    t_rowid_tab;
    v_sql_sel   CLOB;
    v_sql_del   VARCHAR2(1000);
    v_cursor    SYS_REFCURSOR;
    l_batch_no  NUMBER := 1;

    v_successful_rowids t_rowid_tab;
    v_idx INTEGER := 0;
BEGIN
    -- Step 1: Build dynamic SQL for selection
    v_sql_sel := '
        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';

    -- Step 2: Fetch data
    OPEN v_cursor FOR v_sql_sel 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
        BEGIN
            -- Step 3: Log deletions using bulk insert with SAVE EXCEPTIONS
            FORALL i IN INDICES OF v_pks SAVE EXCEPTIONS
                INSERT INTO t_deleted_records (
                    TABLE_NAME, TABLE_PK_VALUE, DELETION_STATUS, BATCH_NO
                ) VALUES (
                    p_table, v_pks(i), 'DEL', l_batch_no
                );

            -- Step 4: All inserts succeeded; delete all
            v_sql_del := 'DELETE FROM ' || p_schema || '.' || p_table || ' WHERE ROWID = :1';

            FORALL i IN INDICES OF v_rowids
                EXECUTE IMMEDIATE v_sql_del USING v_rowids(i);

            COMMIT;
            DBMS_OUTPUT.PUT_LINE('All rows inserted and deleted successfully. Total: ' || v_pks.COUNT);

        EXCEPTION
            WHEN DML_ERRORS THEN
                -- Step 5: Log insert failures
                DBMS_OUTPUT.PUT_LINE('Some inserts failed. Logging errors...');
                FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
                    DBMS_OUTPUT.PUT_LINE(
                        'Insert failed at index ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
                        ': ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)
                    );
                END LOOP;

                -- Step 6: Filter only successful inserts
                FOR i IN v_pks.FIRST .. v_pks.LAST LOOP
                    IF NOT EXISTS (
                        SELECT 1
                        FROM DUAL
                        WHERE i IN (SELECT SQL%BULK_EXCEPTIONS(j).ERROR_INDEX FROM DUAL CONNECT BY LEVEL <= SQL%BULK_EXCEPTIONS.COUNT)
                    ) THEN
                        v_idx := v_idx + 1;
                        v_successful_rowids(v_idx) := v_rowids(i);
                    END IF;
                END LOOP;

                -- Step 7: Delete only successfully inserted rows
                v_sql_del := 'DELETE FROM ' || p_schema || '.' || p_table || ' WHERE ROWID = :1';
                FORALL i IN v_successful_rowids.FIRST .. v_successful_rowids.LAST
                    EXECUTE IMMEDIATE v_sql_del USING v_successful_rowids(i);

                COMMIT;
                DBMS_OUTPUT.PUT_LINE('Deleted ' || v_successful_rowids.COUNT || ' successfully inserted rows.');
        END;
    ELSE
        DBMS_OUTPUT.PUT_LINE('No rows found for deletion.');
    END IF;
END purge_worker_proc;
/