Skip navigation.

The Anti-Kyte

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

Going dotty – Generating a Filename containing a parameter value in SQL*Plus

Sat, 2014-10-18 12:01

As I have alluded to previously, I was not born in the UK.
Nope, my parents decided to up-sticks and move from London all the way to the
other side of the world, namely Auckland.
Then they had me. Then they came back.
To this day, they refuse to comment on whether these two events were related.

I went back to New Zealand a few years ago.
As I wandered around places that I hadn’t seen since I was five, it was strange how memories that I had forgotten came flooding back.
That last sentence doesn’t make much sense. It’s probably more accurate to say that memories I hadn’t thought about for years came flooding back.

I recently remembered something else I once knew, and then forgot – namely how to generate a SQL*Plus file name which includes a parameter value.

The scenario

I’ve got a script that lists all of the employees in a given department :

accept deptno prompt 'Enter Department ID : '
spool department.lis

select first_name, last_name
from hr.employees
where department_id = &deptno
order by employee_id
/

spool off

Now, rather than it just creating a file called department.lis, I want to create a file that includes the department number I’m querying.

Obvious…but wrong

You might think the following is a reasonable attempt to do this :

accept deptno prompt 'Enter Department ID : '
spool department_&deptno.lis

select first_name, last_name
from hr.employees
where department_id = &&deptno
order by employee_id
/

spool off

Unfortunately, SQL*Plus insists on being obtuse and outputting the following file :

ls
department_10lis.lst

It is at this point that a colleague came to the rescue ( thanks William)…

Going dotty

This will do the job…

accept deptno prompt 'Enter Department ID : '

spool department_&deptno..lis

select first_name, last_name
from hr.employees
where department_id = &deptno
order by employee_id
/

spool off

Run this and we not only get :

Enter Department ID : 10
old   3: where department_id = &deptno
new   3: where department_id = 10

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Jennifer             Whalen

SQL> 

…we get a file, appropriately named :

ls
department_10.lis

The magic here is that the “.” character delimits the variable substitution.
Just to prove the point, we can do the same with a positional parameter :

set verify off

spool department_&1..lis

select first_name, last_name
from hr.employees
where department_id = &1
order by employee_id
/

spool off

…run this and we get :

SQL> @position_param.sql 10

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Jennifer             Whalen

SQL> 

…and the appropriate file…

ls
department_10.lis

On that note, I’m off to the pub. Now, where did I leave my keys ?


Filed under: Oracle, SQL Tagged: spool; filename including a variable value, SQL*Plus

Sayonara to Sequences and Trouble for Triggers – Fun and Games in Oracle 12c

Sat, 2014-09-13 08:57

Ah, Nostalgia.
Not only can I remember the Good Old Days, I also remember them being far more fun than they probably were at the time.
Oh yes, and I was much younger….and had hair.
Yes, the Good Old Days, when Oracle introduced PL/SQL database packages, partitioning, and when the sequence became extinct.
Hang on, I don’t remember that last one…

The Good Old Ways

Say we have a requirement for a table to hold details of gadgets through the ages.
This table has been modelled with a synthetic key, and also a couple of audit columns so we can track when a row was created and by whom.
Traditionally, the code to fulfill this requirement would follow a familiar pattern.

The table might look something like this :

create table gadgets
(
    id number constraint dino_pk primary key,
    gadget_name varchar2(100) not null,
    created_by varchar2(30) default user,
    creation_date date default sysdate
)
/

NOTE – you’d normally expect to see NOT NULL constraints on the CREATED_BY and CREATION_DATE columns. I’ve left these off for for the purposes of the examples that follow.

We’ll also want to have a sequence to generate a value for the id…

create sequence gad_id_seq
/

As it stands, this implementation has one or two issues…

-- Specify all values
insert into gadgets( id, gadget_name, created_by, creation_date)
values( gad_id_seq.nextval, 'Dial-Up Modem', user, sysdate)
/

-- omit the "default" columns
insert into gadgets( id, gadget_name)
values( gad_id_seq.nextval, 'Tablet Computer')
/

-- specify null values for the "default" columns
-- also, don't use the sequence for the id value
insert into gadgets( id, gadget_name, created_by, creation_date)
values(3, 'Netbook', null, null)
/

The first problem becomes apparent when we query the table after these inserts…

SQL> select * from gadgets;

  ID GADGET_NAME	  CREATED_BY	       CREATION_DATE
