Oracle SQL and PL/SQL Coding Standards – Cat Herding for Dummies

Whilst in Montreal recently, Deb and I made a pilgrimage to the Circuit Giles Villeneuve, home of the Canadian Grand Prix. When not in use, the track is open to the public. It’s divided into two lanes – one for people to walk and cycle down down, and a one for people to drive down.
You can just imagine flying round in an F1 car. You come out of the excruciatingly slow L’epingle hairpin and build up to top speed as you tear down the Casino Straight. Ahead lies the final chicane before the start/finish line. A tricky right left combination with the treacherous curb on the inside of the last turn ready to spit the unwary into the Wall of Champions on the opposite side of the track.
At over 300 kph you start to think about spotting your braking point. Suddenly, this comes into view….

What do you think this is, a race track ?

… and now you know what it’s like to be a programmer, who has channeled raw inspiration through his or her dancing fingers to produce a thing of beauty and elegance…only to run into the QA person pointing out that the commas are in the wrong place according to page 823, paragraph 2 sub-section e of The Coding Standards.

Often measured in weight rather than the number of pages, Coding Standards documents are often outdated, arbitrary and just plain wrong.
On the other hand, their absence can cause much heartache, not least to those poor souls in support who are trying to maintain code where the Agilista philosophy of Code over Documentation has been taken to the ultimate extreme.

What follows is an attempt to make sense of the Coding Standards conundrum.
I’ll look at what I think a Coding Standards document should contain, and what it shouldn’t.
Then I’ll give some suggestions as to standards for Oracle SQL and PL/SQL which you can either embrace or throw rocks at, depending on your preference.
Before all of that however, I feel the need for some serious catharsis…

Wacky Coding Standards

What follows is a select list of standards that I’ve been required to work with at various places. Some of them are out of date, some just plain arbitrary. Others are what happens when you take the word of “experts” without checking it out for yourself. As you can imagine, this particular section has required quite a bit of restraint on my part.

The ever-changing alias

“When joining two or more tables in a SQL query, the first table must have an alias of ‘a’, the second ‘b’, and so forth.”

So, if we use the HR schema as an example, the following would fall foul of the standards…

SELECT emp.first_name, emp.last_name
FROM employees emp, departments dept
WHERE emp.department_id = dept.department_id
AND dept.department_id = 60
/

What we must do to conform to this stricture is :

SELECT a.first_name, a.last_name
FROM employees a, departments b
WHERE a.department_id = b.department_id
AND b.department_id = 60
/

You can just imagine how exciting it is when a table alias can be any arbitrary letter from a to z and it’s different in every query.

Implicit Cursors are bad, mkay?

“Thou shalt not use an implicit cursor!”

This one was quite popular for quite a long time. This was almost entirely based on the opinion of the great and the good of PL/SQL programming ( you know who you are).
It’s surprising how few people actually thought about this in any depth.
The premise behind this prohibition was that an implicit cursor always executes an extra fetch after retrieving the first row. This extra fetch, the argument went, was inefficient. Using an explicit cursor would result in only one fetch.

What happens when an implicit cursor finds a row when performing that second fetch ? You get a TOO_MANY_ROWS error. If you’re using an implicit cursor, it can be reasonably assumed that you want to fetch one, and only one, row. If there’s more than one row being returned, you probably want to know about it.

To be fair to the experts in question, one of the main health-warnings they issue is “don’t take my word for it”.

Look Mum, less typing

This standard was born out of the ban on implicit cursors.
“When fetching a single row from an explicit cursor, use a FOR LOOP to reduce the amount of code written…”

CURSOR c_deptno IS 
    SELECT department_id 
    FROM departments 
    WHERE department_name = 'IT'; 

BEGIN 
    ... 
    FOR r_deptno IN c_deptno LOOP 
	    l_deptno := r_deptno.department_id; 
	    EXIT; 
    END LOOP; 
    ... 

None of that OPEN FETCH CLOSE malarkey, oh no. For us it was the simple and oh so easy to read construct you see above.
Of course, those unfamiliar with our coding standards often did a double-take when they saw this sort of thing, but they soon got used to it. After all, less typing = less work, right ? I mean, what could possibly go wrong ? Um…

It’s C Jim, but not as we know it

This last one isn’t an Oracle example, it comes from a C coding standards document.

“When incrementing a variable by 1, don’t use the ++ operator. Instead use var = var + 1 as this is more readable”.

The author of this particular gem was later cornered by the entire Development Team and forced to read aloud, extracts from “The C Programming Language” by Kernighan and Ritchie.

I’m sure that anyone who has been programming professionally for any length of time will have their own list of favourites. But how does this help move the discussion on ?
Sure, it gives us some insight into the problems with Standards, but how are we going to solve them ?

This brings us on to the “ideal” Coding Standards document, or at least, my ideal Coding Standards document.
At this point, you may well wonder what right I have to lay down the Coding Standards ? The only answer I can think of is that I’m the egomaniac at the keyboard right now. So there.

To my mind, Coding Standards need to be :-

