Oracle | DBMS

Oracle DBMS_DDL 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 API to data definition language (DDL) statements from stored procedures and access to operations not available as DDL

AUTHID CURRENT_USER

Constants (SET_TRIGGER_FIRING_PROPERTIES)

Name	Data Type	Value
APPLY_SERVER_ONLY	NUMBER	1
FIRE_ONCE	NUMBER	2

Dependencies

DBMS_DDL_INTERNAL	DBMS_STANDARD	DBMS_UTILITY
DBMS_INTERNAL_LOGSTDBY	DBMS_STATS	DRVXTABC
DBMS_LOB	DBMS_SYS_ERROR	OWM_CPKG_PKG
DBMS_PRIV_CAPTURE	DBMS_SYS_SQL	PLITBLM
DBMS_SQL

Documented Yes: Packages and Types Reference

Exceptions

Error Code	Reason
ORA-01031	Insufficient privileges
ORA-04072	Invalid Type
ORA-20000	Insufficient privileges or object does not exist
ORA-20001	Remote object, cannot compile
ORA-20002	Bad value for object type
ORA-23308	object %s.%s does not exist or is invalid.
ORA-24230	malformed_wrap_input

First Available 7.3.4

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsddl.sql

Subprograms

ALTER_COMPILE	ANALYZE_OBJECT	IS_TRIGGER_FIRE_ONCE_INTERNAL
ALTER_TABLE_NOT_REFERENCEABLE	CREATE_WRAPPED	SET_TRIGGER_FIRING_PROPERTY
ALTER_TABLE_REFERENCEABLE	IS_TRIGGER_FIRE_ONCE	WRAP

ALTER_COMPILE

Compile a PL/SQL object

Deprecated since 10gR2 dbms_ddl.alter_compile( type IN VARCHAR2, schema IN VARCHAR2, name IN VARCHAR2, reuse_settings IN BOOLEAN := FALSE);

CREATE OR REPLACE PROCEDURE testproc IS

      BEGIN

        NULL;

      END testproc;

      /

      

      exec dbms_ddl.alter_compile('PROCEDURE', USER, 'testproc');

ALTER_TABLE_NOT_REFERENCEABLE

Alters an object table's name so it is not the default referenceable table dbms_ddl.alter_table_not_referenceable( table_name IN VARCHAR2, table_schema IN VARCHAR2 DEFAULT NULL, affected_schema IN VARCHAR2 DEFAULT NULL);

exec dbms_ddl.alter_table_not_referenceable('mytable', user, 'UWCLASS');

ALTER_TABLE_REFERENCEABLE

Alter an object table's name so it becomes the default referenceable table dbms_ddl.alter_table_referenceable( table_name IN VARCHAR2, table_schema IN VARCHAR2 DEFAULT NULL, affected_schema IN VARCHAR2 DEFAULT NULL);

CREATE TABLE emp_new OF employee OID AS emp;

      

      INSERT INTO emp_new (sys_nc_oid$, emp_new)

      

      SELECT SYS_NC_OID$, EMP

      FROM EMP;

      

      exec dbms_ddl.alter_table_referenceable('EMP_NEW');

      

      RENAME emp TO emp_old;

      RENAME emp_new TO emp;

ANALYZE_OBJECT

Equivalent to SQL ANALYZE TABLE, CLUSTER, or INDEX dbms_ddl.analyze_object( type IN VARCHAR2, schema IN VARCHAR2, name IN VARCHAR2, method IN VARCHAR2, estimate_rows IN NUMBER DEFAULT NULL, estimate_percent IN NUMBER DEFAULT NULL, method_opt IN VARCHAR2 DEFAULT NULL, partname IN VARCHAR2 DEFAULT NULL); METHOD: ESTIMATE', 'COMPUTE' or 'DELETE' METHOD_OPT: [ FOR TABLE ], [ FOR ALL [INDEXED] COLUMNS] [SIZE n], or [ FOR ALL INDEXES ]

exec dbms_ddl.analyze_object('TABLE', user, 'SERVERS', 'COMPUTE', NULL, NULL, 'FOR TABLE');

CREATE_WRAPPED

Shortcut for dbms_ddl.wrap

Overload 1 dbms_ddl.create_wrapped(ddl IN VARCHAR2);

CREATE OR REPLACE FUNCTION generate_code(tabname VARCHAR2)

      RETURN VARCHAR2 IS

      

      BEGIN

        RETURN 'CREATE OR REPLACE FUNCTION obj_count RETURN INTEGER IS'

        || ' x PLS_INTEGER; ' ||

        'BEGIN ' ||

        'SELECT COUNT(*) ' ||

        'INTO x ' ||

        'FROM ' || tabname || '; ' ||

        'RETURN x; ' ||

        'END obj_count;';

      END generate_code;

      /

      

      SELECT generate_code('ALL_TABLES')

      FROM dual;

      

      DECLARE

       ddl VARCHAR2(32767);

      BEGIN

        ddl := generate_code('ALL_TABLES');

        dbms_output.put_line(ddl);

        dbms_ddl.create_wrapped(ddl);

      END;

      /

      

      desc user_source

      

      SELECT text

      FROM user_source

      WHERE name = 'OBJ_COUNT'

      ORDER BY line;

