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>

How to log DML errors.

While performing any DML operation, if an error occurs, the statement is terminated and rolled back in its entirety. In case large DML operation it can be wasteful of time and system resources.
For such dml statements, you can avoid this situation by using the DML error logging feature.


example as:
Firstly we have to create DML error table. like

SQL> EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('EMP', 'EMP_ERR');

PL/SQL procedure successfully completed.

SQL> DESC EMP_ERR;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORA_ERR_NUMBER$                                    NUMBER
 ORA_ERR_MESG$                                      VARCHAR2(2000)
 ORA_ERR_ROWID$                                     ROWID
 ORA_ERR_OPTYP$                                     VARCHAR2(2)
 ORA_ERR_TAG$                                       VARCHAR2(2000)
 EMPNO                                              VARCHAR2(4000)
 ENAME                                              VARCHAR2(4000)
 JOB                                                VARCHAR2(4000)
 MGR                                                VARCHAR2(4000)
 HIREDATE                                           VARCHAR2(4000)
 SAL                                                VARCHAR2(4000)
 COMM                                               VARCHAR2(4000)
 DEPTNO                                             VARCHAR2(4000)

SQL> 

Or we can create Error log table manually.

Oracle Database logs the following errors during DML operations:
    * Column values that are too large
    * Constraint violations (NOT NULL, unique, referential, and check constraints)
    * Errors raised during trigger execution
    * Errors resulting from type conversion between a column in a subquery and the corresponding column of the table
    * Partition mapping errors
    * Certain MERGE operation errors (ORA-30926: Unable to get a stable set of rows for MERGE operation.)

Moreover some the errors will not be logged in log table as a result the DML operation will be stopped will error and roll backed.

SQL> INSERT INTO emp
  2    SELECT * FROM emp
  3    LOG ERRORS INTO EMP_ERR ('daily_load') REJECT LIMIT UNLIMITED;

0 rows created.

SQL> column ORA_ERR_MESG$ format a100
SQL> column ORA_ERR_TAG$  format  a50
SQL> column ENAME  format a50
SQL> column JOB  format a50
SQL> column SAL  format a50

SQL> select ORA_ERR_NUMBER$ ,ORA_ERR_MESG$ from emp_err;

ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- -------------------------------------------------------------------
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated

ORA_ERR_NUMBER$ ORA_ERR_MESG$
--------------- --------------------------------------------------------------------
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated
              1 ORA-00001: unique constraint (SCOTT.PK_EMP) violated

14 rows selected.

SQL> 

Like in example insert statement doesn't fail with error.. but it has not insert any record becuase due to primary key in emp table. If we query the error log table (EMP_ERR) then we can check the error message and column values for the query is failing.

DML error logging can work on almost all dml statements (Insert, Update, Delete, Merge) This type of error logging mechanism can be very beneficial in data warehouse environments.

Also the error logging table will be transaction independent. if the base dml statement is rollback then the data which is modified in main table (EMP) will be roll backed but the error log table (EMP_ERR) table data will not be roll backed.
like:

SQL> DELETE FROM EMP_ERR;

12 rows deleted.
SQL> CREATE TABLE EMP1 AS SELECT * FROM EMP;

Table created.

SQL> UPDATE EMP1 SET EMPNO = ROWNUM WHERE ROWNUM <=2
  2  /

2 rows updated.

SQL> COMMIT;

Commit complete.

SQL> INSERT INTO EMP SELECT * FROM EMP1
  2     LOG ERRORS INTO EMP_ERR ('daily_load') REJECT LIMIT UNLIMITED;

2 rows created.

SQL> select count(*) from emp_err;

  COUNT(*)
----------
        12

SQL> select empno from emp;

    EMPNO
---------
        1
        2
     7369
     7499
     7521
     7566
     7654
     7698
     7782
     7788
     7839

    EMPNO
---------
     7844
     7876
     7900
     7902
     7934

16 rows selected.

SQL> roll
Rollback complete.
SQL> select count(*) from emp_err;

  COUNT(*)
----------
        12

SQL> select empno from emp;

    EMPNO
---------
     7369
     7499
     7521
     7566
     7654
     7698
     7782
     7788
     7839
     7844
     7876

    EMPNO
---------
     7900
     7902
     7934

14 rows selected.

SQL>

In above example after rollback changes from EMP table has been roll backed but the error messages in EMP_ERR are not rollbacked.

Find string in current schema

In some cases developer ask us to find some string in the particular schema. So, I have develop one anonymous block to find the string in tables. It will return the table name and column name in which value is existing.

SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DECLARE
  2     string_to_search_v      VARCHAR(200) := UPPER('KING');
  3     datatype_V              VARCHAR(200) := 'CHAR';
  4     exists_v                NUMBER;
  5  BEGIN
  6  FOR i IN (SELECT
  7                     table_name, column_name
  8       FROM
  9             user_tab_columns
 10       WHERE
 11             data_type LIKE '%'||datatype_v||'%' AND
 12             data_length >= LENGTH(string_to_search_v) AND
 13             table_name NOT LIKE 'BIN$%' )
 14  LOOP
 15     EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' ||i.table_name ||' WHERE UPPER('|| i.column_name
 16     ||') like ''%'|| string_to_search_v||'%'''  INTO exists_v;
 17
 18     IF exists_v > 0 THEN
 19        dbms_output.put_line ('String: ' || string_to_search_v || ' Exists in ' || i.table_name
 20        ||':'||i.column_name);
 21     END IF;
 22     END LOOP;
 23  END;
 24  /
String: KING Exists in EMP:ENAME

PL/SQL procedure successfully completed.

SQL>
SQL> 

Hope it will help.

Usage of Reserved words in object naming

Today I have been assigned to do some R&D on using Reserve words as column name or object names. When we need to use reserve words in our database when we have to use double quotes with the column/object names.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL>
SQL>  create table demo (NUMBER number);
create table demo (NUMBER number)
*
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> create table demo ("NUMBER" number);

Table created.

SQL> select NUMBER FROM DEMO;
select NUMBER FROM DEMO
*
ERROR at line 1:
ORA-00936: missing expression


SQL> select "NUMBER" FROM DEMO;

no rows selected

SQL>
SQL> insert into demo (NUMBER) values (1)
2  /
insert into demo (NUMBER) values (1)
*
ERROR at line 1:
ORA-00928: missing SELECT keyword


SQL> insert into demo ("number") values (1)
2  /
insert into demo ("number") values (1)
*
ERROR at line 1:
ORA-00904: "number": invalid identifier


SQL> insert into demo ("NUMBER") values (1)
2  /

1 row created.

SQL>

Moreover if we will use reserve words for column/table names in then we all have to support the reserve words in our ETL scripts and reporting tools also.

We can query all the reserve words with v$reserved_words dynamic view. Depending on the version oracle has different reserve words.