Today I received a strange error from Oracle, for what appeared to be no good reason:
ORA-06552: PL/SQL: Compilation unit analysis terminatedORA-06553: PLS-320: The declaration of the type of this expression is incomplete or malformed
I received the error when attempting to create a trigger on a newly created table. I could create triggers on other tables without any errors, however on the new table – it would error every time.
The table definition included a column named timestamp with the data type timestamp. Oracle was happy enough for me to create the table, however it refused to let me create a trigger on the table with a column name over lapping a reserved word.
The solution was simple, renamed the column in question to use a non-reserved word and everything continued as normal. I must say though, I think it is strange that it would let me create the table using the reserved word in the first place if it was going to complain about me using other standard Oracle features which would error as a by product of the reserved word.
serves you right for using a reserved word for your column :P *rssssp*
Maybe I’m wrong but I thought that you are able to use reserved words. You just have to use escape chars when accessing them eg [DATETIME] in sqlserver, or `SOMERESERVEDWORD` in mysql…
a quick google gives us this lovely snippet which tells us that all oracle reserved words can be used as column and table names when wrapped in double quotes. The only exception to this rule is the ROWID reserved word.
Jake,
It isn’t a matter of Oracle not allowing the reserved word as a column name, it allows
timestampas a column name just fine.The issue is that if you use a reserved word as a column name, it breaks some of the other standard Oracle features such as triggers.
Even if you enclose the column
timestampin double quotes while creating the table – you will still receive the error I outlined above when you try to create a trigger on the table; hence my confusion.Al.
Thanks! I run into the same problem. I called one of the columns “date” :(
Just had the same problem;
For anyone googling their way here, the problem surfaces when you have a column of the _type_ timestamp in the same table as a column with the _name_ timestamp. Change either and it works fine.