• concise
• concerned exclusively with Standards, not Best Practice
• concerned with a single language or language family (e.g. SQL and PL/SQL)

The Secret is in the Scope

The first thing to do is to put the Standards Document on a diet.

Best Practices change between releases. They are not standards, to my mind.
Do you remember when you used SELECT COUNT(rowid) instead of SELECT COUNT(*) ?
Back in Oracle 7, the former was more efficient. However, since Oracle 8, the latter statement now works in exactly the same way.

Best Practices belong in a version-specific Best Practices document. Standards should be rather more immutable.

Rather than being buried in a comprehensive Coding Standards document for all of the technologies being used on site, each language or language-family, should have it’s own document.

I have a feeling that my final point on our Standards Diet may be a touch controversial… Standards do not have to specify code layout in any detail…

Code Layout Standards

There always seems to be reams written about how to indent and align code, not to mention the old favourite of where to put your commas.
There are historical reasons for this.
Once upon a time, Oracle would determine whether a statement was cached by doing a straight character-by-character comparison. Consistent use of whitespace was therefore important.
Since around 9i, it takes a slightly different approach.
All non-literals in the statement are converted to lowercase and whitespace is stripped prior to the comparison.

Therefore, I’d contend that all you need to say in the Standards is that code should be indented consistently and be readable.
Provided peer reviews are a feature of your development process, this should be more than enough to ensure that the code that goes forward to production is fit for purpose in terms of maintainability. This is, after all, the primary purpose of Coding Standards.

If you really are an obsessive/compulsive, you can always make use of whichever IDE happens to be in favour and stipulate that code should be run through it’s formatter prior to check in. Yes, all of them can be configured so you can get your commas wherever you want them.

SQL and PL/SQL Coding Standards

If you’ve read this blog for any length of time, you’ll have noticed that my coding standards have evolved over time. One of the advantages of being my own QA department is that I can adopt/change/drop standards as I come across better ways of doing stuff.

PL/SQL is essentially, ADA with SQL extensions. As so much of PL/SQL relies on SQL, it makes sense to combine the standards for both in one place, at least, if you’re exclusively an Oracle shop.

The standards themselves should begin with a disclaimer along the lines of :

“These standards should be adhered to, unless there is a sensible reason to deviate from them.”

This get-out clause should mean that we won’t get lumbered with following a rule which is no longer sensible/ was wrong to start with.
One example that springs to mind…it would seem sensible to have a standard stipulating that all calls to stored program units from within PL/SQL should be by reference, right ?
Sounds like a great idea…until you get to DBMS_OUTPUT.PUT_LINE, for example.
Do you really want to be lumbered with …

DBMS_OUTPUT.PUT_LINE( a => 'Some line to output');

What now follows are the standards I use because I’m comfortable with them and it’s what I’m used to. There’s no reason to believe that you’ll be struck by lightning if, for example, you put your SQL keywords in lowercase.
Anyway, rocks at the ready…

Object naming

In general terms, I prefer the use of suffixes rather than prefixes. This is mainly because my heart sinks every time I look at a list of, say triggers, in an IDE and find that they all start with TRG_.
By using a suffix, you’ll still be able to easily identify the different object types and have a nice usable alphabetical listing.

Another point to note – object names are stored in uppercase in the data dictionary. Therefore, separating words in object names is best done with underscores ( e.g. order_items).
Using Camel Case is problematic in this context as the word boundaries will be lost in the data dictionary ( e.g. OrderItems will be stored as ORDERITEMS).

Tables

Table names should be meaningful. They should also be plural, unless you’re creating a table to hold only a single row.
EMPLOYEES ( because we’ve got rows for more than one) rather than EMPLOYEE or EMP.
Generally speaking, we don’t need a suffix for tables. The exceptions are :
Global Temporary Tables should have a suffix of GTT
Permanent segments used to hold temporary data should have a suffix of TMP.
Comments should be added for all columns in a table.
The comment should specify :

• Whether the column is part of a Primary or Foreign Key.
• If it is part of a Foreign Key, what the parent table/column is.
• If it is a Synthetic Key generated from a sequence, the name of the sequence.
• If there is a check constraint a description of the allowable values rather than just the values themselves.

Primary Key, Foreign Key and Check constraints should be named with appropriate table aliases :

• Primary Key – alias_pk
• Foreign Key – child alias_parent alias_fk
• Check constraint alias_column_name_ck

Where an index is created to support a Foreign Key relationship, it’s named after the Foreign Key constraint with a _IDX suffix. Otherwise, it should start with the alias of the table and end with _IDX. What you put in the middle is more or less up to you.

As an example :

CREATE TABLE contracts ( 
    contract_id NUMBER CONSTRAINT con_pk PRIMARY KEY, 
    client_id NUMBER CONSTRAINT con_cli_fk REFERENCES clients(id), 
    status_ind VARCHAR2(1) CONSTRAINT con_status_ck 
        CHECK (status_ind IN ('L', 'D', 'F')) 
) 
/ 


CREATE INDEX con_cli_fk_idx ON contracts( client_id) 
/ 

