Oracle DBMS_DDL
Version 26ai

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

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
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