Androx.NET

Database | Programming | Videogames and personal stuff...


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

published on

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.

Generate a sequence of numbers using a SQL query

published on

Sometimes we need to generate a sequence of numbers, maybe for cartesian products, or we want to work directly with number series using SQL expressions, for those cases I present a simple option using query recursiveness

Oracle

Using CONNECT BY operator

select level
  from dual
connect by level <= 10;
Using Query Recursive Subfactoring

with c_numbers (lvl) as
( select 1 as lvl
  from dual
 union all
  select lvl + 1
   from c_numbers
   where lvl < 10
)
select lvl
  from c_numbers
order by lvl;

MySQL / MariaDB

(borrowed directly from the MySQL manual) https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive


WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

Hello World!

published on

Be welcome to this site! I am Nicko, The Drako PaxPax and I'll be your host during your stay here. mostly you'll find information about the projects I've been working for, and some handy stuff I've learnt across time.

And last but not least, I'll be using this space here as a personal journal were I'll be sharing some thoughts and experiences I had, I really hope you find this place handy and entertaining as well.