Dion Cho – Oracle Performance Storyteller

We are natural born scientists

Executing DDL in the DDL trigger

with 2 comments

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.

Written by Dion Cho

March 9, 2009 at 7:16 am

Posted in Misc.

Tagged with ,

2 Responses

Subscribe to comments with RSS.

  1. It’s very interesting that I hit this metalink note# searching other items.

    https://metalink2.oracle.com/metalink/plsql/f?p=130:14:5142332431507679278::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,210693.1,1,1,1,helvetica

    The basic approach is much same with mine. :)

    Dion Cho

    March 10, 2009 at 1:53 am

  2. I can tell that this is not the first time at all that you mention the topic. Why have you decided to touch it again?

    How to Get Six Pack Fast

    April 15, 2009 at 3:11 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: