How to generate scripts for Oracle schema objects, such as tables, procedures, etc.
The answers directed me to the DBMS_METADATA package (9i documentation, 10g documentation). The function DBMS_METADATA.GET_DDL is the functional equivalent of the MSSQL SMO library, which prompted me to adapt my command-line utility SMOscript to Oracle databases. Voilà, oraddlscript.
oraddlscript 0.14.3584.16268 (c) by devio.at 2009 list and script databases and database objects. usage: oraddlscript [options] [command] options: (leading '-' or '/') -s server TNS name or host name or host:port -svc service service name (if host name is provided) -o owner owner name -u username username (default: integrated authentication) -p password password (if -u is missing, password for sa) -f filename output to file -F directory output to directory -A current ANSI codepage -O ASCII -T Unicode -U UTF8 commands: l list objects s script object/s (using dbms_meta.get*ddl) -xml use dbms_meta.get*xml list object owners on server (implied by -s) list objects in database (implied by -s -o) script all objects (implied by -s -o -F/-f)
The command-line arguments are consistent with SMOscript, except for -d (database) which has been replaced by -o (owner name).
The list of objects is retrieved by querying DBA_OBJECTS, ALL_OBJECTS and USER_OBJECTS depending on which of the catalog views is accessible by the user specified by -u.
The package also contains a function GET_XML which is used to retrieve the XML representation of a database object.
The functions of oraddlscript are:
- list usernames of object owners
- list objects of specific owner
- generate CREATE scripts of objects owned by specific user
- generate XML files representing objects owned by specific user
- generate one file per object or single file for all objects
Of course, logging and batch operations work just as previously described for SMOscript.
oraddlscript is available for download here.