Column names you should NOT use

We all know from our data modelling days… (Sidenote: Remember those days? You know…when we used to think about good design, good names, table and column comments etc …Ah good times. How I miss them )  … that naming objects is half the battle when it comes to a self-documenting your database and ultimately your code. Good names help the next person who comes along that needs to understand your data and your code.

Problematic Columns

But I made a discovery today, when a colleague reported a breakage in my audit utility, which generates auditing packages/triggers for tables. Here’s a small demo of how PL/SQL “breaks” with certain column names. I’ll start with a simple table containing a column called EXCEPTION.

SQL> create table t( x int, exception int);

Table created.

SQL> insert into t values (1,1);

1 row created.

SQL> commit;

Commit complete.

At first glance, everything seems to work in PL/SQL

SQL> declare
  2    v int;
  3  begin
  4    select exception
  5    into   v
  6    from   t;
  7  end;
  8  /

PL/SQL procedure successfully completed.

But some elements of the PL/SQL get confused when the column that matches a keyword is encountered:

SQL> declare
  2    r t%rowtype;
  3  begin
  4    r.x := 1;
  5    r.exception := 1;
  6  end;
  7  /
  r.exception := 1;
    *
ERROR at line 5:
ORA-06550: line 5, column 5:
PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following:
 
current delete exists prior
The symbol " was inserted before "EXCEPTION" to continue.

As with just about any special name, we can easily workaround this by introducing quote identifiers, ie

SQL> declare
  2    r t%rowtype;
  3  begin
  4    r.x := 1;
  5    r."EXCEPTION" := 1;
  6  end;
  7  /

PL/SQL procedure successfully completed.

but I think I sit with majority opinion when I state that wrapping all of your names throughout your code base in quotes just to handle these rare anomalies is an unpopular approach. I know one of the most commonly requested enhancements to DDL generation tools in the Oracle Database (eg DBMS_METADATA) is to make quotes optional rather then always present.

You might be thinking that this issue would be far more widespread given the vast array of reserved words that exist in the database. However, most of these reserved words are reserved not just from a PL/SQL perspective but also from a SQL perspective. For example, I cannot add a column called “SELECT” to a table (without quotes):

SQL> alter table t add SELECT int;
alter table t add SELECT int
                  *
ERROR at line 1:
ORA-00904: : invalid identifier

Thus it would appear that “EXCEPTION” is a special case where:

• it can be added as a column without quotes
• but it needs in certain parts of PL/SQL code to be referenced with quotes

The big question then becomes: What other column names fall into this niche category?

Which columns break things?

To solve this, I compiled a list of the PL/SQL reserved words from the documentation and for each keyword, attempted to add it as a column to a table without quoting the column name. Most of them failed, but the list below is the column names you should avoid because they sit in that strange place of being allowed as unquoted column names but still require quotes in some PL/SQL syntax.

AT
BEGIN
CASE
CLUSTERS
COLAUTH
COLUMNS
CRASH
CURSOR
DECLARE
END
EXCEPTION
FETCH
FUNCTION
GOTO
IF
INDEXES
OVERLAPS
PROCEDURE
SQL
SUBTYPE
TABAUTH
TYPE
VIEWS
WHEN

OK I concede that “not” is perhaps too strong a word for the title of this post, but lets face it – No-one wants to be adding quotes everywhere in their code because you decided that “GOTO” was an awesome column name

But … I have pushed a fix to my audit utility to handle those column names above. They will be quoted within the audit generation code.