| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsddl.sql |
| First Available |
7.3.4 |
| Constants |
See SET_TRIGGER_FIRING_PROPERTY below |
| Dependencies |
| DBMS_DDL_INTERNAL |
DBMS_RLMGR_IRP |
DBMS_SYS_SQL |
| DBMS_EXPFIL_IR |
DBMS_SQL |
DBMS_UTILITY |
| DBMS_IJOB |
DBMS_STATS |
DRVXTABC |
| DBMS_LOB |
DBMS_SYS_ERROR |
PLITBLM |
|
| 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 |
|
| Security Model |
Execute is granted to PUBLIC |
| Subprograms |
|
| |
| ALTER_COMPILE |
Compile a PL/SQL object
Oracle docs indicate that this was deprecated in 10gR2 but it is still in the package for purposes of backward compatibility |
dbms_ddl.alter_compile(
type IN VARCHAR2,
schema IN VARCHAR2,
name IN VARCHAR2,
reuse_settings IN BOOLEAN := FALSE);
| Type Values |
| FUNCTION |
| PACKAGE |
| PACKAGE BODY |
| PROCEDURE |
| TRIGGER |
|
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 |
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 (new overload and pragma change in 11.2)  |
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);
| Name |
Data Type |
Value |
| APPLY_SERVER_ONLY |
NUMBER |
1 |
| FIRE_ONCE |
NUMBER |
2 |
|
| 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 |
