Friday, October 21, 2005

ORA-00904 and MERGE

Oracle 10.1.0.2 on Windows

I just came across the following parser whim:


CREATE TABLE test_a (a NUMBER, b NUMBER, c NUMBER, d NUMBER)
/
CREATE TABLE test_b (a NUMBER, b NUMBER, c NUMBER, d NUMBER)
/

MERGE INTO test_a t1
USING (SELECT a, b
c, d from test_b) t2
ON (t1.a = t2.a)
WHEN MATCHED THEN
UPDATE SET t1.b = t2.b,
t1.c = t2.c
WHEN NOT MATCHED THEN
INSERT (a, b, c, d)
VALUES (t2.a, t2.b, t2.c, t2.d)
/

ORA-00904: "T2"."B": invalid identifier


As you can see, there is a missing comma after the "b" field in the SELECT clause at the beginning of the MERGE statement.

The parser is not complaining about the "c" identifier in the SELECT clause but on the "b" identifier in the NOT MATCHED clause!
This is absolutely misleading, it took me 10 minutes to spot this quirk.

Funnily enough, if you modify the above statement in the following way:

MERGE INTO test_a t1
USING (SELECT a, b
c, d from test_b) t2
ON (t1.a = t2.a)
WHEN NOT MATCHED THEN
INSERT (a, c, d)
VALUES (t2.a, t2.c, t2.d);


The statement gets executed without complaints about the missing comma after "b"!

This is definitely a parser flaw.

Bye,
Flavio

See message translations for ORA-00904 and search additional resources.



ORA-00904: : identificativo non valido
ORA-00904: : identificador no válido
ORA-00904: : identificador incorrecte
ORA-00904: : identificateur non valide
ORA-00904: Ungültiger Spaltenname
ORA-00904: : μη αποδεκτό αναγνωριστικό
ORA-00904: : ugyldig identifikator
ORA-00904: : ogiltig identifierare
ORA-00904: : ugyldig ID
ORA-00904: : virheellinen tunniste
ORA-00904: : érvénytelen azonosító
ORA-00904: : identificator nevalid
ORA-00904: : ongeldige ID.
ORA-00904: nome inválido de coluna
ORA-00904: : identificador inválido
ORA-00904: : недопустимый идентификатор
ORA-00904: : neplatný identifikátor
ORA-00904: : neplatný identifikátor
ORA-00904: : niepoprawny identyfikator
ORA-00904: : geçersiz belirleyici

5 comments:

Anonymous said...

It probably interpreted "c" as a column alias for "b", so the only columns available to the t2 "table" were "a", "c", & "d"

Byte64 said...

Excellent explanation, thank you!

Anonymous said...

Moreover, the error 00904 came about not because of a missing comma, but because the condition in the ON clause contains a reference to a column in the UPDATE statement (when matched).
This is apparently an undocumented "feature".
By leaving out the b column in the update statement, you made the statement work.

Anonymous said...

Excellent !

Thanks for this last comment: the oracle doc doesn't talk about this (logical) limitation.

Etienne

Unknown said...

You get the same error using MERGE if you try to update the joined columns. (See http://forums.oracle.com/forums/thread.jspa?threadID=341012.) C'mon Oracle, is it that hard to create a new error number for this?

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