CODEWITHSUNDEEP
sqloracle-databaseora-06553
Omar Kooheji
Omar Kooheji

Reputation: 55760

Oracle why does creating trigger fail when there is a field called timestamp?

I've just wasted the past two hours of my life trying to create a table with an auto incrementing primary key bases on this tutorial, The tutorial is great the issue I've been encountering is that the Create Target fails if I have a column which is a timestamp and a table that is called timestamp in the same table...

Why doesn't oracle flag this as being an issue when I create the table?

Here is the Sequence of commands I enter:

  1. Creating the Table:

    CREATE TABLE myTable
   (id NUMBER PRIMARY KEY,
    field1 TIMESTAMP(6),
    timeStamp NUMBER,
);

  2. Creating the Sequence:

    CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;

  3. Creating the trigger:

    CREATE OR REPLACE TRIGGER test_trigger  
BEFORE INSERT  
ON myTable  
REFERENCING NEW AS NEW  
FOR EACH ROW  
BEGIN  
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;  
END;  
/

Here is the error message I get:

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

Any combination that does not have the two lines with a the word "timestamp" in them works fine. I would have thought the syntax would be enough to differentiate between the keyword and a column name.

As I've said I don't understand why the table is created fine but oracle falls over when I try to create the trigger...

CLARIFICATION

I know that the issue is that there is a column called timestamp which may or may not be a keyword. MY issue is why it barfed when I tried to create a trigger and not when I created the table, I would have at least expected a warning.

That said having used Oracle for a few hours, it seems a lot less verbose in it's error reporting, Maybe just because I'm using the express version though.

If this is a bug in Oracle how would one who doesn't have a support contract go about reporting it? I'm just playing around with the express version because I have to migrate some code from MySQL to Oracle.

Upvotes: 5

Views: 4785

Answers (6)

Diogo Maschio
Diogo Maschio

Reputation: 74

You can execute via EXECUTE IMMEDIATE. IT's not better way but work's and avoid column rename.

In my case rename column will be a caotic way

Upvotes: 0

Java-Oracle-Expert
Java-Oracle-Expert

Reputation: 1

Instead of having Oracle maintain a view, use EXECUTE IMMEDIATE (i.e. if 'Rename the column to a non-reserved word' is not an option.

Upvotes: 0

pablo
pablo

Reputation: 408

There is a note on metalink about this (227615.1) extract below:

# symptom: Creating Trigger fails
# symptom: Compiling a procedure fails
# symptom: ORA-06552: PL/SQL: %s
# symptom: ORA-06553: PLS-%s: %s     
# symptom: PLS-320: the declaration of the type of this expression is incomplete or malformed
    # cause: One of the tables being references was created with a column name that is one of the datatypes (reserved key word). Even though the field is not referenced in the PL/SQL SQL statements, this error will still be produced.

    fix:

    Workaround:

    1. Rename the column to a non-reserved word.
    2. Create a view and alias the column to a different name.

Upvotes: 9

Dave Costa
Dave Costa

Reputation: 48111

TIMESTAMP is not listed in the Oracle docs as a reserved word (which is surprising).

It is listed in the V$RESERVED_WORDS data dictionary view, but its RESERVED flag is set to 'N'.

It might be a bug in the trigger processing. I would say this is a good one for Oracle support.

Upvotes: 3

tragomaskhalos
tragomaskhalos

Reputation: 2753

You've hinted at the answer yourself. You're using timestamp as a column name but it's also a keyword. Change the column name to something else (eg xtimestamp) and the trigger compiles.

Upvotes: 1

Alexandre
Alexandre

Reputation: 1026

Well, I'm not totally sure about it, but I think this happens because the SQL code used to manipulate and access database objects is interpreted by some interpreter different form the one used to interpret PL/SQL code.

Have in mind that SQL an PL/SQL are different things, and so they are processed differently. So, I think there is some error in one interpreter, just not sure which one is.

Upvotes: 0

Related Questions