These functions can be used as part of a select statement to produce output for groups of objects, such as object creation scripts, like the one shown below.
CONN scott/tiger
SET LONG 20000
SET PAGESIZE 0
SELECT DBMS_METADATA.get_ddl (object_type, object_name, USER)
FROM user_objects;
Note: the output does not include terminators (semicolon 😉 The formatting of the output is improved using the following commands.
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, ‘SQLTERMINATOR’, true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, ‘PRETTY’, true);
END;
/
The results can be spooled into a file for editing.
