ora_client_ip_address
| VARCHAR2
| Returns IP address of the client in a LOGON event when the underlying protocol is TCP/IP | DECLARE
v_addr VARCHAR2(11);
IF (ora_sysevent = 'LOGON') THEN
v_addr := ora_client_ip_address;
END IF;
END;
|
ora_database_name
| VARCHAR2(50)
| Database name. | DECLARE
v_db_name VARCHAR2(50);
BEGIN
v_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
VALUES (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
VALUES ('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
VALUES ('object owner is' ||
ora_dict_obj_owner);
|
ora_dict_obj_owner_list
(owner_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_list);
END IF;
|
ora_dict_obj_type
| VARCHAR(20)
| Type of the dictionary object on which the DDL operation occurred. | INSERT INTO event_table
VALUES ('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 VALUES ('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('C');
END IF;
|
ora_is_creating_nested_table
| BOOLEAN
| Returns 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_table
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('C');
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
VALUES ('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.
v_n := ora_partition_pos;
v_new_stmt := SUBSTR(sql_text,1,v_n - 1)
|| ' ' || my_partition_clause
|| ' ' || SUBSTR(sql_text, v_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 revokees 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
VALUES ('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
VALUES ('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 substitution value (%s , %d , and so on) in the error message. | -- For example, the second %s in a
-- message: "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 into multiple PL/SQL table elements. The function return value shows the number of elements are in the PL/SQL table. | sql_text ora_name_list_t;
v_stmt VARCHAR2(2000);
...
n := ora_sql_txt(sql_text);
FOR i IN 1..n LOOP
v_stmt := v_stmt || sql_text(i);
END LOOP;
INSERT INTO event_table
VALUES ('text of triggering statement: '
|| v_stmt);
|
ora_sysevent
| VARCHAR2(20)
| System event firing the trigger: Event name is same as that in the syntax. | INSERT INTO event_table
VALUES (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
VALUES ('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;
|