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(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 ('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
|
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_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 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 ('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;
|
