CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_DDL
Version 11.1
 
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
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.
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
 
Related Topics
DBMS_APPLY_ADM
DBMS_SQL
Native Dynamic SQL
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [42 users online]    © 2010 psoug.org