CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle System Events
Version 11.1
 
General
Source {ORACLE_HOME}/rdbms/admin/dbmstrig.sql
 

Event Table DDL
CREATE TABLE event_log (
database_name VARCHAR2(50),
client_ipadd  VARCHAR2(15),
encrypt_pwd   VARCHAR2(100),
obj_name      VARCHAR2(30),
obj_owner     VARCHAR2(30),
obj_type      VARCHAR2(20),
instance_num  NUMBER);

CREATE TABLE parent (
person_id NUMBER(5),
last_name VARCHAR2(20));
 
ORA_CLIENT_IP_ADDRESS

Client IP address when protocol is TCP/IP

May not work on single Windows machine: Thus the NVL
CREATE OR REPLACE FUNCTION client_ip_address RETURN VARCHAR2 IS
BEGIN
  RETURN dbms_standard.client_ip_address;
END;
/
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER LOGON
ON DATABASE
BEGIN
  IF (ora_sysevent='LOGON') THEN
    INSERT INTO event_log
    (client_ipadd)
    VALUES
    (NVL(ora_client_ip_address, 'N/A'));
  END IF;
END sysevent_trig;
/

TRUNCATE TABLE event_log;

conn uwclass/uwclass

SELECT client_ipadd FROM event_log;
 
ORA_DATABASE_NAME

Database name

Returns a
VARCHAR2(50)
CREATE OR REPLACE FUNCTION database_name RETURN VARCHAR2 IS
BEGIN
  RETURN dbms_standard.database_name;
END;
/
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER LOGON
ON DATABASE
BEGIN
  INSERT INTO event_log
  (database_name)
  VALUES
  (ora_database_name);
END sysevent_trig;
/

TRUNCATE TABLE event_log;

conn uwclass/uwclass

SELECT database_name FROM event_log;
 
ORA_DES_ENCRYPTED_PASSWORD

