Oracle DBMS_STANDARD
Version 11.2.0.3

General Information
DBMS_STANDARD is a stand-alone package header (no body). The functionality it provides is defined with PRAGMA INTERFACE(C, ...)
AUTHID DEFINER
Data Types TYPE ora_name_list_t IS TABLE OF VARCHAR2(64);
Dependencies 811 objects

SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_STANDARD'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_STANDARD';
First Available 7.3.4
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsstdx.sql
Subprograms
APPLYING_CROSSEDITION_TRIGGER INSERTING SAVEPOINT
CLIENT_IP_ADDRESS INSTANCE_NUM SERVER_ERROR
COMMIT IS_ALTER_COLUMN SERVER_ERROR_DEPTH
COMMIT_CM IS_CREATING_NESTED_TABLE SERVER_ERROR_MSG
DATABASE_NAME IS_DROP_COLUMN SERVER_ERROR_NUM_PARAMS
DELETING IS_SERVERERROR SERVER_ERROR_PARAM
DES_ENCRYPTED_PASSWORD LOGIN_USER SET_TRANSACTION_USE
DICTIONARY_OBJ_NAME PARTITION_POS SQL_TXT
DICTIONARY_OBJ_NAME_LIST PRIVILEGE_LIST SYSEVENT
DICTIONARY_OBJ_OWNER RAISE_APPLICATION_ERROR SYS_GETTRIGGERSTATE
DICTIONARY_OBJ_OWNER_LIST REVOKEE UPDATING
DICTIONARY_OBJ_TYPE ROLLBACK_NR WITH_GRANT_OPTION
GRANTEE ROLLBACK_SV  
 
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;
conn uwclass/uwclass

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);
conn uwclass/uwclass

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
Returns the name of the current database dbms_standard.database_name RETURN VARCHAR2;
SELECT dbms_standard.database_name FROM dual;
 
DELETING
Provides the DELETING functionality in a table or instead-of trigger dbms_standard.deleting RETURN BOOLEAN;
See Table Triggers Demos Using Link At Page Bottom

Look for "IF DELETING THEN"
 
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;
conn uwclass/uwclass

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;
conn / as sysdba

CREATE OR REPLACE FUNCTION ftest RETURN VARCHAR2 AUTHID CURRENT_USER 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 := dbms_standard.dictionary_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;
 
DICTIONARY_OBJ_OWNER
Functionality underlying System Event ORA_DICT_OBJ_OWNER dbms_standard.dictionary_obj_owner RETURN VARCHAR2;
conn uwclass/uwclass

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 dbms_standard.ora_name_list_t)
RETURN BINARY_INTEGER;
conn / as sysdba

CREATE TABLE uwclass.t (
testcol VARCHAR2(20));

CREATE INDEX scott.ix_t
ON uwclass.t(testcol);

CREATE INDEX hr.fbi_t
ON uwclass.t(UPPER(testcol));

CREATE OR REPLACE TRIGGER sysevent_trig
BEFORE DROP
ON DATABASE
DECLARE
 olist_t ora_name_list_t;
 x       PLS_INTEGER;
BEGIN
  x := dbms_standard.dictionary_obj_name_list(olist_t);
  dbms_output.put_line('Return value: ' || TO_CHAR(x));
--  FOR i IN 1 .. x LOOP
--    dbms_output.put_line(olist_t(i));
--  END LOOP;
END sysevent_trig;
/

DROP TABLE uwclass.t;
 
DICTIONARY_OBJ_TYPE
Functionality underlying System Event ORA_DICT_OBJ_TYPE dbms_standard.dictionary_obj_type RETURN VARCHAR2;
conn uwclass/uwclass

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
Return the grantees of a GRANT DCL statement dbms_standard.grantee(user_list OUT ora_name_list_t) RETURN BINARY_INTEGER;
conn / as sysdba

CREATE OR REPLACE TRIGGER ddl_trig
BEFORE GRANT
ON DATABASE
DECLARE
 g_list dbms_standard.ora_name_list_t;
 n     PLS_INTEGER;
BEGIN
  IF (ora_sysevent = 'GRANT') THEN
    n := dbms_standard.grantee(g_list);
  END IF;
  dbms_output.put_line(TO_CHAR(n));
  dbms_output.put_line(g_list(n));
END;
/

set serveroutput on

GRANT select ON uwclass.servers TO PUBLIC;

REVOKE select ON uwclass.servers FROM PUBLIC;
 
INSERTING
Provides the INSERTING functionality in a table or instead-of trigger dbms_standard.inserting RETURN BOOLEAN;
See Table Triggers Demos Using Link At Page Bottom

Look for "IF DELETING THEN"
 
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;
conn uwclass/uwclass

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);
conn uwclass/uwclass

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
Equates to SET TRANSACTION USE ROLLBACK SEGMENT dbms_standard.set_transaction_use(vc IN VARCHAR2);
conn / as sysdba

SELECT segment_name, owner
FROM dba_rollback_segs;

exec dbms_standard.set_transaction_use('SYSTEM');
 
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;
conn / as sysdba

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;
conn uwclass/uwclass

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
Returns the state of a trigger when it fires dbms_standard.sys_gettriggerstate RETURN PLS_INTEGER;
conn uwclass/uwclass

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
Provides the UPDATING functionality in a table or instead-of trigger dbms_standard.upating RETURN BOOLEAN;
See Table Triggers Demos Using Link At Page Bottom

Look for "IF UPDATING THEN"
Overload 2 dbms_standard.upating(colnam IN VARCHAR2) RETURN BOOLEAN
conn uwclass/uwclass

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: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved