Skip Headers

Oracle® Database Application Developer's Guide - Fundamentals
10g Release 1 (10.1)

Part Number B10795-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

10
Working With System Events

System events, like LOGON and SHUTDOWN, provide a mechanism for tracking system changes. Oracle Database lets you combine this tracking with database event notification, which provides a simple and elegant method of delivering asynchronous messaging to an application.

This chapter includes descriptions of the various events on which triggers can be created. It also provides the list of event attribute functions. Topics include the following:

Event Attribute Functions

When a trigger is fired, you can retrieve certain attributes about the event that fired the trigger. Each attribute is retrieved by a function call. Table 10-1 describes the system-defined event attributes.


Note:

  • To make these attributes available, you must first run the CATPROC.SQL script.
  • The trigger dictionary object maintains metadata about events that will be published and their corresponding attributes.
  • In earlier releases, these functions were accessed through the SYS package. We recommend you use these public synonyms whose names begin with ora_.

Table 10-1   System-Defined Event Attributes
Attribute Type Description Example
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;

List of Database Events

System Events

System events are related to entire instances or schemas, not individual tables or rows. Triggers created on startup and shutdown events must be associated with the database instance. Triggers created on error and suspend events can be associated with either the database instance or a particular schema.

Table 10-2 contains a list of system manager events.

Table 10-2   System Manager Events  
Event When Fired? Conditions Restrictions Transaction Attribute Functions
STARTUP

When the database is opened.

None allowed

No database operations allowed in the trigger.

Return status ignored.

Starts a separate transaction and commits it after firing the triggers.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
SHUTDOWN

Just before the server starts the shutdown of an instance.

This lets the cartridge shutdown completely. For abnormal instance shutdown, this event may not be fired.

None allowed

No database operations allowed in the trigger.

Return status ignored.

Starts a separate transaction and commits it after firing the triggers.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
SERVERERROR

When the error eno occurs. If no condition is given, then this event fires when any error occurs.

Does not apply to ORA-1034, ORA-1403, ORA-1422, ORA-1423, and ORA-4030 conditions, because they are not true errors or are too serious to continue processing.

ERRNO = eno

Depends on the error.

Return status ignored.

Starts a separate transaction and commits it after firing the triggers.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_server_error
ora_is_
servererror
space_error_info

Client Events

Client events are the events related to user logon/logoff, DML, and DDL operations. For example:

CREATE OR REPLACE TRIGGER On_Logon  
  AFTER LOGON  
  ON The_user.Schema  
BEGIN  
  Do_Something;  
END;  

The LOGON and LOGOFF events allow simple conditions on UID( ) and USER( ). All other events allow simple conditions on the type and name of the object, as well as functions like UID( ) and USER( ).

The LOGON event starts a separate transaction and commits it after firing the triggers. All other events fire the triggers in the existing user transaction.

The LOGON and LOGOFF events can operate on any objects. For all other events, the corresponding trigger cannot perform any DDL operations, such as DROP and ALTER, on the object that caused the event to be generated.

The DDL allowed inside these triggers is altering, creating, or dropping a table, creating a trigger, and compile operations.

If an event trigger becomes the target of a DDL operation (such as CREATE TRIGGER), it cannot be fired later during the same transaction

Table 10-3 contains a list of client events.

Table 10-3   Client Events  
Event When Fired? Attribute Functions
BEFORE ALTER

AFTER ALTER

When a catalog object is altered.

ora_sysevent 
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_type 
ora_dict_obj_name
ora_dict_obj_owner
ora_des_encrypted_password 
(for ALTER USER events)
ora_is_alter_column, ora_is_
drop_column (for ALTER TABLE 
events)
BEFORE DROP

AFTER DROP

When a catalog object is dropped.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_type
ora_dict_obj_name
ora_dict_obj_owner
BEFORE ANALYZE

AFTER ANALYZE

When an analyze statement is issued

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
BEFORE ASSOCIATE STATISTICS

AFTER ASSOCIATE STATISTICS

When an associate statistics statement is issued

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
ora_dict_obj_name_list
ora_dict_obj_owner_list
BEFORE AUDIT
AFTER AUDIT

BEFORE NOAUDIT
AFTER NOAUDIT

When an audit or noaudit statement is issued

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
BEFORE COMMENT

AFTER COMMENT

When an object is commented

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
BEFORE CREATE 

AFTER CREATE

When a catalog object is created.

ora_sysevent 
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_type 
ora_dict_obj_name
ora_dict_obj_owner
ora_is_creating_nested_table 
(for CREATE TABLE events)
BEFORE DDL

AFTER DDL

When most SQL DDL statements are issued. Not fired for ALTER DATABASE, CREATE CONTROLFILE, CREATE DATABASE, and DDL issued through the PL/SQL procedure interface, such as creating an advanced queue.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
BEFORE DISASSOCIATE 
STATISTICS

AFTER DISASSOCIATE STATISTICS

When a disassociate statistics statement is issued

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
ora_dict_obj_name_list
ora_dict_obj_owner_list
BEFORE GRANT

AFTER GRANT

When a grant statement is issued

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
ora_grantee
ora_with_grant_option
ora_privileges
BEFORE LOGOFF

At the start of a user logoff

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
AFTER LOGON

After a successful logon of a user.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_client_ip_address
BEFORE RENAME

AFTER RENAME

When a rename statement is issued.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_owner
ora_dict_obj_type
BEFORE REVOKE

AFTER REVOKE

When a revoke statement is issued

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner
ora_revokee
ora_privileges
AFTER SUSPEND

After a SQL statement is suspended because of an out-of-space condition. The trigger should correct the condition so the statement can be resumed.

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_server_error
ora_is_servererror
space_error_info
BEFORE TRUNCATE

AFTER TRUNCATE

When an object is truncated

ora_sysevent
ora_login_user
ora_instance_num
ora_database_name
ora_dict_obj_name
ora_dict_obj_type
ora_dict_obj_owner