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';
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;
/
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;
/
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
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';