---- -------------------- -------------------- --------------------
   1 Dial-Up Modem	  MIKE		       31-AUG-14
   2 Tablet Computer	  MIKE		       31-AUG-14
   3 Netbook

Yes, although the insert was successful for the Netbook row, the explicit specification of CREATED_BY and CREATION_DATE values as NULL has overidden the default values defined on the table.

What’s more, there’s nothing enforcing the use of the sequence to generate the ID value. This becomes a problem when we go to do the next insert…


-- Next insert using sequence...
insert into gadgets(id, gadget_name, created_by, creation_date)
values( gad_id_seq.nextval, 'Smart Phone', null, null)
/

insert into gadgets(id, gadget_name, created_by, creation_date)
*
ERROR at line 1:
ORA-00001: unique constraint (MIKE.DINO_PK) violated

Because we didn’t use the sequence for the previous insert, it’s still set to the value it had after it was last invoked…


SQL> select gad_id_seq.currval from dual;

   CURRVAL
----------
	 3

The traditional solution to these problems is, of course, a trigger…

create or replace trigger gad_bir_trg
    before insert on gadgets
    for each row
    --
    -- Make sure that :
    --  - id is ALWAYS taken from the sequence
    --  - created_by and creation date are always populated
begin
	:new.id := gad_id_seq.nextval;
    :new.created_by := nvl(:new.created_by, user);
    :new.creation_date := nvl(:new.creation_date, sysdate);
end;
/

Now, if we re-run our insert…


insert into gadgets(id, gadget_name, created_by, creation_date)
values( gad_id_seq.nextval, 'Smart Phone', null, null)
/

1 row inserted


SQL> select * from gadgets where gadget_name = 'Smart Phone';

  ID GADGET_NAME	  CREATED_BY	       CREATION_DATE
---- -------------------- -------------------- --------------------
   5 Smart Phone	  MIKE		       31-AUG-14

Yes, even though we’ve invoked the sequence in the INSERT statement, the trigger invokes it again and assigns that value to the ID column ( in this case 5, instead of 4).
Reassuringly thought, the CREATED_BY and CREATION_DATE columns are now populated.

So, in order to fulfill our requirements, we need to create three database objects :

  • A table
  • a sequence
  • a DML trigger on the table

Or at least, we did….

12c – the Brave New World

Oracle Database 12c introduces a couple of enhancements which will enable us to do away with our trigger completely.
First of all…

Changes to Default Values Specification

You can now specify a default value for a column that will be used, even if NULL is explicitly specified on Insert.
Furthermore, you can now also use a sequence number as a default value for a column.

If we were writing this application in 12c, then the code would look a bit different….


create sequence gad_id_seq
/

create table gadgets
(
    id number default gad_id_seq.nextval 
        constraint dino_pk primary key,
    gadget_name varchar2(100) not null,
    created_by varchar2(30) default on null user,
    creation_date date default on null sysdate
)
/

We’ve dispensed with the trigger altogether.
The ID column now uses the sequence as a default.
The CREATED_BY and CREATION_DATE columns will now be populated, even if NULL is explicitly specified as a value in the INSERT statement….


-- Specify all values
insert into gadgets( id, gadget_name, created_by, creation_date)
values( gad_id_seq.nextval, 'Dial-Up Modem', user, sysdate)
/

-- omit the "default" columns
insert into gadgets( id, gadget_name)
values( gad_id_seq.nextval, 'Tablet Computer')
/

-- specify null values for the "default" columns
-- also, don't use the sequence for the id value
insert into gadgets( id, gadget_name, created_by, creation_date)
values(3, 'Netbook', null, null)
/




  ID GADGET_NAME	  CREATED_BY	 CREATION_
---- -------------------- -------------- ---------
   1 Dial-Up Modem	  MIKE		 31-AUG-14
   2 Tablet Computer	  MIKE		 31-AUG-14
   3 Netbook		  MIKE		 31-AUG-14

Whilst we can now guarantee that the CREATED_BY and CREATION_DATE columns are populated, we are still left with one issue, or so you might think…

-- Next insert using sequence...
insert into gadgets(id, gadget_name, created_by, creation_date)
values( gad_id_seq.nextval, 'Smart Phone', null, null)
/

1 row inserted

That’s odd. You’d think that the sequence NEXTVAL would be 3, thus causing the same error as before. However…

 select * from gadgets;

  ID GADGET_NAME	  CREATED_BY	 CREATION_
