ORA-00984 column not allowed here

This is one of those messages i love so much because it belongs to the category of those obscure errors that convinced me to embark myself in the oracle quirks saga.

I had to "debug" a procedure and i couldn't use the standard debugging tools, not even the traditional dbms_output.put_line so i was logging the content of some variables into a table for later review.

Initially, i wrote an exception handling block like this:

exception
when others then
insert into error_log (text) values(sqlerrm);
end;

When i attempted to compile the procedure i got the following error:

ORA-06550: line 50, column 41:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 50, column 3:

I was quite sure the name of the column was right, but given the message i went to the table to double check the column name, so, when i saw it was correct, i was puzzled.
I was running late, i had to get this stupid program to work and i was getting weird errors on a trivial statement, gosh!

Then i removed SQLERRM from the insert statement, just to give it a try and it compiled seamlessly.

Bingo.

Frankly speaking, although i've read many manuals and often more than once, i didn't remember of any particular limitation in the usage of SQLERRM. Moreover i was so convinced i've already used the function that way that i found difficult to believe to my eyes.

Nevertheless the limitation is clearly documented in the PL/SQL User's Guide and Reference.
You cannot use SQLERRM inside SQL statements.
If you want to do so, you must first store the value of SQLERRM in a variable.

So, the original code must be changed as follows:

exception
when others then
l_errmsg := SQLERRM;
insert into error_log (text) values(l_errmsg);
end;

And last but not least:
try to look for ORA-00984 in the official error message book for version 10.2 or 10.1.
You won't even find the entry.

It does show up in the 9.2 version and earlier versions with the following explanation:

ORA-00984 column not allowed here

Cause: A column name was used in an expression where it is not permitted, such as in the VALUES clause of an INSERT statement.

Action: Check the syntax of the statement and use column names only where appropriate.

So, at the end of the day we have to choose between an old documented misleading message and a new nonexisting message!

I love these quirks, but not when i am in a hurry.
:-D

See message translations for ORA-00984 and search additional resources

---

ORA-00984: colonna non consentita in questo caso
ORA-00984: columna no permitida aquí
ORA-00984: aquí la columna no és permesa
ORA-00984: Un nom de colonne n'est pas autorisé ici
ORA-00984: Spalte hier nicht zulässig
ORA-00984: στήλη δεν επιτρέπεται εδώ
ORA-00984: kolonne er ikke tilladt her
ORA-00984: kolumn inte tillåten här
ORA-00984: kolonne er ikke tillatt her
ORA-00984: tässä ei voi olla saraketta
ORA-00984: oszlopnév használata itt nem megengedett
ORA-00984: coloană nepermisă aici
ORA-00984: Kolom is hier niet toegestaan.
ORA-00984: coluna não permitida aqui
ORA-00984: coluna não é aqui permitida
ORA-00984: употребление столбца здесь недопустимо
ORA-00984: sloupec zde není povolen
ORA-00984: stĺpec tu nie je dovolený
ORA-00984: w tym miejscu, kolumna jest niedozwolona
ORA-00984: burada sütun kullanımına izin verilemez