Geek Answers Handbook

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

I just wasted the last two hours of my life trying to create a table with automatically increasing primary key bases for this tutorial . The lessons are great, the problem I am facing is that Create Target will work if I have a column that is a timestamp and a table called a timestamp in the same table ...

Why doesn't the oracle indicate this as a problem when creating a table?

The following is the sequence of commands that I entered:

  • Creating a table:

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

);

  1. Creating a sequence:

    CREATE SEQUENCE test_sequence START WITH 1 INCREMENT BY 1;

  2. Creating a trigger:

    CREATE OR REPLACE TRIGGER test_trigger BEFORE INSERTING ON myTable LINKS NEWS FOR EACH HAND START 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 two lines with the word "timestamp" in them works fine. I would think that the syntax would be enough to distinguish between a keyword and a column name.

As I said, I don’t understand why the table is created perfectly, but the oracle crashes when I try to create a trigger ...

EXPLANATION

, , timestamp, . , , , .

, Oracle , . , , -.

Oracle, , , ? -, MySQL Oracle.

+5
6

metalink (227615.1) :

# 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.
+9

TIMESTAMP Oracle ( ).

V $RESERVED_WORDS, RESERVED 'N'.

. , Oracle.

+3

. timestamp , . - (, xtimestamp) .

+1

, , , , SQL, , - , , PL/SQL.

, SQL PL/SQL - , -. , , , , .

0

, Oracle , EXECUTE IMMEDIATE (.. " " .

0

EXECUTE IMMEDIATE. , .

0

More articles: