Oracle DBMS_DDL
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. 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.
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_SQL DBMS_SYS_SQL
DBMS_EXPFIL_IR DBMS_STANDARD DBMS_UTILITY
DBMS_IJOB DBMS_STATS DRVXTABC
DBMS_INTERNAL_LOGSTDBY DBMS_SYS_ERROR PLITBLM
DBMS_LOB    
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

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