Oracle...

Working with Oracle is always an adventure. The error messages are usually not very helpful, so you have to guess a lot. What I’ve seen today is an extreme though. Oracle allows you to create a table with a column named “TIMESTAMP” if you quote it:

CREATE TABLE "SOME_TABLE" (
    ...
    "TIMESTAMP" TIMESTAMP WITH TIME ZONE
);

Oracle is rather picky on identifier names, but since it accepted “TIMESTAMP”, I was assuming everything is fine. Later I needed to create a trigger for this table and that’s where the fun starts.

CREATE OR REPLACE TRIGGER "SOME_TABLE_TR" 
BEFORE INSERT ON "SOME_TABLE"
FOR EACH ROW
BEGIN
    ...
END;

This was failing for some reason though. The only thing I got was this “nice” error message, pointing to the table name in the CREATE TRIGGER statement:

ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed

What type? Do I have a typo somewhere? Did the table somehow get corrupted? You can’t imaging how long did it take for me to figure out that it doesn’t like the column name, which was not mentioned anywhere in the PL/SQL block. I would have no problem if it told me that I can’t use the name. There are too many restrictions on identifiers anyway. What I don’t understand is why does it allow me to create something that’s going to break other core functionality.