Delete In Batches

Delete In Batches

CREATE OR REPLACE PROCEDURE delete_in_batches (
    p_schema_name   IN VARCHAR2,                  -- e.g. 'HR'
    p_table_name    IN VARCHAR2,                  -- e.g. 'EMPLOYEES'
    p_where_clause  IN VARCHAR2 DEFAULT NULL,     -- e.g. 'SALARY > 50000'
    p_batch_sz      IN PLS_INTEGER DEFAULT 5000   -- rows per COMMIT
) IS
    /* ------------------------------------------------------------------
       Variables
    ------------------------------------------------------------------ */
    l_sql          VARCHAR2(4000);
    l_sql_cnt      VARCHAR2(4000);
    l_rowid_tab    DBMS_SQL.NUMBER_TABLE;      -- holds ROWIDs for a batch
    l_rows_deleted PLS_INTEGER := 0;
    l_total_rows   PLS_INTEGER := 0;
    l_cursor       INTEGER;
    l_status       INTEGER;
BEGIN
    --------------------------------------------------------------------
    -- Build the dynamic SELECT that pulls ROWIDs to delete
    --------------------------------------------------------------------
    l_sql := q'[
        SELECT ROWID
          FROM ]' || p_schema_name || '.' || p_table_name;

    IF p_where_clause IS NOT NULL THEN
        l_sql := l_sql || ' WHERE ' || p_where_clause;
    END IF;

    --------------------------------------------------------------------
    -- Open a cursor for the ROWID query
    --------------------------------------------------------------------
    l_cursor := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(l_cursor, l_sql, DBMS_SQL.NATIVE);

    --------------------------------------------------------------------
    -- Loop: fetch batch‑size ROWIDs, delete them, commit
    --------------------------------------------------------------------
    LOOP
        -- Fetch up to p_batch_sz ROWIDs into the collection
        DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_rowid_tab(1), 18);
        l_rows_deleted := 0;
        l_rowid_tab.DELETE;               -- clear from previous iteration

        WHILE l_rows_deleted < p_batch_sz AND DBMS_SQL.FETCH_ROWS(l_cursor) > 0 LOOP
            l_rows_deleted := l_rows_deleted + 1;
            DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_rowid_tab(l_rows_deleted));
        END LOOP;

        EXIT WHEN l_rows_deleted = 0;      -- no more rows → exit loop

        ----------------------------------------------------------------
        -- Build and execute the batch DELETE statement
        ----------------------------------------------------------------
        l_sql_cnt := q'[
            DELETE FROM ]' || p_schema_name || '.' || p_table_name ||
            q'[ WHERE ROWID IN (SELECT column_value FROM TABLE( CAST( :1 AS SYS.ODCINUMBERLIST) ))]';

        -- Use a temporary collection of NUMBER (ROWID as NUMBER) – DBMS_SQL
        -- cannot bind a collection of ROWID directly, so we convert to NUMBER.
        DECLARE
            l_num_tab SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST();
        BEGIN
            FOR i IN 1 .. l_rows_deleted LOOP
                l_num_tab.EXTEND;
                l_num_tab(l_num_tab.COUNT) := TO_NUMBER(l_rowid_tab(i));
            END LOOP;

            EXECUTE IMMEDIATE l_sql_cnt USING l_num_tab;
            l_total_rows := l_total_rows + SQL%ROWCOUNT;
        END;

        COMMIT;   -- <-- batch commit
    END LOOP;

    --------------------------------------------------------------------
    -- Final clean‑up
    --------------------------------------------------------------------
    DBMS_SQL.CLOSE_CURSOR(l_cursor);

    DBMS_OUTPUT.PUT_LINE('Deleted '||l_total_rows||' rows from '
                         ||p_schema_name||'.'||p_table_name
                         ||' (batch size = '||p_batch_sz||').');

EXCEPTION
    WHEN OTHERS THEN
        IF DBMS_SQL.IS_OPEN(l_cursor) THEN
            DBMS_SQL.CLOSE_CURSOR(l_cursor);
        END IF;
        ROLLBACK;
        RAISE;
END delete_in_batches;
/

Examples:

-- Example 1: Delete old audit rows in batches of 10,000
BEGIN
    delete_in_batches(
        p_schema_name  => 'AUDIT',
        p_table_name   => 'USER_ACTIVITY',
        p_where_clause => 'ACTIVITY_DATE < SYSDATE - 365',
        p_batch_sz     => 10000 );
END;
/

-- Example 2: Purge a whole table (be careful!)
BEGIN
    delete_in_batches(
        p_schema_name => 'HR',
        p_table_name  => 'TEMP_LOG',
        p_where_clause=> NULL,          -- no filter → delete everything
        p_batch_sz    => 5000 );
END;
/