---- -------------------- -------------- ---------
   1 Dial-Up Modem	  MIKE		 31-AUG-14
   2 Tablet Computer	  MIKE		 31-AUG-14
   3 Netbook		  MIKE		 31-AUG-14
  21 Smart Phone	  MIKE		 31-AUG-14

Hmmm. Let’s take a closer look at the sequence…

select min_value, increment_by, cache_size, last_number
from user_sequences
where sequence_name = 'GAD_ID_SEQ'  
/

 MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
---------- ------------ ---------- -----------
	 1	      1 	20	    41

Yes, it looks like, in 12c at least, the default for sequences is a cache size of 20.
If we wanted to create the sequence in the same way as for 11g ( i.e. with no caching), we’d need to do this :

create sequence gad_id_seq
    nocache
/

We can now see that the sequence values will not be cached :

PDB1@ORCL> select cache_size
  2  from user_sequences
  3  where sequence_name = 'GAD_ID_SEQ'
  4  /

CACHE_SIZE
----------
	 0

All of this is a bit of an aside however. The fact is that, as it stands, it’s still quite possible to by-pass the sequence altogether during an insert into the table.
So, we still need to have a trigger to enforce the use of the sequence, right ?
Well, funny you should say that….

Identity Column in 12c

Time for another version of our table. This time however, we’re dispensing with our sequence, as well as the trigger…

create table gadgets
(
    id number generated as identity 
		constraint gad_pk primary key,
    gadget_name varchar2(100) not null,
    created_by varchar2(30) default on null user not null,
    creation_date date default on null sysdate not null
)
/

Let’s see what happens when we try to insert into this table. Note that we’ve modified the insert statements from before as the sequences does not exist ….

-- Specify all values
insert into gadgets( id, gadget_name, created_by, creation_date)
values( default, 'Dial-Up Modem', user, sysdate)
/


-- specify null values for the "default" columns
-- also, don't use the sequence for the id value
insert into gadgets( id, gadget_name, created_by, creation_date)
values(3, 'Netbook', null, null)
/

-- omit the "default" columns
insert into gadgets( id, gadget_name)
values( null, 'Tablet Computer')
/

The first statement succeeds with no problem. However, the second and third both fail with :

ORA-32795: cannot insert into a generated always identity column

We’ll come back to this in a bit.

In the meantime, if we check the table, we can see the ID column is automagically populated….

 select * from gadgets;

  ID GADGET_NAME	  CREATED_BY	 CREATION_
---- -------------------- -------------- ---------
   1 Dial-Up Modem	  MIKE		 31-AUG-14

Oh, it’s just like being on SQL Server.

How is this achieved ? Well, there are a couple of clues.
First of all, executing the create table statement for this particular version of the table requires that you have the additional privilege of CREATE SEQUENCE.
A further clue can be found by looking once again at USER_SEQUENCES…

select sequence_name, min_value, increment_by, cache_size, last_number
from user_sequences
/

SEQUENCE_NAME	      MIN_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
-------------------- ---------- ------------ ---------- -----------
ISEQ$$_95898		      1 	   1	     20 	 21

If we have a look at the column details for the table, we get confirmation that this sequence is used as the default value for the ID column :

  1  select data_default
  2  from user_tab_cols
  3  where table_name = 'GADGETS'
  4* and column_name = 'ID'
PDB1@ORCL> /

DATA_DEFAULT
--------------------------------------------------------------------------------
"MIKE"."ISEQ$$_95898".nextval


It’s worth noting that this sequence will hang around, even if you drop the table, until or unless you purge the table from the RECYCLEBIN.

If you prefer your sequences to be, well, sequential, the good news is that you can use the Sequence Creation syntax when specifying an identity column.
The change in the default number of values cached for sequences created in 12c, compared with 11g and previously, may lead you to consider being a bit more specific in how you create your sequence, just in case things change again in future releases.

Here we go then, the final version of our table creation script….

 create table gadgets
(
    id number generated always as identity
    (
        start with 1
        increment by 1
        nocache
        nocycle
    )
    constraint gad_pk primary key,
    gadget_name varchar2(100) not null,
    created_by varchar2(30) default on null user not null,
    creation_date date default on null sysdate not null
)
/

As we saw earlier, the INSERT statements for this table, now need to change. We can either specify “DEFAULT” for the ID column :

insert into gadgets( id, gadget_name, created_by, creation_date)
values( default, 'Dial-Up Modem', user, sysdate)
/

