Oracle DBMS_STANDARD
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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 21c
What's New In 23c

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