Batch processing in Oracle using a single PL/SQL Anonymous Block

Published on: 2024-03-13 - Permalink Oracle PL/SQL

As an Oracle consultant a very common activity is to perform commands in batch, for example: rebuild invalid indexes on a given schema, unlock all locked users on the database, recompile invalid stored procedures, etc.

One of the most common techniques I learnt and seen on field is to dynamically build the commands needed to be executed, put them on a script and after that execute it, for example, on a SQL Developer window or directly on SQL*Plus, while this approach works fine, it can't be easily automated, this is relevant specially when we have to automate this batch processing, like when we have to disable all indexes from a staging schema before loading data.

My personal favorite option for doing batch processing is to use dynamic sql execution inside a looped cursor, a technique I found on an old Oracle forum. below you may find an example of how to rebuild indexes from a specific schema

DECLARE
  c_schema_name CONSTANT VARCHAR2(30) := 'SCOTT';
BEGIN
  FOR c_r IN ( SELECT owner, index_name
                 FROM dba_indexes
                WHERE status != 'VALID'
                  AND owner = c_schema_name
             ) LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX "' || c_r.owner || '"."' || c_r.index_name || '" REBUILD';
  END LOOP;
END;
/

Above script can be enhanced by using parallel processing with APEX_PLSQL_JOB.SUBMIT_PROCESS, of course if the APEX subsystem is not installed on the database, the same feature can be implemented using DBMS_SCHEDULER or DBMS_JOB, however the later is deprecated and might be removed on future Oracle releases.