Oracle DBMS_DDL
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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
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
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 specificed 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

Related Topics
DBMS_APPLY_ADM
DBMS_DDL_INTERNAL
DBMS_SQL
Native Dynamic SQL
Packages
Wrap Utility
What's New In 12cR1
What's New In 12cR2

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