COMMENT ON COLUMN contracts.contract_id IS 
    'Synthetic Key, populated from sequence CON_ID_SEQ' 
/ 

COMMENT ON COLUMN contracts.client_id IS 
    'FK to clients.id' 
/ 

COMMENT ON COLUMN contracts.status_ind IS 
    'Valid values are L(ive), D(ead) and F(rozen)' 
/ 

Stored Program Units

As Oracle supplied packages are essentially extensions to the PL/SQL language, it makes sense to treat them in the same way as the core language keywords. In my case, I put SQL and PL/SQL keywords in uppercase.

I use the following suffixes :

• function – _fn
• materialized_view _mv
• package – _pkg
• procedure _pr
• sequence – seq
• trigger _trg
• view _vw

For SQL statements, each clause begins on a new line unless the entire statement can fit comfortably onto one line ( I use a limit of 80 characters as a rule of thumb on this one).

For PL/SQL, indentation must be consistent throughout the program.

Triggers

The naming convention I use for DML Triggers is :
table alias_firing timing_DML action(s)_level ( if statement)_trg.

The firing timing is either B(efore) or A(fter).
The DML actions are I(nsert), U(pdate) or D(elete)
Most DML triggers tend to be row-level. If I have a statement Level then I’ll use S.

Lets say we have a table to contain the names and teams of F1 drivers. For reasons best known to the Application Designer, the synthetic key on this table needs to increment by 1 each time a record is added, with no gaps. Yes, I know that without using a sequence we could get the same id being used twice if the latest record in the table is deleted and then another record inserted. Look, this is just something I knocked together to illustrate the trigger naming thing. I’m not really trying to hammer home the point that standards and best practice don’t mix.
Anyhow, say the table looks something like this :

SQL> desc drivers 
 Name					   Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 ID					   NOT NULL NUMBER 
 FIRST_NAME				   NOT NULL VARCHAR2(100) 
 LAST_NAME				   NOT NULL VARCHAR2(100) 
 TEAM					   NOT NULL VARCHAR2(100) 
 CREATED_BY				   NOT NULL VARCHAR2(30) 
 CREATION_DATE				   NOT NULL DATE 
 MODIFIED_BY					    VARCHAR2(30) 
 MODIFIED_DATE					    DATE 

SQL> 

Oh look, complete with old-style auditing columns. I wonder what we’d need a trigger for…

CREATE OR REPLACE TRIGGER drv_biu_trg 
    BEFORE INSERT OR UPDATE on drivers 
    FOR EACH ROW 
DECLARE 
    l_next_id drivers.id%TYPE; 
BEGIN 
    IF INSERTING THEN 
        SELECT NVL( MAX( id), 0) + 1 INTO l_next_id FROM drivers; 
        :new.id := l_next_id; 
        :new.created_by := USER; 
        :new.creation_date := SYSDATE; 
    ELSIF UPDATING THEN 
        :new.modified_by := USER; 
        :new.modified_date := SYSDATE; 
    END IF; 
END; 
/

There you go – the trigger name is the table alias followed by the timing ( BEFORE in this case), followed by the DML actions the trigger fires on (Insert, Update) and the appropriate suffix.
You’ll also notice that the code is (a) beautifully formatted and (b) not worth reading.

Variables

Variables should have anchored declarations where they are :

• always assigned a value directly from a database column
• is used in a query predicate as a comparison against a database column
• is used in a DML statement to assign a value to a database column

NOTE – I’ve deliberately not gone into details about scalar declarations because they tend to be version specific. For example, it may be more efficient to use BINARY_INTEGER, PLS_INTEGER or NATURAL_INTEGER depending on which database version you’re running.
Of course, by the time Oracle 12 comes out, it’s entirely possible that they’ll all be synonyms for the same thing. In 10g PLS_INTEGER and BINARY_INTEGER already are.

Variable Names

I use the following prefixes should for variable names :

• local variable – l_
• Global variable – g_
• Constant – gc_ for a global constant,
• local constant – lc_
• Type – typ_
• record – rec_
• Table of records – tbl_
• cursor – c_
• ref cursor – rc_

Parameters are prefixed as follows

• In parameter – i_
• Out parameter o_
• In/Out parameter – io_

Comments

To my mind, comments, when taken with the code, should allow you to work out what is going on.
A header comment should be included to explain the program’s purpose and, if it’s quite involved, to give a brief overview of the functionality it implements.

What does all this have to do with Cats ?

Yes, trying to control a bunch of computer programmers is like trying to herd cats.
My take on this is, if at first you don’t succeed…don’t bother.
By providing a simple concise framework for standards and then allowing code readability to be policed by means of peer review, everyone benefits from a degree of coding consistency from easy to follow rules.
The rules themselves do not get in the way of the actual process of writing code.
Of course, it may well mean that your Peer Review sessions get quite a bit more interesting but hey, we all need a bit of excitement now and then. Just stand back and let the fur fly !
In the meantime, I’m off to take cover from all those rocks that suddenly seem to be flying in my direction.