Introducing oraddlscript

A recent question on StackOverflow inspired me to write a utility called oraddlscript:

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.

About these ads

This entry was posted on Sunday, October 25th, 2009 at 12:31 and is filed under Code Generation, Oracle, oraddlscript, SQL, XML. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to Introducing oraddlscript

Oracle database – generate scripts for all sources | tHe PaLaCe Of CoNcEpTiOn Is BuRnInG says:

February 14, 2011 at 14:37

[...] http://devio.wordpress.com/2009/10/25/introducing-oraddlscript/ [...]

Reply
Lawrence Ong says:

June 9, 2011 at 17:43

Hello,

and thanks for this script. I’ve found it to be quite useful. However, I also noticed that it does not script out the Java code that’s stored in the database. Is there a mechanism to pull this out at this time?

thanks again.

Reply

devioblog