…or simply omit it altogether…

insert into gadgets(gadget_name, created_by, creation_date)
values('Smart Phone', user, sysdate)
/

And, of course, we can also omit the values for the other defaulted columns should we choose….

insert into gadgets(gadget_name)
values('Netbook')
/

If we check the table after these statements, we can see that all is as expected :


select * from gadgets
/

  ID GADGET_NAME	  CREATED_BY	 CREATION_
---- -------------------- -------------- ---------
   1 Dial-Up Modem	  MIKE		 31-AUG-14
   2 Smart Phone	  MIKE		 31-AUG-14
   3 Netbook		  MIKE		 31-AUG-14

As with a “traditional” table/sequence/trigger setup, an erroneous INSERT will cause a gap in the sequence…

insert into gadgets( id, gadget_name, created_by, creation_date)
values( default, 'Psion Series 5', 'Aridiculouslylongusernamethatwontfitnomatterwhat', sysdate)
/

values( default, 'Psion Series 5', 'Aridiculouslylongusernamethatwontfitnomatterwhat', sysdate)
                                   *
ERROR at line 2:
ORA-12899: value too large for column "MIKE"."GADGETS"."CREATED_BY" (actual:
48, maximum: 30)

insert into gadgets( id, gadget_name, created_by, creation_date)
values( default, 'Psion Series 5', default, default)
/

select * from gadgets;

  ID GADGET_NAME	  CREATED_BY	 CREATION_
---- -------------------- -------------- ---------
   1 Dial-Up Modem	  MIKE		 31-AUG-14
   2 Smart Phone	  MIKE		 31-AUG-14
   3 Netbook		  MIKE		 31-AUG-14
   5 Psion Series 5	  MIKE		 31-AUG-14
Conclusion

While we can see that 12c hasn’t done away with sequences altogether, it is fair to say that they are now a lot more unobtrusive.
As for the good old DML trigger ? Well, they’ll still be with us, but they may well be a little lighter on the mundane default handling stuff we’ve been through in this post.


Filed under: Oracle, PL/SQL, SQL Tagged: column default value, create sequence, default always, default cache value of sequence, default on null, generated as identity, identity column, insert value into identity column, ORA-32795 : cannot insert into a generated always identity column

Getting Python to play with Oracle using cxOracle on Mint and Ubuntu

Mon, 2014-08-25 12:57

“We need to go through Tow-ces-ter”, suggested Deb.
“It’s pronounced Toast-er”, I corrected gently.
“Well, that’s just silly”, came the indignant response, “I mean, why can’t they just spell it as it sounds ?”
At this point I resisted the temptation of pointing out that, in her Welsh homeland, placenames are, if anything, even more difficult to pronounce if you’ve only ever seen them written down.
Llanelli is a linguistic trap for the unwary let alone the intriguingly named Betws-Y-Coed.
Instead, I reflected on the fact that, even when you have directions, things can sometimes be a little less than straight forward.

Which brings me to the wonderful world of Python. Having spent some time playing around with this language, I wanted to see how easy it is to plug it into Oracle.
To do this, I needed the cxOracle Python library.
Unfortunately, installation of this library proved somewhat less than straightforward – on Linux Mint at least.
What follows are the gory details of how I got it working in the hope that it will help anyone else struggling with this particular conundurum.

My Environment

The environment I’m using to execute the steps that follows is Mint 13 (with the Cinnamon desktop).
The database I’m connecting to is Oracle 11gXE.

In Mint, as with most other Linux Distros, Python is part of the base installation.
In this particular distro version, the default version of Python is 2.7.

If you want to check to see which version is currently the default on your system :

which python
/usr/bin/python

This will tell you what file gets executed when you invoke python from the command line.
You should then be able to do something like this :

ls -l /usr/bin/python
lrwxrwxrwx 1 root root 9 Apr 10  2013 python -> python2.7

One other point to note is that, if you haven’t got it already, you’ll probably want to install the Oracle Client.
The steps you follow to do this will depend on whether your running a 32-bit or 64-bit OS.

To check this, open a Terminal Window and type :

uname -i

If this comes back with x86_64 then you are running 64-bit. If it’s i686 then you are on a 32-bit os.
In either case, you can find the instructions for installation of the Oracle Client on Debian based systems here.

According to the cxOracles’s official SourceForge site, the next bit should be simple.
Just by entering the magic words…

