Oracle DBMS_STANDARD
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Purpose Kernel extensions to the package STANDARD
AUTHID DEFINER
Constants
Name Data Type Value
General
ORA_MAX_NAME_LEN PLS_INTEGER 128
Data Types -- 12.2
TYPE ora_name_list_t IS TABLE OF VARCHAR2(64);
-- 12.2
TYPE ora_name_list_x IS TABLE OF VARCHAR2(2*(ORA_MAX_NAME_LEN+2)+1);

SUBTYPE dbms_id IS VARCHAR2(ORA_MAX_NAME_LEN);
SUBTYPE dbms_quoted_id IS VARCHAR2(ORA_MAX_NAME_LEN+2);

SUBTYPE dbms_id_30 IS VARCHAR2(30);
SUBTYPE dbms_quoted_id_30 IS VARCHAR2(32);
SUBTYPE dbms_id_128 IS VARCHAR2(ORA_MAX_NAME_LEN);
SUBTYPE dbms_quoted_id_128 IS VARCHAR2(ORA_MAX_NAME_LEN+2);
Dependencies SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_STANDARD'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_STANDARD';

-- returns 540 objects
Documented No
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 INSTANCE_NUM ROLLBACK_SV
CLIENT_IP_ADDRESS IS_ALTER_COLUMN SAVEPOINT
COMMIT IS_CREATING_NESTED_TABLE SERVER_ERROR
COMMIT_CM IS_DROP_COLUMN SERVER_ERROR_DEPTH
DATABASE_NAME IS_SERVERERROR SERVER_ERROR_MSG
DELETING LOGIN_USER SERVER_ERROR_NUM_PARAMS
DES_ENCRYPTED_PASSWORD ORA_MAX_NAME_LEN_SUPPORTED SERVER_ERROR_PARAM
DICTIONARY_OBJ_NAME ORIGINAL_SQL_TXT SET_TRANSACTION_USE
DICTIONARY_OBJ_NAME_LIST PARTITION_POS SQL_TXT
DICTIONARY_OBJ_OWNER PRIVILEGE_LIST SYSEVENT
DICTIONARY_OBJ_OWNER_LIST RAISE_APPLICATION_ERROR SYS_GETTRIGGERSTATE
DICTIONARY_OBJ_TYPE REVOKEE UPDATING
GRANTEE ROLLBACK_NR WITH_GRANT_OPTION
INSERTING    
 
APPLYING_CROSSEDITION_TRIGGER
Undocumented dbms_standard.applying_crossedition_trigger RETURN BOOLEAN;
BEGIN
  IF dbms_standard.applying_crossedition_trigger THEN
    dbms_output.put_line('True');
  ELSE
    dbms_output.put_line('False');
  END IF;
END;
/
 
CLIENT_IP_ADDRESS
Undocumented dbms_standard.client_ip_address RETURN VARCHAR2;
DECLARE
 cip VARCHAR2(30) := dbms_standard.client_ip_address;
BEGIN
  IF LENGTH(cip) > 0 THEN
    dbms_output.put_line(cip);
  ELSE
    dbms_output.put_line('Not Found');
  END IF;
END;
/
 
COMMIT
Commit Transaction Command Function dbms_standard.commit;
conn uwclass/uwclass@pdbdev

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@pdbdev

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;
conn sys@pdbdev as sysdba

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@pdbdev

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 sys@pdbdev 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@pdbdev

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
Returns the list of object owners affected by the event firing a DDL Event Trigger dbms_standard.dictionary_obj_owner_list(owner_list OUT dbms_standard.ora_name_list_t)
RETURN BINARY_INTEGER;
conn sys@pdbdev 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@pdbdev

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 sys@pdbdev 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
Returns TRUE if in system event trigger fired by an ALTER COLUMN dbms_standard.is_alter_column(column_name IN VARCHAR2) RETURN BOOLEAN;
TBD
 
IS_CREATING_NESTED_TABLE
Returns TRUE if in system event trigger fired by a CREATE NESTED TABLE dbms_standard.is_creating_nested_table RETURN BOOLEAN;
TBD
 
IS_DROP_COLUMN
Returns TRUE if in system event trigger fired by a DROP COLUMN dbms_standard.is_drop_column(column_name IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
IS_SERVERERROR
Returns TRUE if in system event trigger fired by a Server Error 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;
 
ORA_MAX_NAME_LEN_SUPPORTED
Returns the maximum length in bytes supported for an object name dbms_standard.ora_max_name_len_supported RETURN PLS_INTEGER;
conn sys@pdbdev as sysdba

SELECT dbms_standard.ora_max_name_len_supported
FROM dual;
 
ORIGINAL_SQL_TXT
Undocumented dbms_standard.original_sql_txt(sql_text OUT ora_name_list_t) RETURN BINARY_INTEGER;
conn sys@pdbdev 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 := dbms_standard.original_sql_txt(stmnt_list);
    dbms_output.put_line(TO_CHAR(n));
    dbms_output.put_line(stmnt_list(n));
  END IF;
END test;
/

set serveroutput on

GRANT all ON tab$ TO scott;
 
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@pdbdev

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@pdbdev

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
Returns the error number from the input position in the exception stack 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
Returns the error message from the input position in the exception stack 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 sys@pdbdev 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 dbms_standard.sql_txt(sql_text OUT ora_name_list_t) RETURN BINARY_INTEGER;
conn sys@pdbdev 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@pdbdev

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@pdbdev

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

Overload 1		 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@pdbdev

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
Built-in Functions
Built-in Packages
DDL Event Triggers
Instead-Of Triggers
STANDARD
System Events
System Event Triggers
SYS_CONTEXT
Table Triggers
Undocumented Oracle
What's New In 19c
What's New In 20c-21c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx