Oracle 12c DBMS_DDL_INTERNAL Demos

Oracle DBMS_DDL_INTERNAL Version 12.1.0.2

General Information

Library Note

Morgan's Library Page Footer

The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,

Purpose

Support package for DBMS_DDL

AUTHID

DEFINER

Dependencies

DBMS_DDL	DBMS_SYS_ERROR	TRIGGER$
DBMS_PRIV_CAPTURE	DBMS_SYS_SQL	USER$
DBMS_SQL	DBMS_TRIGGER_LIB	V$PARAMETER
DBMS_STANDARD	DBMS_UTILITY	_CURRENT_EDITION_OBJ

Documented

Security Model

Owned by SYS with no privileges granted

Source

{ORACLE_HOME}/rdbms/admin/prvthddl.plb

Subprograms

CHECK_TRIGGER_FIRING_PROPERTY	HAS_ALTER_ANY_TRIGGER_PRIV	IS_DDL_TRIGGER
COMPAT_OK	HAS_EXP_IMP_PRIV	SWAP_BOOTSTRAP
GEN_NEW_OBJECT_ID

CHECK_TRIGGER_FIRING_PROPERTY

Undocumented

dbms_ddl_internal.check_trigger_firing_property(
trig_owner        IN     VARCHAR2,
trig_name         IN     VARCHAR2,
canon_owner          OUT VARCHAR2,
canon_oname          OUT VARCHAR2,
p_property        IN OUT NUMBER,
unsupported_trig     OUT BOOLEAN);

conn uwclass/uwclass@pdbdev

CREATE OR REPLACE TRIGGER t_trig
BEFORE UPDATE
ON airplanes
BEGIN
NULL;
END t_trig;
/

conn sys@pdbdev as sysdba

set serveroutput on

DECLARE
co VARCHAR2(30);
cn VARCHAR2(30);
pp NUMBER(10) := 0;
ut BOOLEAN;
BEGIN
dbms_ddl_internal.check_trigger_firing_property('UWCLASS', 'T_TRIG', co, cn, pp, ut);

dbms_output.put_line(co);
dbms_output.put_line(cn);
dbms_output.put_line(TO_CHAR(pp));

IF ut THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/

COMPAT_OK

Undocumented

dbms_ddl_internal.compat_ok(rcompat IN NUMBER) RETURN BOOLEAN;

set serveroutput on

BEGIN
IF dbms_ddl_internal.compat_ok(11) THEN
    dbms_output.put_line('TRUE');
ELSE
    dbms_output.put_line('FALSE');
END IF;

IF dbms_ddl_internal.compat_ok(12) THEN
    dbms_output.put_line('TRUE');
ELSE
    dbms_output.put_line('FALSE');
END IF;
END;
/

GEN_NEW_OBJECT_ID

Used to generate object identifiers

dbms_ddl_internal.gen_new_object_id RETURN BINARY_INTEGER;

SELECT dbms_ddl_internal.gen_new_object_id
FROM dual;

HAS_ALTER_ANY_TRIGGER_PRIV

Returns true of the schema owner has the name privilege

dbms_ddl_internal.has_alter_any_trigger_priv(
p_user IN VARCHAR2,
p_trig_property IN NUMBER)
RETURN BOOLEAN;

conn sys@pdbdev as sysdba

set serveroutput on

BEGIN
IF dbms_ddl_internal.has_alter_any_trigger_priv('UWCLASS', 1) THEN
dbms_output.put_line('Granted');
ELSE
dbms_output.put_line('Not Granted');
END IF;
END;
/

GRANT alter any trigger TO uwclass;

BEGIN
IF dbms_ddl_internal.has_alter_any_trigger_priv('UWCLASS', 1) THEN
dbms_output.put_line('Granted');
ELSE
dbms_output.put_line('Not Granted');
END IF;
END;
/

HAS_EXP_IMP_PRIV

Returns true if the schema owner has export and/or import full database privileges

dbms_ddl_internal.has_exp_imp_priv(
p_uid IN NUMBER,
p_privs_to_check IN VARCHAR2)
RETURN BOOLEAN;

-- the following does not work ... and no working combination has been discovered. Solutions will be gratefully accepted.

conn sys@pdbdev as sysdba

SELECT user_id
FROM dba_users
WHERE username = 'UWCLASS';

set serveroutput on

BEGIN
IF dbms_ddl_internal.has_exp_imp_priv(82,'EXPORT FULL DATABASE') THEN
dbms_output.put_line('Granted');
ELSE
dbms_output.put_line('Not Granted');
END IF;
END;
/

IS_DDL_TRIGGER

Returns true of the trigger is a DDL Event Trigger

dbms_ddl_internal.is_ddl_trigger(sys_evts IN NUMBER) RETURN BOOLEAN;

conn sys@pdbdev as sysdba

desc trigger$

SELECT sys_evts, type#, COUNT(*)
FROM trigger$
GROUP BY sys_evts, type#
ORDER BY 1;

-- from sql.bsq
type# number not null, /* trigger type: */
/* 0 = BEFORE TABLE, 1 = BEFORE ROW, 2 = AFTER TABLE, 3 = AFTER ROW */
/* 4 = INSTEAD OF TRIGGER */
-- also see dba_triggers in catprc.sql

set serveroutput on

BEGIN
IF dbms_ddl_internal.is_ddl_trigger(31) THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/

BEGIN
IF dbms_ddl_internal.is_ddl_trigger(32) THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/

SWAP_BOOTSTRAP

Undocumented. Used in the script utlmmig.sql

dbms_ddl_internal.swap_bootstrap(replacement_tbl_name IN VARCHAR2);

TBD