Oracle DBMS_DDL
Version 26ai

General Information
Library Note Morgan's Library Page Header
The best Oracle News for FY2026

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?
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_LOB DBMS_STATS DRVXTABC
DBMS_PRIV_CAPTURE DBMS_SYS_ERROR OWM_CPKG_PKG
DBMS_SQL DBMS_SYS_SQL PLITBLM
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 dbms_ddl.alter_compile(
type           IN VARCHAR2,
schema         IN VARCHAR2,
name           IN VARCHAR2,
reuse_settings IN BOOLEAN := FALSE);
Deprecated since 10gR2

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

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

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'));
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
Built-in Functions
Built-in Packages
DBMS_APPLY_ADM
DBMS_DDL_INTERNAL
DBMS_SQL
Native Dynamic SQL
Wrap Utility
What's New In 21c
What's New In 26ai

Morgan's Library Page Footer
This site is maintained by Daniel Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2026 Daniel A. Morgan All Rights Reserved