Androx.NET
Database | Programming | Videogames and personal stuff...
Batch processing in Oracle using a single PL/SQL Anonymous Block
published onAs 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 onSometimes 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 onBe 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.