Oracle PL/SQL Best Practices

Name

MOD-14: Use ORA_% public synonyms to reference database and schema event trigger attributes.

Synopsis

Within the confines of DDL and database event triggers, there is a lot of information available about what specifically caused the trigger to fire, for example, the exact table and column or the name of the user. This information is available via a set of PL/SQL functions contained in the DBMS_STANDARD package. Always reference these functions via the public synonyms (ORA_%) also provided (and defined in the dbmstrig.sql file in the Rdbms/Admin subdirectory of installed Oracle software).

Here’s a small subset of the functions available (consult dbmstrig.sql for a complete list):

• ora_sysevent: The system event that invokes the system trigger

• ora_dict_obj_owner : The object owner on which the DDL statement is being done

• ora_dict_obj_name : The object name on which the DDL statement is being performed

Example

CREATE OR REPLACE TRIGGER after_create
AFTER CREATE ON SCHEMA
DECLARE
  /*
    || The BAD way. Direct calls to the functions in DBMS_STANDARD
  */
  v_type VARCHAR2(30) := DICTIONARY_OBJ_TYPE;
  v_name VARCHAR2(30) := DICTIONARY_OBJ_NAME;
BEGIN
  -- the GOOD way; via the synonyms
  INSERT INTO log_create
  VALUES(ORA_DICT_OBJ_TYPE,
         ORA_DICT_OBJ_NAME);
  -- the BAD way; via direct calls
  INSERT INTO log_create
  VALUES(v_type,
         v_name);
END;

Benefits

Your code is protected from future changes.

Resources

always_use_ora.sql : Contains the preceding example.