ORA-04098

I noticed this recently on an Oracle 11.1.0.6.0 database.

 

If you create a table, add a trigger then add a column, it all works:

 

SQL> create table tab1 (col1 varchar2(1))

  2  /

 

Table created.

 

SQL> create trigger trig1

  2  after update on tab1

  3  begin

  4  null;

  5  end;

  6  /

 

Trigger created.

 

SQL> alter table tab1

  2  add col2 varchar2(1)

  3  /

 

Table altered.

 

SQL> desc tab1

Name                       Null?    Type

-------------------------- -------- ------------------

COL1                                VARCHAR2(1)

COL2                                VARCHAR2(1)

 

SQL>

 

If the trigger is invalid (in this case because there is no semi-colon at the end of the null statement), you get an error when you try to create it but you can still add the column:

 

SQL> create table tab1 (col1 varchar2(1))

  2  /

 

Table created.

 

SQL> create trigger trig1

  2  after update on tab1

  3  begin

  4  null

  5  end;

  6  /

 

Warning: Trigger created with compilation errors.

 

SQL> alter table tab1

  2  add col2 varchar2(1)

  3  /

 

Table altered.

 

SQL> desc tab1

Name                       Null?    Type

-------------------------- -------- ------------------

COL1                                VARCHAR2(1)

COL2                                VARCHAR2(1)

 

SQL>

 

However, if you include a default clause when you add the column, you get an ORA-04098 instead and the column is not added to the table:

 

SQL> create table tab1 (col1 varchar2(1))

  2  /

 

Table created.

 

SQL> create trigger trig1

  2  after update on tab1

  3  begin

  4  null

  5  end;

  6  /

 

Warning: Trigger created with compilation errors.

 

SQL> alter table tab1

  2  add col2 varchar2(1) default 'Y'

  3  /

alter table tab1

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-04098: trigger 'ANDREW.TRIG1' is invalid and

failed re-validation

 

SQL> desc tab1

Name                       Null?    Type

-------------------------- -------- ------------------

COL1                                VARCHAR2(1)

 

SQL>

 

Of course, if the trigger is valid, the column gets added even with the default clause:

 

SQL> create table tab1 (col1 varchar2(1))

  2  /

 

Table created.

 

SQL> create trigger trig1

  2  after update on tab1

  3  begin

  4  null;

  5  end;

  6  /

 

Trigger created.

 

SQL> alter table tab1

  2  add col2 varchar2(1) default 'Y'

  3  /

 

Table altered.

 

SQL> desc tab1

Name                       Null?    Type

-------------------------- -------- ------------------

COL1                                VARCHAR2(1)

COL2                                VARCHAR2(1)

 

SQL>