Tuesday, February 20, 2007

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

13 comments:

Anonymous said...

hi, ive also encountered the same error and manage to figure out the problem when inserting data to oracle.

use single quote instead double quote.. in values

:D

Anonymous said...

Oh. My. God.

You just saved me hours of RTFM'ing. Thanks a LOT.

Byte64 said...

You're welcome.
And keep coming back from time to time.

Bye,
Flavio

Anonymous said...

I just encountered across this error when I was trying to add a column to a table. I was using a query auto-generated by TOAD. Turns out it had forgotten to put single-quotes around the default value ('N'), and that's why I was getting the message!

ALTER TABLE CLIENT_T
ADD (nutrition_fl VARCHAR2(1 BYTE) DEFAULT N)
;

GG said...

Thank you so much!!! I had missed the single quotes for the CHAR values. Now it works!

Leigh said...

hi,
just wanted to say thank you so,so much for this article. I am a final yr student who is using Oracle to build my database for one of my modules. I however, unfortunately is still encountering problems. Any help would be greatly appreciated.
Its a database for a wedding planner
My table and datatypes are as follow:
eventid not null number(2),
eventname char(20),
starttime date
endtime date
venueid not null varchar2(2)
weddingid not null varchar2(6)
eventtypeid not null varchar2(7)

The last 3 attributes are foreign keys.
my insert statement is as follows:
insert into event values
(01,'ceremony',to_date('11-00-00','hh-mi-ss'),to_date('11-45-00','hh-mi-ss'),v1,w1,cer1);

all my work is written in all caps and i keep receiving the error described in this article. I tried change the dashes with the time information to / and : but no such luck.
Once again any help you can give would be a blessing

Byte64 said...

Leigh,
you are getting ORA-00984 because you are trying to insert undefined values (the last three values).
What are they, variables?

Secondly, i don't think is a good idea to use a CHAR(20) for storing the event name, CHAR columns are right padded with blanks and this can give many headaches in the code. Much better to use VARCHAR2.

The error message is a bit inconsistent for the situation, it would be much easier to understand, especially for newbies, if you'd get "undefined variable" or something like that.

Flavio

LeighJ said...

Thank you so much for your speedy response on my question. the last 3 items (v1,w1,cer1) are my foreign keys. they represent primary keys in my venue table, my wedding table and event type table respectively. thanks again,
Leigh

Anonymous said...

I was passing in a variable into my values clause that was not declared (I changed the name in the declaration but forgot to rename it in the value clause). Interestingly, it did not throw the an undefined varialble error. Also, it gave me the wrong line number offset by 2- sending me on a goose chase.

Something to consider in addition to your suggestions- make sure all vars match the spelling of their declarations.

Byte64 said...

Usually the wrong numbering happens when you put on separate lines the CREATE XXX statement and the object name.
For some reason ORACLE assumes they are always on the same line, so you may end up with the parser telling that the error occurred at 100, whereas the actual problem is at 101.

For instance:

create or replace
procedure test as
begin
insert into test_tab (sqlerrm);
end;
/

will report an error at line 3, column 31 (pl/sql-wise), but clearly the problem is at line 4 (text editor -wise).

A similar problem occurs with triggers, if for some reason you add the DECLARE clause, all the line references in the PL/SQL profiler will be shifted by one line.

Thanks
Flavio

nandu said...

SQL> insert into e_company (ename, cname, salary, jdate)
2 values ('ANIL', 'ACC', 1500.00, 1-MAY-89) ;
values ('ANIL', 'ACC', 1500.00, 1-MAY-89)
*
ERROR at line 2:
ORA-00984: column not allowed here
what is the solution for this ........

Byte64 said...

you are missing the single quotes around the date value.

Anonymous said...

Hi,
I got this error when trying to insert the return-value from a package function. I misspelled one of the parameters for that function and got this error, too. It should have been an 'invalid identifier' error, but was not...

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles