0

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.