Monday, September 27, 2010

ORA-06552: PL/SQL: Compilation Unit Analysis Terminated

While working today I received a strange error ORA-06552. This error I am getting while creating trigger on table. Upon R&D I came to know that this error is due to reserve word used in table column.
like:

SQL> CREATE TABLE DEMO2(TIMESTAMP TIMESTAMP);

Table created.

SQL> CREATE TABLE DEMO2_AUDIT AS SELECT * FROM DEMO2;

Table created.

SQL> ED
Wrote file afiedt.buf

  1  CREATE OR REPLACE TRIGGER demo2_trg
  2  BEFORE INSERT ON demo2
  3  FOR EACH ROW
  4  BEGIN
  5    INSERT INTO demo2_audit VALUES (:new.TIMESTAMP);
  6* END;
SQL> /
BEFORE INSERT ON demo2
                 *
ERROR at line 2:
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is
incomplete or malformed


SQL> SELECT * FROM V$RESERVED_WORDS WHERE KEYWORD = 'TIMESTAMP'
  2  /

KEYWORD                            LENGTH R R R R D
------------------------------ ---------- - - - - -
TIMESTAMP                               9 N N N N N

SQL> ALTER TABLE DEMO2_AUDIT  RENAME COLUMN TIMESTAMP TO TIMESTAMP2;

Table altered.

SQL> ALTER TABLE DEMO2  RENAME COLUMN TIMESTAMP TO TIMESTAMP2;

Table altered.

SQL> ED
Wrote file afiedt.buf

  1   CREATE OR REPLACE TRIGGER demo2_trg
  2   BEFORE INSERT ON demo2
  3   FOR EACH ROW
  4   BEGIN
  5     INSERT INTO demo2_audit VALUES (:new.TIMESTAMP2);
  6*  END;
SQL> /

Trigger created.

SQL>

No comments: