ora_client_ip_address
|
VARCHAR2
|
Returns the IP address of the client in a LOGON event, when the underlying protocol is TCP/IP
|
if (ora_sysevent = 'LOGON') then addr := ora_client_ip_address; end if;
|
ora_database_name
|
VARCHAR2(50)
|
Database name.
|
DECLARE db_name VARCHAR2(50); BEGIN db_name := ora_database_name; END;
|
ora_des_encrypted_password
|
VARCHAR2
|
The DES encrypted password of the user being created or altered.
|
IF (ora_dict_obj_type = 'USER') THEN INSERT INTO event_table (ora_des_encrypted_password); END IF;
|
ora_dict_obj_name
|
VARCHAR(30)
|
Name of the dictionary object on which the DDL operation occurred.
|
INSERT INTO event_table ('Changed object is ' || ora_dict_obj_name');
|
ora_dict_obj_name_list (name_list OUT ora_name_list_t)
|
BINARY_INTEGER
|
Return the list of object names of objects being modified in the event.
|
if (ora_sysevent = 'ASSOCIATE STATISTICS') then number_modified := ora_dict_obj_name_list (name_list); end if;
|
ora_dict_obj_owner
|
VARCHAR(30)
|
Owner of the dictionary object on which the DDL operation occurred.
|
INSERT INTO event_table ('object owner is' || ora_dict_obj_owner');
|
ora_dict_obj_owner_list(owne r_list OUT ora_name_list_t)
|
BINARY_INTEGER
|
Returns the list of object owners of objects being modified in the event.
|
if (ora_sysevent = 'ASSOCIATE STATISTICS') then number_of_modified_objects := ora_dict_obj_owner_list(owner_li st); end if;
|
ora_dict_obj_type
|
VARCHAR(20)
|
Type of the dictionary object on which the DDL operation occurred.
|
INSERT INTO event_table ('This object is a ' || ora_dict_obj_type);
|
ora_grantee( user_list OUT ora_name_list_t)
|
BINARY_INTEGER
|
Returns the grantees of a grant event in the OUT parameter; returns the number of grantees in the return value.
|
if (ora_sysevent = 'GRANT') then number_of_users := ora_grantee(user_list); end if;
|
ora_instance_num
|
NUMBER
|
Instance number.
|
IF (ora_instance_num = 1) THEN INSERT INTO event_table ('1'); END IF;
|
ora_is_alter_column( column_name IN VARCHAR2)
|
BOOLEAN
|
Returns true if the specified column is altered.
|
if (ora_sysevent = 'ALTER' and ora_dict_obj_type = 'TABLE') then alter_column := ora_is_alter_column('FOO'); end if;
|
ora_is_creating_nested_table
|
BOOLEAN
|
Return TRUE if the current event is creating a nested table
|
if (ora_sysevent = 'CREATE' and ora_dict_obj_type = 'TABLE' and ora_is_creating_nested_table) then insert into event_tab values ('A nested table is created'); end if;
|
ora_is_drop_column( column_name IN VARCHAR2)
|
BOOLEAN
|
Returns true if the specified column is dropped.
|
if (ora_sysevent = 'ALTER' and ora_dict_obj_type = 'TABLE') then drop_column := ora_is_drop_column('FOO'); end if;
|
ora_is_servererror
|
BOOLEAN
|
Returns TRUE if given error is on error stack, FALSE otherwise.
|
IF (ora_is_servererror(error_number )) THEN INSERT INTO event_table ('Server error!!'); END IF;
|
ora_login_user
|
VARCHAR2(30)
|
Login user name.
|
SELECT ora_login_user FROM dual;
|
ora_partition_pos
|
BINARY_INTEGER
|
In an INSTEAD OF trigger for CREATE TABLE, the position within the SQL text where you could insert a PARTITION clause.
|
-- Retrieve ora_sql_txt into -- sql_text variable first.
n := ora_partition_pos; new_stmt := substr(sql_text, 1, n-1) || ' ' || my_partition_clause || ' ' || substr(sql_text, n));
|
ora_privilege_list( privilege_list OUT ora_name_list_t)
|
BINARY_INTEGER
|
Returns the list of privileges being granted by the
grantee or the list of privileges revoked from the revokee in the OUT
parameter; returns the number of privileges in the return value.
|
if (ora_sysevent = 'GRANT' or ora_sysevent = 'REVOKE') then number_of_privileges := ora_privilege_list(priv_list); end if;
|
ora_revokee ( user_list OUT ora_name_list_t)
|
BINARY_INTEGER
|
Returns the revokees of a revoke event in the OUT parameter; returns the number of revokees in the return value..
|
if (ora_sysevent = 'REVOKE') then number_of_users := ora_revokee(user_list);
|
ora_server_error
|
NUMBER
|
Given a position (1 for top of stack), it returns the error number at that position on error stack
|
INSERT INTO event_table ('top stack error ' || ora_server_error(1));
|
ora_server_error_depth
|
BINARY_INTEGER
|
Returns the total number of error messages on the error stack.
|
n := ora_server_error_depth; -- This value is used with -- other functions such as -- ora_server_error
|
ora_server_error_msg (position in binary_integer)
|
VARCHAR2
|
Given a position (1 for top of stack), it returns the error message at that position on error stack
|
INSERT INTO event_table ('top stack error message' || ora_server_error_msg(1));
|
ora_server_error_num_params (position in binary_integer)
|
BINARY_INTEGER
|
Given a position (1 for top of stack), it returns the
number of strings that have been substituted into the error message
using a format like "%s".
|
n := ora_server_error_num_params(1);
|
ora_server_error_param (position in binary_integer, param in binary_integer)
|
VARCHAR2
|
Given a position (1 for top of stack) and a parameter
number, returns the matching "%s", "%d", and so on substitution value
in the error message.
|
-- E.g. the 2rd %s in a message -- like "Expected %s, found %s" param := ora_server_error_param(1,2);
|
ora_sql_txt (sql_text out ora_name_list_t)
|
BINARY_INTEGER
|
Returns the SQL text of the triggering statement in the
OUT parameter. If the statement is long, it is broken up into multiple
PL/SQL table elements. The function return value specifies how many
elements are in the PL/SQL table.
|
sql_text ora_name_list_t; stmt VARCHAR2(2000); ... n := ora_sql_txt(sql_text); FOR i IN 1..n LOOP stmt := stmt || sql_text(i); END LOOP; INSERT INTO event_table ('text of triggering statement: ' || stmt);
|
ora_sysevent
|
VARCHAR2(20)
|
System event firing the trigger: Event name is same as that in the syntax.
|
INSERT INTO event_table (ora_sysevent);
|
ora_with_grant_option
|
BOOLEAN
|
Returns true if the privileges are granted with grant option.
|
if (ora_sysevent = 'GRANT' and ora_with_grant_option = TRUE) then insert into event_table ('with grant option'); end if;
|
space_error_info( error_number OUT NUMBER, error_type OUT VARCHAR2, object_owner OUT VARCHAR2, table_space_name OUT VARCHAR2, object_name OUT VARCHAR2, sub_object_name OUT VARCHAR2)
|
BOOLEAN
|
Returns true if the error is related to an out-of-space
condition, and fills in the OUT parameters with information about the
object that caused the error.
|
if (space_error_info(eno, typ, owner, ts, obj, subobj) = TRUE) then dbms_output.put_line('The object ' || obj || ' owned by ' || owner || ' has run out of space.'); end if;
|