Shortcut for dbms_sql.parse

Overload 2 dbms_ddl.create_wrapped( ddl IN DBMS_SQL.VARCHAR2S, lb IN PLS_INTEGER, ub IN PLS_INTEGER);

TBD

Shortcut for dbms_sql.parse

Overload 3 dbms_ddl.create_wrapped( ddl IN DBMS_SQL.VARCHAR2A, lb IN PLS_INTEGER, ub IN PLS_INTEGER);

TBD

IS_TRIGGER_FIRE_ONCE

Returns TRUE if the specified DML or DDL trigger is set to fire once dbms_ddl.is_trigger_fire_once( trig_owner IN VARCHAR2, trig_name IN VARCHAR2) RETURN BOOLEAN;

CREATE TABLE t (

      testcol VARCHAR2(20));

      

      CREATE OR REPLACE TRIGGER testtrig

      BEFORE UPDATE

      ON t

      BEGIN

        NULL;

      END testtrig;

      /

      

      set serveroutput on

      

      BEGIN

        IF dbms_ddl.is_trigger_fire_once(user, 'testtrig') THEN

          dbms_output.put_line('TRUE');

        ELSE

          dbms_output.put_line('FALSE');

        END IF;

      END;

      /

      

      -- A FALSE will be reported for a trigger when changes are made by a Streams apply process

      -- or for changes made by executing one or more Streams apply errors using the EXECUTE_ERROR

      -- or EXECUTE_ALL_ERRORS procedures in the DBMS_APPLY_ADM package.

IS_TRIGGER_FIRE_ONCE_INTERNAL

Returns 1 if the specified DML or DDL trigger is set to fire once: otherwise 2 dbms_ddl.is_trigger_fire_once_internal( trig_owner IN VARCHAR2, trig_name IN VARCHAR2) RETURN BINARY_INTEGER;

SELECT owner, trigger_name

      FROM dba_triggers

      ORDER BY 1,2;

      

      SELECT dbms_ddl.is_trigger_fire_once_internal('OE', 'ORDERS_ITEMS_TRG')

      FROM dual;

SET_TRIGGER_FIRING_PROPERTY

Sets the specified DML or DDL trigger's firing property. Used in replication to keep a downstream trigger from firing.

Overload 1 dbms_ddl.set_trigger_firing_property( trig_owner IN VARCHAR2, trig_name IN VARCHAR2, fire_once IN BOOLEAN); PRAGMA SUPPLEMENTAL_LOG_DATA(set_trigger_firing_property, AUTO_WITH_COMMIT);

exec dbms_ddl.set_trigger_firing_property (user, 'streams_trig'. TRUE);

Overload 2 dbms_ddl.set_trigger_firing_property( trig_owner IN VARCHAR2, trig_name IN VARCHAR2, property IN BINARY_INTEGER, setting IN BOOLEAN); PRAGMA SUPPLEMENTAL_LOG_DATA(set_trigger_firing_property, AUTO_WITH_COMMIT);

exec dbms_ddl.set_trigger_firing_property (user, 'streams_trig', dbms_ddl.fire_once, FALSE);

WRAP

Wrap PL/SQL

Overload 1 dbms_ddl.wrap(ddl IN VARCHAR2) RETURN VARCHAR2;

CREATE OR REPLACE FUNCTION generate_code(tabname VARCHAR2)

            RETURN VARCHAR2 IS

            

            BEGIN

              RETURN 'CREATE OR REPLACE FUNCTION obj_count RETURN INTEGER IS'

              || ' x PLS_INTEGER; ' ||

              'BEGIN ' ||

              'SELECT COUNT(*) ' ||

              'INTO x ' ||

              'FROM ' || tabname || '; ' ||

              'RETURN x; ' ||

              'END obj_count;';

            END generate_code;

            /

SELECT generate_code('ALL_TABLES')

      FROM dual;

DECLARE

             ddl VARCHAR2(32767);

            BEGIN

              ddl := dbms_ddl.wrap(generate_code('ALL_TABLES'));

              dbms_output.put_line(ddl);

            END;

            /

or

SELECT dbms_ddl.wrap

(generate_code('ALL_TABLES'))

      FROM dual;

Overload 2 dbms_ddl.wrap(ddl IN DBMS_SQL.VARCHAR2S, lb IN PLS_INTEGER, ub IN PLS_INTEGER) RETURN DBMS_SQL.VARCHAR2S;

TBD

Overload 3 dbms_ddl.wrap(ddl IN DBMS_SQL.VARCHAR2A, lb IN PLS_INTEGER, ub IN PLS_INTEGER) RETURN DBMS_SQL.VARCHAR2A;

TBD