| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsddl.sql |
| First Available |
7.3.4 |
| Dependencies |
| DBMS_DDL_INTERNAL |
DBMS_STATS |
DBMS_UTILITY |
|
DBMS_IJOB |
DBMS_SYS_ERROR |
DRVXTABC |
| DBMS_LOB |
DBMS_SYS_SQL |
PLITBLM |
|
DBMS_SQL |
|
|
|
| Error Messages |
|
| Object Privileges |
grant execute on dbms_ddl |
| GRANT execute ON dbms_ddl TO uwclass; |
| |
| ALTER_COMPILE |
Compile a PL/SQL object
Note: reuse_settings is a new parameter in 10gR2
Oracle docs indicate that this has been deprecated in 10gR2 but it is still in the package |
dbms_ddl.alter_compile(
type VARCHAR2,
schema VARCHAR2,
name VARCHAR2,
reuse_settings BOOLEAN := FALSE);
| Type Values |
| FUNCTION |
| PACKAGE |
| PROCEDURE |
|
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,user); |
| |
| 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); |
|
exec dbms_ddl.alter_table_referenceable('mytable',user,user); |
| |
| ANALYZE_OBJECT |
Equivalent to SQL ANALYZE TABLE, CLUSTER, or INDEX |
dbms_ddl.analyze_object(
type
VARCHAR2,
schema VARCHAR2,
name VARCHAR2,
method VARCHAR2,
estimate_rows NUMBER DEFAULT NULL,
estimate_percent NUMBER DEFAULT NULL,
method_opt VARCHAR2 DEFAULT NULL,
partname 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 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 DBMS_SQL.VARCHAR2S,
lb PLS_INTEGER,
ub PLS_INTEGER); |
| TBD |
Shortcut for dbms_sql.parse
Overload 3 |
dbms_ddl.create_wrapped(
ddl DBMS_SQL.VARCHAR2A,
lb PLS_INTEGER,
ub 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 |
| This function returns TRUE if the specified DML or DDL trigger is set to fire once. |
dbms_ddl.is_trigger_fire_once_internal(
trig_owner IN VARCHAR2,
trig_name IN VARCHAR2) RETURN BINARY_INTEGER; |
| |
| SET_TRIGGER_FIRING_PROPERTY |
| Sets the specified DML or DDL trigger's firing property.
Used in replication to keep a downstream trigger from firing. |
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'. FALSE); |
| |
| WRAP |
Wrap PL/SQL
Overload 1 |
dbms_ddl.wrap(ddl 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 |
