Executing DDL in the DDL trigger

One of the common requests on the DDL trigger is to execute DDL in it.

For instance, you would like to grant select on the table to another user in the create table DDL trigger. But the fact is that you just end up with following error.

30511, "invalid DDL operation in system triggers"
// *Cause:  An attempt was made to perform an invalid DDL operation
//          in a system trigger. Most DDL operations currently are not
//          supported in system triggers. The only currently supported DDL
//          operations are table operations and ALTER?COMPILE operations.
// *Action: Remove invalid DDL operations in system triggers.

So I devised the trick to use dbms_job to execute the DDL in background.

Let me show a simple demonstration on how to execute DDL in the DDL trigger.

SYS@ukja10> -- as sys user
SYS@ukja10> connect sys /as sysdba
Connected.

SYS@ukja10> -- create after create ddl trigger to execute grant after creating table
SYS@ukja10> create or replace trigger ddl_trigger_test
  2  after create on database
  3  begin
  4  
  5    -- this does not work, ORA-30511
  6    if ora_dict_obj_owner = 'UKJA' and ora_dict_obj_type = 'TABLE' then
  7      execute immediate 'grant select on ' || ora_dict_obj_owner || '.' ||
  8              ora_dict_obj_name || ' to ukja2';
  9    end if;
 10  
 11  end;
 12  /

Trigger created.

SYS@ukja10> connect ukja/ukja@ukja102
Connected.

UKJA@ukja102> -- Oops! ORA-30511 error
UKJA@ukja102> create table tx(c1 int);
create table tx(c1 int)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30511: invalid DDL operation in system triggers
ORA-06512: at line 5 


UKJA@ukja102> connect sys /as sysdba
Connected.

SYS@ukja10> -- stored procedure to execute the DDL statements(in background)
SYS@ukja10> create or replace procedure execute_grant(v_ddl in varchar2)
  2  is
  3  begin
  4  
  5    execute immediate v_ddl;
  6  
  7  end;
  8  /

Procedure created.

SYS@ukja10> -- create after create ddl trigger. see how I forward the request to background process
SYS@ukja10> create or replace trigger ddl_trigger_test
  2  after create on database
  3  declare
  4    v_ddl   varchar2(4000);
  5    v_job   number;
  6  begin
  7    if ora_dict_obj_owner = 'UKJA' and ora_dict_obj_type = 'TABLE' then
  8      v_ddl := 'grant select on ' || ora_dict_obj_owner || '.' ||
  9              ora_dict_obj_name || ' to ukja2';
 10    end if;
 11  
 12    -- execute the ddl after 1 sec
 13    dbms_job.submit(v_job, 'execute_grant(''' || v_ddl || ''');', sysdate+(1/24/60/60));
 14  
 15  
 16  end;
 17  /

Trigger created.


SYS@ukja10> connect ukja/ukja@ukja102
Connected.

UKJA@ukja102> create table tx(c1 int);

Table created.

UKJA@ukja102> -- wait for a while to ensure that the job was executed 
UKJA@ukja102> exec dbms_lock.sleep(5);

PL/SQL procedure successfully completed.

UKJA@ukja102> -- check if another user(ukja2) has been granted to select ukja's table
UKJA@ukja102> connect ukja2/ukja2@ukja102
Connected.

UKJA2@ukja102> -- oh, yes! ukja.tx is visible! 
UKJA2@ukja102> select * from ukja.tx;  

no rows selected

Simple but powerful solution, isn’t it?

I really enjoy the power of dbms_job to implement simple asynchronous job in Oracle.