The DES encrypted password of the user being created or altered
CREATE OR REPLACE FUNCTION des_encrypted_password(
user IN VARVCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
BEGIN
  RETURN dbms_standard.des_encrypted_password(user);
END;
/
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER ALTER
ON DATABASE
BEGIN
  INSERT INTO event_log
  (database_name, encrypt_pwd)
  VALUES
  (ora_database_name, ora_des_encrypted_password);
END sysevent_trig;
/

TRUNCATE TABLE event_log;

ALTER USER uwclass
IDENTIFIED BY sowhat;

ALTER USER uwclass
IDENTIFIED BY uwclass;

col encrypt_pwd format a40

SELECT database_name, encrypt_pwd FROM event_log;
 
ORA_DICT_OBJ_NAME

Name of the dictionary object on which the DDL operation occurred

Returns a
VARCHAR2(30)
CREATE OR REPLACE FUNCTION dictionary_obj_name RETURN VARCHAR2 IS
BEGIN
  RETURN dbms_standard.dictionary_obj_name;
END;
/
drop trigger sysevent_trig;

CREATE OR REPLACE TRIGGER sysevent_trig
AFTER ALTER
ON SCHEMA
BEGIN
  INSERT INTO event_log
  (database_name, obj_name)
  VALUES
  (ora_database_name, ora_dict_obj_name);
END sysevent_trig;
/

TRUNCATE TABLE event_log;

desc parent

ALTER TABLE parent
ADD (event_when TIMESTAMP(9));

desc parent

SELECT database_name, obj_name FROM event_log;
 
ORA_DICT_OBJ_NAME_LIST

Return the list of object names of objects being modified by the event
CREATE OR REPLACE FUNCTION dictionary_obj_name_list 
(object_list OUT ora_name_list_t) RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.dictionary_obj_name_list(object_list);
END;
/
drop trigger sysevent_trig;

CREATE FUNCTION ftest RETURN VARCHAR2 IS
BEGIN
  RETURN 'ZZYZX';
END ftest;
/

CREATE OR REPLACE TRIGGER sysevent_trig
AFTER ASSOCIATE STATISTICS
ON DATABASE
DECLARE
 nlist_t ora_name_list_t;
 x       PLS_INTEGER;
BEGIN
  IF ora_sysevent='ASSOCIATE STATISTICS' THEN
    x := ora_dict_obj_name_list(nlist_t);
  END IF;

  FOR i IN 1 .. x
  LOOP
    dbms_output.put_line(nlist_t(i));
  END LOOP;
END sysevent_trig;
/

set serveroutput on

ASSOCIATE STATISTICS WITH FUNCTIONS ftest DEFAULT SELECTIVITY 10;
 
ORA_DICT_OBJ_OWNER

Owner of the dictionary object on which the DDL operation occurred

Returns a
VARCHAR2(30)
CREATE OR REPLACE FUNCTION dictionary_obj_owner RETURN VARCHAR2 IS
BEGIN
  RETURN dbms_standard.dictionary_obj_owner;
END;
/
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER ALTER
ON SCHEMA
BEGIN
  INSERT INTO event_log
  (obj_owner)
  VALUES
  (ora_dict_obj_owner);
END sysevent_trig;
/

desc parent

ALTER TABLE parent
MODIFY (event_when TIMESTAMP(6));

SELECT * FROM event_log;
 
ORA_DICT_OBJ_OWNER_LIST

Return the list of object owners of objects being modified by the event
CREATE OR REPLACE FUNCTION dictionary_obj_owner_list
(owner_list out ora_name_list_t)
RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.dictionary_obj_owner_list(owner_list);
END;
/

ora_dict_obj_name_list is a synonym for dictionary_obj_name_list.
DECLARE
 x       PLS_INTEGER;
 olist_t ora_name_list_t; <- verify this for function
BEGIN
  IF ora_sysevent = 'ASSOCIATE STATISTICS' THEN
    x := ora_dict_obj_owner_list(olist_t);
  END IF;

  FOR i IN 1 .. x
  LOOP 
    dbms_output.put_line(olist_t(i));
  END LOOP;
END;
/
 
ORA_DICT_OBJ_TYPE

Type of the dictionary object on which the DDL operation occurred

Returns a VARCHAR2(20)
CREATE OR REPLACE FUNCTION dictionary_obj_type RETURN VARCHAR2 IS
BEGIN
  RETURN dbms_standard.dictionary_obj_type;
END;
/
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER ALTER
ON SCHEMA
BEGIN
  INSERT INTO event_log
  (obj_type)
  VALUES
  (ora_dict_obj_type);
END sysevent_trig;
/

desc parent

ALTER TABLE parent
MODIFY (event_when DATE);

SELECT * FROM event_log;
 
ORA_GRANTEE

Return the grantees of a grant event
CREATE OR REPLACE FUNCTION grantee (user_list OUT ora_name_list_t) 
RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.grantee(user_list);
END;
/

ora_dict_obj_name_list is a synonym for dictionary_obj_name_list.
CREATE OR REPLACE TRIGGER ddl_trig
BEFORE GRANT
ON DATABASE
DECLARE
 glist dbms_standard.ora_name_list_t;
 n     PLS_INTEGER;
BEGIN
  IF (ora_sysevent = 'GRANT') THEN
    n := ora_grantee(g_list);
  END IF;
  dbms_output.put_line(TO_CHAR(n));
END; 
/

set serveroutput on

GRANT select ON servers TO PUBLIC;

REVOKE select ON servers FROM PUBLIC;
 
ORA_INSTANCE_NUM

Instance number
CREATE OR REPLACE FUNCTION instance_num RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.instance_num;
END;
/
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER LOGON
ON DATABASE
BEGIN
  INSERT INTO event_log
  (instance_num)
  VALUES
  (ora_instance_num);
END sysevent_trig;
/

conn uwclass/uwclass

SELECT * FROM event_log;
 
ORA_IS_ALTER_COLUMN

Returns true if the specified column is altered
CREATE OR REPLACE FUNCTION is_alter_column(column_name IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
  RETURN dbms_standard.is_alter_column(column_name);
END;
/
CREATE OR REPLACE TRIGGER sysevent_trig
BEFORE ALTER
ON SCHEMA
BEGIN
  IF ora_is_alter_column('PERSON_ID') THEN
    RAISE_APPLICATION_ERROR(-20001,
    'Primary Key Column Can Not Be
    Altered');
  END IF;
END sysevent_trig;
/

ALTER TABLE parent
MODIFY (last_name VARCHAR2(25));

ALTER TABLE parent
MODIFY (person_id NUMBER(6));
 
ORA_IS_CREATING_NESTED_TABLE

Returns true if the current event is creating a nested table
CREATE OR REPLACE FUNCTION is_creating_nested_table
RETURN BOOLEAN IS
BEGIN
  RETURN dbms_standard.is_creating_nested_table;
END;
/
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

Returns true if the specified column is dropped
CREATE OR REPLACE FUNCTION is_drop_column(column_name IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
  RETURN dbms_standard.is_drop_column(column_name);
END;
/
CREATE TABLE t (
col1 DATE,
col2 DATE,
col3 DATE);

set serveroutput on

CREATE OR REPLACE TRIGGER sysevent_trig
AFTER ALTER
ON SCHEMA
DECLARE
 drop_col BOOLEAN;
BEGIN
  IF (ora_sysevent = 'ALTER'
  AND ora_dict_obj_type = 'TABLE') THEN
    drop_col := ora_is_drop_column('COL2');
  END IF;

  IF drop_col THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/

ALTER TABLE t DROP COLUMN col3;
ALTER TABLE t DROP COLUMN col2;
 
ORA_IS_SERVERERROR

Returns TRUE if given error is on error stack
CREATE OR REPLACE FUNCTION is_servererror(errno IN BINARY_INTEGER)
RETURN BOOLEAN IS
BEGIN
  RETURN dbms_standard.is_servererror(errno);
END;
/
set serveroutput on

CREATE OR REPLACE TRIGGER sysevent_trig
AFTER DDL
ON DATABASE
DECLARE
 error_number NUMBER := SQLCODE;
BEGIN
  dbms_output.put_line(TO_CHAR(error_number));
  IF ora_is_servererror(error_number) THEN
    dbms_output.put_line('Oops!: ' || sqlerrm);
  ELSE
    dbms_output.put_line('Ok: ' || sqlerrm);
  END IF;
END sysevent_trig;
/

CREATE TABLE t (col DATE);
 
ORA_LOGIN_USER

Login user name
CREATE OR REPLACE FUNCTION login_user RETURN VARCHAR2 IS
BEGIN
  RETURN dbms_standard.login_user;
END;
/
SELECT ora_login_user
FROM dual;
 
ORA_PARTITION_POS

In an INSTEAD OF trigger for CREATE TABLE, the position within the SQL text where you could insert a PARTITION clause.
CREATE OR REPLACE FUNCTION partition_pos
RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.partition_pos;
END;
/
-- 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

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.
CREATE OR REPLACE FUNCTION privilege_list(
priv_list OUT ora_name_list_t) RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.privilege_list(priv_list);
END;
/
IF (ora_sysevent = 'GRANT'
OR ora_sysevent = 'REVOKE') THEN
  numpriv := ora_privilege_list(priv_list);
END IF;
 
ORA_REVOKEE

Returns the revokees of a revoke event in the OUT parameter; RETURNs the number of revokees in the RETURN value.
CREATE OR REPLACE FUNCTION revokee (user_list OUT ora_name_list_t) RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.revokee(user_list);
END;
/
IF (ora_sysevent = 'REVOKE') THEN
  num_of_users := ora_revokee(user_list);
END IF;
 
ORA_SERVER_ERROR

Given a position (1 for top of stack), it RETURNs the error number at that position on error stack
CREATE OR REPLACE FUNCTION server_error (position IN BINARY_INTEGER)
RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.server_error(position);
END;
/
INSERT INTO event_table
(?)
VALUES
('1st error ' || ora_server_error(1));
 
ORA_SERVER_ERROR_DEPTH
Returns the total number of error messages on the error stack CREATE OR REPLACE FUNCTION server_error_depth
RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.server_error_depth;
END;
/
n := ora_server_error_depth;
 
ORA_SERVER_ERROR_MSG

Given a position (1 for top of stack), it RETURNs the error message at that position on error stack
CREATE OR REPLACE FUNCTION server_error_msg (position IN BINARY_INTEGER) RETURN VARCHAR2 IS
BEGIN
  RETURN dbms_standard.server_error_msg(position);
END;
/
INSERT INTO event_table
(?)
VALUES
('1st err msg' || ora_server_error_msg(1));
 
ORA_SERVER_ERROR_NUM_PARAMS
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". CREATE OR REPLACE FUNCTION server_error_num_params (
position IN BINARY_INTEGER) RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.server_error_num_params(position);
END;
/
n := ora_server_error_num_params(1);
 
ORA_SERVER_ERROR_PARAM
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. CREATE OR REPLACE FUNCTION server_error_param(
position IN BINARY_INTEGER, param IN BINARY_INTEGER)
RETURN VARCHAR2 IS
BEGIN
  RETURN dbms_standard.server_error_param(position, param);
END;
/
-- For example, the second %s in a
-- message: "Expected %s, found %s"
param := ora_server_error_param(1,2);
 
ORA_SQL_TXT

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.
CREATE OR REPLACE FUNCTION sql_txt (sql_text OUT ora_name_list_t)
RETURN BINARY_INTEGER IS
BEGIN
  RETURN dbms_standard.sql_txt(sql_text);
END;
/
DECLARE
 sql_text ora_name_list_t;
 v_stmt   VARCHAR2(2000);
BEGIN
  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);
  COMMIT;
END;
/
 
ORA_SYSEVENT
System event firing the trigger: Event name is same as that in the syntax.

Returns a VARCHAR2(20)
CREATE OR REPLACE FUNCTION sysevent RETURN VARCHAR2 IS
BEGIN
  RETURN dbms_standard.sysevent;
END;
/
See Undocumented Oracle Reference
 
ORA_WITH_GRANT_OPTION

Returns true if the privileges are granted with grant option.
CREATE OR REPLACE FUNCTION with_grant_option RETURN BOOLEAN IS
BEGIN
  RETURN dbms_standard.with_grant_option;
END;
/
IF (ora_sysevent = 'GRANT'
AND ora_with_grant_option = TRUE) THEN
  INSERT INTO event_table
  (?) 
  VALUES
  ('with grant option');
END IF;
 
SPACE_ERROR_INFO

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.
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)
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;
 
Related Topics
DDL Triggers
Exception Handling
Resumable Transactions
SYSTEM_Triggers
Undocumented Oracle
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [227 users online]    © 2010 psoug.org