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

3 Responses to Introducing oraddlscript

  1. Lawrence Ong says:

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 65 other followers

%d bloggers like this: