Saturday, March 01, 2014

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>

No comments: