Oracle schema object name when fire trigger

ddl-triggeroracleplsql

I need to get the object name and user name from oracle schema after fire a trigger. It executes on DDL statements. I need to get executed user name and object name which trigger executed on. Below is my PL/SQL code.

CREATE OR REPLACE TRIGGER orders_changes 
after drop or alter or create on schema

DECLARE
 v_username varchar2(10);
 obj_name varchar2(300);

 BEGIN

  -- Find username and object name
  SELECT user, object_name INTO v_username, obj_name
  FROM dual;


  insert into table1 values (v_username, obj_name);

END;

Seems issue is with the way I access the object name. It gives an error. Please any one can help me to solve this.

Best Answer

Simply like this:

CREATE OR REPLACE TRIGGER orders_changes 
after drop or alter or create on schema

BEGIN
  insert into table1 values (USER, ora_dict_obj_name);
END;

See Event Attribute Functions for predefined variables inside triggers.

Related Question