pip install cxOracle

…you can wire up your Python scripts to the Oracle Database of your choice.
Unfortunately, there are a few steps required on Mint before we can get to that point.

Installing pip

This is simple enough. Open a Terminal and :

sudo apt-get install python-pip

However, if we then run the pip command…

pip install cx_Oracle

cx_Oracle.c:6:20: fatal error: Python.h: No such file or directory

It seems that, in order to run this, there is one further package you need…

sudo apt-get install python-dev

Another point to note is that you need to execute the pip command as sudo.
Even then, we’re not quite there….

sudo pip install cx_Oracle

Downloading/unpacking cx-Oracle
  Running setup.py egg_info for package cx-Oracle
    Traceback (most recent call last):
      File "<string>", line 14, in <module>
      File "/home/mike/build/cx-Oracle/setup.py", line 135, in <module>
        raise DistutilsSetupError("cannot locate an Oracle software " \
    distutils.errors.DistutilsSetupError: cannot locate an Oracle software installation
    Complete output from command python setup.py egg_info:
    Traceback (most recent call last):

  File "<string>", line 14, in <module>

  File "/home/mike/build/cx-Oracle/setup.py", line 135, in <module>

    raise DistutilsSetupError("cannot locate an Oracle software " \

distutils.errors.DistutilsSetupError: cannot locate an Oracle software installation

----------------------------------------
Command python setup.py egg_info failed with error code 1
Storing complete log in /home/mike/.pip/pip.log

So, whilst we now have all of the required software, it seems that sudo does not recognize the $ORACLE_HOME environment variable.

You can confirm this as follows. First of all, check that this environment variable is set in your session :

echo $ORACLE_HOME
/usr/lib/oracle/11.2/client64

That looks OK. However….

sudo env |grep ORACLE_HOME

…returns nothing.

Persuading sudo to see $ORACLE_HOME

At this point, the solution presented here comes to the rescue.

In the terminal run…

sudo visudo

Then add the line :

Defaults env_keep += "ORACLE_HOME"

Hit CTRL+X then confirm the change by selecting Y(es).

If you now re-run the visudo command, the text you get should look something like this :

#
# This file MUST be edited with the 'visudo' command as root.
#
# Please consider adding local content in /etc/sudoers.d/ instead of
# directly modifying this file.
#
# See the man page for details on how to write a sudoers file.
#
Defaults        env_reset
Defaults        mail_badpass
Defaults        secure_path="/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:$
Defaults        env_keep += "ORACLE_HOME"
# Host alias specification

# User alias specification

# Cmnd alias specification

# User privilege specification
                               [ Read 30 lines ]
^G Get Help  ^O WriteOut  ^R Read File ^Y Prev Page ^K Cut Text  ^C Cur Pos
^X Exit      ^J Justify   ^W Where Is  ^V Next Page ^U UnCut Text^T To Spell

You can confirm that your change has had the desired effect…

sudo env |grep ORACLE_HOME
ORACLE_HOME=/usr/lib/oracle/11.2/client64
Finally installing the library

At last, we can now install the cxOracle library :

sudo pip install cx_Oracle
Downloading/unpacking cx-Oracle
  Running setup.py egg_info for package cx-Oracle
    
Installing collected packages: cx-Oracle
  Running setup.py install for cx-Oracle
    
Successfully installed cx-Oracle
Cleaning up...

To make sure that the module is now installed, you can now run :

python
Python 2.7.3 (default, Feb 27 2014, 19:37:34) 
[GCC 4.7.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> help('modules')

Please wait a moment while I gather a list of all available modules...

If all is well, you should be presented with the following list :

ScrolledText        copy_reg            ntpath              tty
SgiImagePlugin      crypt               nturl2path          turtle
SimpleDialog        csv                 numbers             twisted
SimpleHTTPServer    ctypes              oauth               types
SimpleXMLRPCServer  cups                opcode              ubuntu_sso
SocketServer        cupsext             operator            ufw
SpiderImagePlugin   cupshelpers         optparse            unicodedata
StringIO            curl                os                  unittest
SunImagePlugin      curses              os2emxpath          uno
TYPES               cx_Oracle           ossaudiodev         unohelper
TarIO               datetime            packagekit    

Finally, you can confirm that the library is installed by running a simple test.
What test is that ?, I hear you ask….

Testing the Installation

A successful connection to Oracle from Python results in the instantiation of a connection object. This object has a property called version, which is the version number of Oracle that the database is running on. So, from the command line, you can invoke Python…

python
Python 2.7.3 (default, Feb 27 2014, 19:58:35) 
[GCC 4.6.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.

… and then run

>>> import cx_Oracle
>>> con = cx_Oracle.connect('someuser/somepwd@the-db-host-machine/instance_name')
>>> print con.version
11.2.0.2.0
>>> 

You’ll need to replace someuser/somepwd with the username and password of an account on the target database.
The db-host-machine is the name of the server that the database is sitting on.
The instance name is the name of the database instance you’re trying to connect to.

Incidentally, things are a bit easier if you have an Oracle client on your machine with the TNS_ADMIN environment variable set. To check this :

env |grep -i oracle
LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin
ORACLE_HOME=/usr/lib/oracle/11.2/client64

Assuming that your tnsnames.ora includes an entry for the target database, you can simply use a TNS connect string :

>>> import cx_Oracle
>>> con = cx_Oracle.connect('username/password@database')
>>> print con.version
11.2.0.2.0
>>> 
Useful Links

Now you’ve got cxOracle up and running, you may want to check out some rather useful tips on how best to use it :


Filed under: Linux, Oracle Tagged: cxOracle, pip install cxOracle, python, python-dev, uname, visudo, which

ANSI Joins and Uppercase Keywords – making PL/SQL look less like COBOL

Wed, 2014-07-30 06:12

The month-long festival of football has finally come to an end.
A tournament that was supposed to be about “No 10s” and the coronation of the host nation has lived up to expectations. Ironically, by defying them.

Where to start ? Well, it seems that goalkeepers had something to say about just who the star players were going to be.
Ochoa, Navas and Neuer were all outstanding, not to mention Tim Howard. I wonder if he could save me money on my car insurance ?
Those number 10s were also in evidence. However, in the end, it wasn’t Neymar, Messi, or even Mueller who shone brightest in the firmament. That honour belonged to one James Rodriguez, scorer of the best goal, winner of the Golden Boot, and inspiration to a thrilling Columbia side that were a bit unlucky to lose out to Brazil in a gripping Quarter Final.
Now, usually a World Cup finals will throw up the odd one-sided game. One of the smaller teams will end up on the wrong end of a good thrashing.
This tournament was no exception…apart from the fact that it was the holders, Spain, who were on the wrong-end of a 5-1 defeat by the Netherlands.
Then things got really surreal.
Brazil were taken apart by a team wearing a kit that bore more than a passing resemblence to the Queens Park Rangers away strip.
The popular terrace chant “It’s just like watching Brazil” may well require a re-think after Germany’s 7-1 win.
So, Germany (disguised as QPR) advanced to the final to play a side managed by a former Sheffield United winger.
Eventually, German style and attacking verve triumphed.
Through the course of the tournament, O Jogo Bonito seems to have metamorphosed into Das Schöne Spiel.
The stylish Germans are what provide the tenuous link to this post. I have once again been reviewing my SQL and PL/SQL coding style.
What follows is a review of some of the coding conventions I (and I’m sure, many others) have used since time immemorial with a view to presenting PL/SQL in all it’s glory – a mature, powerful, yet modern language rather than something that looks like a legacy from the pre-history of computing.

Hopefully, the changes discussed here will help my code to become more readable ( and therefore maintainable) as well as looking a bit nicer.
William Robertson (from whom I plaigarised the title for this post) has some interesting things to say on PL/SQL coding style.

In this post, I’ve attempted to focus on stylistic topics that may affect code readability rather than just well established practices that I find annoying. Believe me, it was a bit of a struggle.

What I will be looking at is :

  • Uppercase Keywords
  • The use of camel case
  • The vexed question of ANSI Join syntax

Inevitably, what follows is likely to be highly subjective so feel free to disagree vehemently.

Uppercase Keywords

I started using SQL back in 1993, when the world was black and white.
My first experiences with Oracle was coding on a terminal emulator using vi.
Sure, you could change the screen colour if you were so minded, but syntax highlighting for PL/SQL was the stuff of science fiction.
The accepted way of distinguishing keywords was therefore, to type them in upper case.

--
-- Old style...
-- Keywords and built in packages are in uppercase.
-- brackets in the cursor for loop are not aligned, but the identation is consistent
--
DECLARE
    l_message VARCHAR2(50) := 'Happy New Millenium!';
BEGIN
    FOR r_emps IN (  
        SELECT first_name,  
        FROM hr.employees) 
    LOOP
        DBMS_OUTPUT.PUT_LINE('Message for '||r_emps.first_name||' - '||l_message);
    END LOOP;
END;
/

Needless to say, things have changed a bit since then. Not only do you have multiple Oracle IDEs to choose from, all but the most basic text editors will have syntax highlighting as standard. Of course many of them will have built-in highlighting for SQL rather than PL/SQL, but many such as Textpad and Gedit can easily be customised to suit.

One additional tweak to the next code example is that I’ve aligned the brackets in the way that you’d expect to see in other 3GLs. Apart from making bracket matching a bit easier, I think it looks a bit nicer…

--
-- All keywords and built-in packages are lowercase.
-- Syntax highlighting means that the keywords are still distinguishable...
--
declare
    l_message varchar2(50) := 'Happy New Millenium!';
begin
    for r_emps in
    (
        select first_name
        from hr.employees
    ) 
    loop
        dbms_output.put_line('Message for '||r_emps.first_name||' - '||l_message);
    end loop;
end;
/

…OK, WordPress doesn’t really do PL/SQL, so I’ll try to illustrate with a screenshot from Gedit :

My code in glorious technicolour

My code in glorious technicolour

We could make this code look even more funky and modern if, for example, we were to consider using…

Camel Case

Camel Case has never really gained wide acceptance in Oracle programming circles. I would suggest that the main reason for this is that Oracle stores object names, column names etc in the data dictionary in UPPERCASE.
Therefore, if you were to create a table with the following script…

Create Table Regions
(
    regionId Number,
    regionName Varchar2(25),
    longRegionDescription Varchar2(4000)
)
/

You may well be a bit surprised by the result of the following query…

select column_name, data_type
from user_tab_columns
where table_name = 'Regions'
/

no rows selected

Yes, in order to find the column details we need to specify the table name in upper case…

select column_name, data_type
from user_tab_columns
where table_name = 'REGIONS'
/

COLUMN_NAME                    DATA_TYPE
------------------------------ ------------------------------
REGIONID                       NUMBER
REGIONNAME                     VARCHAR2
LONGREGIONDESCRIPTION          VARCHAR2

Unless you want to go down the long and winding road of quoted identifiers, the underscore remains – in my opinion at least – the best way of separating words in identifiers.

Hang on a second, that’s probably fair enough as far as object names go, but what about variables ?

At this point, I have a confession to make. If I were to see something like this…

Declare
    lMessage varchar2(50) := 'Happy New Millenium!';
Begin
    For rEmps In
    (
        select first_name
        from hr.employees
    ) 
    Loop
        dbms_output.put_line('Message for '||rEmps.first_name||' - '||lMessage);
    End Loop;
End;
/

…my first reaction would be to assume that it was written by someone who normally codes in a language other than PL/SQL. Of course, this says rather more about me than the author of the code or, indeed, the code itself.

My justification for persisting with underscores in variable names can be reduced to two rather small fig leaves.

First of all, when declaring a variable for use to compare to a column value, I tend to keep to the name of the target column. For example :

create or replace function get_employee_name_fn
( 
    i_employee_id employees.employee_id%type
)
    return varchar2	
as
    l_last_name varchar2(100);
begin
    select last_name
    into l_last_name
    from employees
    where employee_id = i_employee_id;
    return l_last_name;
end;
/

The second is that, If I’m using underscores for some variables, it would look a bit odd if I then used Camel Case for others.

In an attempt to rid you of the unfortunate mental image you may have acquired with the fig leaves reference, I’ll move swiftly on to something that seems to ellicit strong opinions in the Oracle world…

ANSI Join syntax

In the vast majority of cases, if you’re writing PL/SQL then you will, at some point, need to write some SQL.

Back in Oracle 6, there was no Cost Based Optimizer. For the first several years of my using Oracle, tuning of SQL was done by understanding the 14 rules applied by the Rule Base Optimizer.
One key point was that a query was evaluated from the bottom up, so the ordering of the predicate was important.
For this reason, it became common practice to specify join conditions first, ahead of other, more restrictive conditions.
Of course, in those days, ANSI syntax was not available in Oracle, but it didn’t matter too much as you could be reasonably certain of where to look for join conditions in SQL. For example :

select dept.department_name, coun.country_name, 
    emp.last_name as manager
from departments dept, employees emp, locations loc,
    countries coun, regions reg
where dept.manager_id = emp.employee_id
and dept.location_id = loc.location_id
and loc.country_id = coun.country_id
and coun.region_id = reg.region_id
and coun.country_name != 'Switzerland'
and reg.region_name = 'Europe'
order by coun.country_name, dept.department_name
/

In the period of time between the Cost Based Optimizer becoming de rigeur and the bugs in the ANSI syntax being ironed out in Oracle, a less structured coding style seems to have become prevalant.
Join conditions can now appear anywhere in the predicate list without any impact on performance…

select dept.department_name, coun.country_name, 
    emp.last_name as manager
from departments dept, employees emp, locations loc,
    countries coun, regions reg
where dept.manager_id = emp.employee_id
and reg.region_name = 'Europe'
and dept.location_id = loc.location_id
and coun.region_id = reg.region_id
and coun.country_name != 'Switzerland'
and loc.country_id = coun.country_id
order by coun.country_name, dept.department_name
/
Inner Joins – ANSI Style

The ANSI syntax offers a solution to this by enforcing the separation of join conditions into a discreet clause in the query :

select dept.department_name, coun.country_name, 
    emp.last_name as manager
from departments dept
join employees emp
    on dept.manager_id = emp.employee_id
join locations loc
    on dept.location_id = loc.location_id
join countries coun
    on loc.country_id = coun.country_id
join regions reg
    on coun.region_id = reg.region_id
where reg.region_name = 'Europe'
and coun.country_name != 'Switzerland'
order by coun.country_name, dept.department_name
/

In terms of readability, unless you’re an old fogey like me, this would seem to be a significant improvement.

The ANSI syntax offers several variations on the theme of Inner Join. You can explicitly use the INNER keyword with your inner joins, although this would seem to be a bit superfluous as other join types would have to be stated explicitly in the code.

As well as the tradional…

select dept.department_name, emp.last_name
from departments dept
inner join employees emp
    on dept.department_id = emp.department_id
/

…you could specify a join between tables that have the same column name like this :

select dept.department_name, emp.last_name
from departments dept
inner join employees emp
    using(department_id)
/

Slightly less appealing is the NATURAL JOIN syntax, which assumes that you want to join on any and all matching column names between two tables.
If you run either of the previous two queries in the HR schema, they will return 106 rows.
However, the following query returns only 32 rows…

select dept.department_name, emp.last_name
from departments dept
natural join employees emp
/

As well as DEPARTMENT_ID, the tables also both contain a MANAGER_ID column. The above statement is therefore not, as you might think, the equivalent of the previous two, but is rather more …

select dept.department_name, emp.last_name
from departments dept
join employees emp
    using( department_id, manager_id)
/

With this in mind, I think I’ll steer clear of having my joins au naturel.

Outer Joins

This is one area where the ANSI syntax has a distinct advantage over it’s more venerable Oracle counterpart.
I’ve previously explored how it allows you to outer join a table multiple times in the same query.

Apart from this, it’s main distinguishing feature over the more traditional Oracle syntax is it’s sense of direction.

For a left outer join, tables are read left-to-right – i.e. the table you’re outer joining to is specified second :

select dept.department_name, 
    count(emp.employee_id) as "Employee Count"
from departments dept
left outer join employees emp
    using( department_id)
group by dept.department_name
order by 2 desc, dept.department_name
/

For a right outer join, the reverse is true, the table specified first is the one you’re outer joining to :

select dept.department_name, 
    count(emp.employee_id) as "Employee Count"
from employees emp
right outer join departments dept
    using( department_id)
group by dept.department_name
order by 2 desc, dept.department_name
/

Whilst having to know your left from right is something new to contend with, either of the above examples is more readable to someone familiar with SQL ( although not necessarily Oracle) than :

select dept.department_name, 
    count(emp.employee_id) as "Employee Count"
from departments dept, employees emp
where dept.department_id = emp.department_id(+)
group by dept.department_name
order by 2 desc, dept.department_name
/

In the past, there have been one or two issues with Oracle’s implementation of the ANSI join syntax. However, it now seems to be fairly stable and consistent.
Add this to it’s undoubted advantage in terms of readability and I’ve really rather run out of excuses not to use it.


Filed under: Oracle, PL/SQL, SQL Tagged: ansi join syntax, camel case, inner join, join using, LEFT OUTER JOIN, natural join, right outer join, uppercase keywords