Oracle DBMS_STANDARD
Version 11.2.0.2
 
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmsstdx.sql
First Available 7.3.4
Data Types type ora_name_list_t is table of varchar2(64);
Dependencies Essentially everything (795 objects)
Security Model Execute is granted to PUBLIC
 
APPLYING_CROSSEDITION_TRIGGER
Undocumented dbms_standard.applying_crossedition_trigger RETURN BOOLEAN;
TBD
 
CLIENT_IP_ADDRESS
Undocumented dbms_standard.client_ip_address RETURN VARCHAR2;
TBD
 
COMMIT
Commit Transaction Command Function dbms_standard.commit(
CREATE TABLE t (
testcol VARCHAR2(20));

INSERT INTO t (testcol) VALUES ('TEST');

exec dbms_standard.commit;

rollback;

SELECT * FROM t;
 
COMMIT_CM
Commit Transaction Command Function with Comment dbms_standard.commit_cm(vc IN VARCHAR2);
CREATE TABLE t (
testcol VARCHAR2(20));

INSERT INTO t (testcol) VALUES ('TEST');

exec dbms_standard.commit_cm('Record Committed');

rollback;

SELECT * FROM t;
 
DATABASE_NAME
Database Name dbms_standard.database_name RETURN VARCHAR2;
SELECT dbms_standard.database_name FROM dual;
 
DELETING
Trigger Operation Function dbms_standard.deleting RETURN BOOLEAN;
See Table Triggers Demos
 
DES_ENCRYPTED_PASSWORD
Functionality underlying System Event ORA_DES_ENCRYPTED_PASSWORD dbms_standard.des_encrypted_password(user IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
CREATE OR REPLACE TRIGGER sysevent_trig
AFTER ALTER
ON DATABASE
BEGIN
  dbms_output.put_line(dbms_standard.des_encrypted_password);
END sysevent_trig;
/

set serveroutput on

ALTER USER uwclass
IDENTIFIED BY sowhat;

ALTER USER uwclass
IDENTIFIED BY uwclass;
 
DICTIONARY_OBJ_NAME
Functionality underlying System Event ORA_DICT_OBJ_NAME dbms_standard.dictionary_obj_name RETURN VARCHAR2;
CREATE OR REPLACE TRIGGER bcs_trigger
BEFORE CREATE
ON SCHEMA
BEGIN
  dbms_output.put_line(dbms_standard.dictionary_obj_name);
END bcs_trigger;
/

set serveroutput on

CREATE TABLE t (
testcol DATE);
 
DICTIONARY_OBJ_NAME_LIST
Undocumented dbms_standard.dictionary_obj_name_list(object_list OUT ora_name_list_t)
RETURN BINARY_INTEGER;
TBD
 
DICTIONARY_OBJ_OWNER
Functionality underlying System Event ORA_DICT_OBJ_OWNER dbms_standard.dictionary_obj_owner RETURN VARCHAR2;
CREATE OR REPLACE TRIGGER bcs_trigger
BEFORE CREATE
ON SCHEMA
BEGIN
  dbms_output.put_line(dbms_standard.dictionary_obj_owner);
END bcs_trigger;
/

set serveroutput on

CREATE TABLE t (
testcol DATE);
 
DICTIONARY_OBJ_OWNER_LIST
Undocumented dbms_standard.dictionary_obj_owner_list(owner_list OUT ora_name_list_t)
RETURN BINARY_INTEGER;
TBD
 
DICTIONARY_OBJ_TYPE
Functionality underlying System Event ORA_DICT_OBJ_TYPE dbms_standard.dictionary_obj_type RETURN VARCHAR2;
CREATE OR REPLACE TRIGGER bcs_trigger
BEFORE CREATE
ON SCHEMA
BEGIN
  dbms_output.put_line(dbms_standard.dictionary_obj_type);
END bcs_trigger;
/

set serveroutput on

CREATE TABLE t (
testcol DATE);
 
GRANTEE
Undocumented dbms_standard.grantee(user_list OUT ora_name_list_t) RETURN BINARY_INTEGER;
TBD
 
INSERTING
Trigger Operation Function dbms_standard.inserting RETURN BOOLEAN;
See Table Triggers Demos
 
INSTANCE_NUM
Likely the functionality underlying the System Event ORA_INSTANCE_NUM dbms_standard.instance_num RETURN BINARY_INTEGER;
SELECT dbms_standard.instance_num FROM dual;
 
IS_ALTER_COLUMN
Undocumented dbms_standard.is_alter_column(column_name IN VARCHAR2) RETURN BOOLEAN;
TBD
 
IS_CREATING_NESTED_TABLE
Undocumented dbms_standard.is_creating_nested_table RETURN BOOLEAN;
TBD
 
IS_DROP_COLUMN
Undocumented dbms_standard.is_drop_column(column_name IN VARCHAR2) RETURN BOOLEAN;
TBD
 
IS_SERVERERROR
Undocumented dbms_standard.is_servererror(errno IN BINARY_INTEGER) RETURN BOOLEAN;
TBD
 
LOGIN_USER
Likely the functionality underlying the function USER dbms_standard.login_user RETURN VARCHAR2;
SELECT dbms_standard.login_user FROM dual;
 
PARTITION_POS
Undocumented dbms_standard.partition_pos RETURN BINARY_INTEGER;
TBD
 
PRIVILEGE_LIST
Undocumented dbms_standard.privilege_list(priv_list OUT ora_name_list_t) RETURN BINARY_INTEGER;
TBD
 
RAISE_APPLICATION_ERROR
Raises an application error

Trigger Operation Function		 dbms_standard.raise_application_error(
num            IN BINARY_INTEGER,
msg            IN VARCHAR2,
keeperrorstack IN BOOLEAN DEFAULT FALSE);
BEGIN
  dbms_standard.raise_application_error('-20999', 'This is a demo');
END;
/
 
REVOKEE
Undocumented dbms_standard.revokee(user_list OUT ora_name_list_t) RETURN BINARY_INTEGER;
TBD
 
ROLLBACK_NR
Rollback Transaction dbms_standard.rollback_nr;
CREATE TABLE t (
testcol VARCHAR2(20));

INSERT INTO t (testcol) VALUES ('TEST');

SELECT * FROM t;

exec dbms_standard.rollback_nr;

SELECT * FROM t;
 
ROLLBACK_SV
Rollback Transaction to SavePoint dbms_standard.rollback_sv(save_point IN VARCHAR2);
CREATE TABLE t (
testcol VARCHAR2(20));

INSERT INTO t (testcol) VALUES ('A');

exec dbms_standard.savepoint('SP1');

INSERT INTO t (testcol) VALUES ('B');

SELECT * FROM t;

exec dbms_standard.rollback_sv('SP1');

SELECT * FROM t;
 
SAVEPOINT
Create a save point dbms_standard.savepoint(save_point IN VARCHAR2);
See ROLLBACK_SV demo above
 
SERVER_ERROR
Undocumented dbms_standard.server_error(position IN BINARY_INTEGER) RETURN BINARY_INTEGER;
TBD
 
SERVER_ERROR_DEPTH
Undocumented dbms_standard.server_error_depth RETURN BINARY_INTEGER;
TBD
 
SERVER_ERROR_MSG
Undocumented dbms_standard.server_error_msg(position IN BINARY_INTEGER) RETURN VARCHAR2;
TBD
 
SERVER_ERROR_NUM_PARAMS
Undocumented dbms_standard.server_error_num_params(position IN BINARY_INTEGER)
RETURN BINARY_INTEGER;
TBD
 
SERVER_ERROR_PARAM
Undocumented dbms_standard.server_error_param(
position IN BINARY_INTEGER,
param    IN BINARY_INTEGER)
RETURN VARCHAR2;
TBD
 
SET_TRANSACTION_USE
Undocumented dbms_standard.set_transaction_use(vc IN VARCHAR2);
TBD
 
SQL_TXT
Returns the text of th SQL statement that fired the trigger function sql_txt (sql_text OUT ora_name_list_t) RETURN BINARY_INTEGER;
CREATE OR REPLACE TRIGGER test
AFTER GRANT
ON DATABASE
DECLARE
 stmnt_list dbms_standard.ora_name_list_t;
 n          PLS_INTEGER;
BEGIN
  IF (ora_sysevent = 'GRANT') THEN
    n := sql_txt(stmnt_list);
    dbms_output.put_line(n);
    dbms_output.put_line(stmnt_list(n));
  END IF;
END test;
/

set serveroutput on

GRANT all ON servers TO scott;
 
SYSEVENT
This is likely the base functionality underlying ORASYSEVENT dbms_standard.sysevent RETURN VARCHAR2;
CREATE OR REPLACE TRIGGER bcs_trigger
BEFORE CREATE
ON SCHEMA
BEGIN
  dbms_output.put_line(dbms_standard.sysevent);
END bcs_trigger;
/

set serveroutput on

CREATE TABLE t (
testcol DATE);
 
SYS_GETTRIGGERSTATE
Undocumented dbms_standard.sys_gettriggerstate RETURN PLS_INTEGER;
CREATE OR REPLACE TRIGGER bcs_trigger
BEFORE CREATE
ON SCHEMA
BEGIN
  dbms_output.put_line(dbms_standard.sys_gettriggerstate);
END bcs_trigger;
/

set serveroutput on

CREATE TABLE t (
testcol DATE);
 
UPDATING
Trigger Operation Function
Overload 1		 dbms_standard.upating RETURN BOOLEAN;
See Table Triggers Demos
Overload 2 dbms_standard.upating(colnam IN VARCHAR2) RETURN BOOLEAN
CREATE TABLE t (
col1 VARCHAR2(10),
col2 VARCHAR2(10),
col3 VARCHAR2(10));

CREATE OR REPLACE TRIGGER updating_demo
BEFORE INSERT OR UPDATE OR DELETE
ON t
FOR EACH ROW
BEGIN
  IF dbms_standard.updating('COL2') THEN
    dbms_output.put_line('Updating Column 2');
  END IF;
END updating_demo;
/

set serveroutput on

INSERT INTO t VALUES ('A', 'B', 'C');
UPDATE t SET col1 = 'Test';
UPDATE t SET col2 = 'Test';
 
WITH_GRANT_OPTION
Undocumented dbms_standard.with_grant_option RETURN BOOLEAN;
TBD
 
Related Topics
DDL Event Triggers
Instead-Of Triggers
Standard
System Events
System Event Triggers
SYS_CONTEXT
Table Triggers
Undocumented Oracle
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: © 2012 Daniel A. Morgan All Rights Reserved