Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
Purpose
Kernel extensions to the package STANDARD
AUTHID
DEFINER
Constants
Name
Data Type
Value
General
ORA_MAX_NAME_LEN
PLS_INTEGER
128
Data Types
TYPE ora_name_list_t 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' ORDER BY 1;
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;
/ Not Found
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;
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;
/
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;
/
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;
/
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;
/
BEGIN
*
ERROR at line 1:
ORA-20999: This is a demo
ORA-06512: at line 2
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;
/
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 and 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';