Oracle provides a set of functions (defined in the DBMS_STANDARD package) that provide information about what fired the DDL trigger and other information about the trigger state (e.g., the name of the table being dropped). Table 18-2 displays these trigger attribute functions. The following sections offer some examples of usage.
| Table 18-2. DDL trigger event and attribute functions | |
| Name | Description |
| ORA_SYSEVENT | Returns the type of event that caused the DDL trigger to fire (e.g., CREATE, DROP, or ALTER). |
| ORA_LOGIN_USER | Returns the name of the Oracle user for which the trigger fired. |
| ORA_INSTANCE_NUM | Returns the number of the database instance. |
| ORA_DATABASE_NAME | Returns the name of the database. |
| ORA_CLIENT_IP_ADDRESS | Returns the IP address of the client. |
| ORA_DICT_OBJ_TYPE | Returns the type of database object affected by the firing DDL (e.g., TABLE or INDEX). |
| ORA_DICT_OBJ_NAME | Returns the name of the database object affected by the firing DDL. |
| ORA_DICT_OBJ_OWNER | Returns the owner of the database object affected by the firing DDL. |
| ORA_IS_CREATING_NESTED_TABLE | Returns TRUE if a nested table is being created, or FALSE if not. |
| ORA_DES_ENCRYPTED_PASSWORD | Returns the DES-encrypted password of the current user. |
| ORA_IS_ALTER_COLUMN | Returns TRUE if the specified COLUMN_NAME argument is being altered, or FALSE if not. |
| ORA_IS_DROP_COLUMN | Returns TRUE if the specified COLUMN_NAME argument is indeed being dropped, or FALSE if not. |
| ORA_DICT_OBJ_NAME_LIST | Returns the count of objects affected. It also returns a complete list of objects affected in the NAME_LIST parameter, which is a collection of type DBMS_STANDARD.ORA_NAME_LIST_T. |
| ORA_DICT_OBJ_OWNER_LIST | Returns the count of objects affected. It also returns a complete list of object owners affected in the NAME_LIST parameter, which is a collection of type DBMS_STANDARD.ORA_NAME_LIST_T. |
| ORA_GRANTEE | Returns the count of grantees. The USER_LIST argument contains the full list of grantees, which is a collection of type DBMS_STANDARD.ORA_NAME_LIST_T. |
| ORA_WITH_GRANT_OPTION | Returns TRUE if privileges were granted with the GRANT option, or FALSE if not. |
| ORA_PRIVILEGE_LIST | Returns the number of privileges being granted or revoked. The PRIVILEGE_LIST argument contains the full list of privileges affected, which is a collection of type DBMS_STANDARD.ORA_NAME_LIST_T. |
| ORA_REVOKEE | Returns the count of revokees. The USER_LIST argument contains the full list of revokees, which is a collection of type DBMS_STANDARD.ORA_NAME_LIST_T. |
Note the following about the event and attribute functions:
· The datatype ORA_NAME_LIST_T is defined in the DBMS_STANDARD package as:
TYPE ora_name_list_t IS TABLE OF VARCHAR2(64);In other words, this is a nested table of strings, each of which can contain up to 64 characters.
· The DDL trigger event and attribute functions are also defined in the DBMS_STANDARD package. Oracle creates a standalone function (which adds the "ORA_" prefix to the function name) for each of the packaged functions by executing the $ORACLE_HOME/rdbms/dbmstrig.sql script during database creation. In some releases of Oracle, there are errors in this script that cause the standalone functions to not be visible or executable. Here is an example from the 8.1.7 script (notice the error in privileg_list):
create public synonym ora_privilege_list for privileg_listWhile this particular problem was fixed in Oracle9i, you may encounter others. In this case, you should ask your DBA to check the script for problems and make the necessary corrections.
· The USER_SOURCE data dictionary view does not get updated until after both BEFORE and AFTER DDL triggers are fired. In other words, you cannot use these functions to provide a "before and after" version control system built entirely within the database and based on database triggers.
