Commits

Anthony Tuininga committed 2d806b0

Last public release.

Comments (0)

Files changed (23)

+"""Executes statements found in a file, checking for errors and optionally
+recompiling all invalid source after all statements in the file are
+executed."""
+
+import cx_Logging
+import cx_LoggingOptions
+import cx_OptionParser
+import cx_OracleUtils
+import cx_SQL
+import sys
+
+# parse command line
+parser = cx_OptionParser.OptionParser("CompileSource")
+parser.AddOption(cx_OracleUtils.SchemaOption())
+parser.AddOption("--on-error-continue", action = "store_true",
+        help = "when an error occurs, continue processing")
+parser.AddOption("--with-recompile", action = "store_true",
+        help = "after all source compiled, recompile invalid objects")
+parser.AddOption("--on-recompile-error-continue", action = "store_true",
+        help = "when an error occurs during recompilation, continue "
+               "recompiling")
+cx_LoggingOptions.AddOptions(parser)
+parser.AddArgument("sourceFileName", required = 1,
+        help = "the name of the file containing statements to execute or '-' "
+               "to indicate statements ought to be read from stdin. Note that "
+               "if the file does not exist and does not have the extension "
+               "'.sql' then an attempt is made to open a file with that "
+               "extension")
+options = parser.Parse()
+cx_LoggingOptions.ProcessOptions(options)
+
+# connect to the database
+connection = cx_OracleUtils.Connect(options.schema)
+cursor = connection.cursor()
+
+# compile the objects in the given file
+for statement in cx_SQL.ParseStatementsInFile(options.sourceFileName):
+    try:
+        if isinstance(statement, cx_SQL.AlterStatement):
+            statement.GetConstraintTypeAndName()
+        statement.Process(connection, cursor)
+        logMessage = statement.LogMessage()
+        if logMessage is not None:
+            cx_Logging.Trace("%s", logMessage)
+    except:
+        cx_Logging.Error("Error at line %s", statement.i_LineNo)
+        if not options.onErrorContinue:
+            raise
+        excType, excValue = sys.exc_info()[:2]
+        if excValue:
+            cx_Logging.Error("%s: %s", excType, excValue)
+        else:
+            cx_Logging.Error("%s", excType)
+
+# recompile the invalid objects in the schema, if applicable
+if options.withRecompile:
+    cx_OracleUtils.RecompileInvalidObjects(connection,
+            [connection.username.upper()], [], connection.password,
+            not options.onRecompileErrorContinue)
+
+"""Copy data from the source to the destination, performing inserts and updates
+as necessary to make the destination match the source. Note that deletes are
+not performed, however."""
+
+import cx_Logging
+import cx_LoggingOptions
+import cx_OptionParser
+import cx_OracleUtils
+
+import Options
+
+# parse command line
+parser = cx_OptionParser.OptionParser("CopyData")
+parser.AddOption(cx_OracleUtils.SchemaOption("source-schema"))
+parser.AddOption(cx_OracleUtils.SchemaOption("dest-schema"))
+parser.AddOption("--key-columns", metavar = "COLS",
+        help = "comma separated list of columns to use for checking if the "
+               "row exists")
+parser.AddOption("--no-check-exists", default = 1, action = "store_false",
+        dest = "checkExists",
+        help = "do not check to see if the row exists in the target")
+parser.AddOption("--no-check-modified", default = 1, action = "store_false",
+        dest = "checkModified",
+        help = "do not check to see if the row is identical to the row in the "
+               "destination")
+parser.AddOption("--skip", metavar = "N", type = "int",
+        help = "number of rows to skip before starting the copy")
+parser.AddOption("--row-limit", metavar = "N", type = "int",
+        help = "number of rows at which the copy will stop")
+parser.AddOption(Options.COMMIT_POINT)
+parser.AddOption(Options.REPORT_POINT)
+parser.AddOption(Options.ARRAY_SIZE)
+parser.AddOption(Options.MAX_LONG_SIZE)
+parser.AddOption("--source-role", metavar = "ROLE",
+        help = "enable this <role> [identified by <password>] in the source "
+               "database immediately after connecting by calling "
+               "dbms_session.set_role")
+parser.AddOption("--dest-role", metavar = "ROLE",
+        help = "enable this <role> [identified by <password>] in the target "
+               "database immediately after connecting by calling "
+               "dbms_session.set_role")
+cx_LoggingOptions.AddOptions(parser)
+parser.AddArgument("source", required = True,
+        help = "a select statement or the name of the table to query")
+parser.AddArgument("destination",
+        help = "the name of a table or view to perform the insert and update "
+               "statements against")
+options = parser.Parse()
+cx_LoggingOptions.ProcessOptions(options)
+
+# set up the source connection
+sourceConnection = cx_OracleUtils.Connect(options.sourceSchema,
+        options.sourceRole)
+sourceCursor = sourceConnection.cursor()
+if options.arraySize:
+    sourceCursor.arraysize = options.arraySize
+if options.maxLongSize:
+    sourceCursor.setoutputsize(options.maxLongSize)
+
+# set up the destination connection
+destConnection = cx_OracleUtils.Connect(options.destSchema, options.destRole)
+cursor = destConnection.cursor()
+
+# determine query to execute
+sourceSQL = options.source.strip()
+destinationTable = options.destination
+if " " not in sourceSQL:
+    if destinationTable is None:
+        destinationTable = sourceSQL
+    sourceInfo = cx_OracleUtils.GetObjectInfo(sourceConnection, sourceSQL)
+    if sourceInfo is None:
+        raise "Source table %s not found." % sourceSQL
+    sourceTableOwner, sourceTableName, sourceTableType = sourceInfo
+    sourceSQL = "select * from %s.%s" % \
+            (cx_OracleUtils.IdentifierRepr(sourceTableOwner),
+             cx_OracleUtils.IdentifierRepr(sourceTableName))
+if not destinationTable:
+    raise "A destination table must be specified when a source query " \
+          "is used."
+
+# verify the destination table exists
+destInfo = cx_OracleUtils.GetObjectInfo(destConnection, destinationTable)
+if destInfo is None:
+    raise "Destination table %s not found." % destinationTable
+destTableOwner, destTableName, destTableType = destInfo
+
+# determine columns in source query
+colPos = 0
+sourceColumns = {}
+definedVars = sourceCursor.execute(sourceSQL)
+for colName, colType, colDisplaySize, colInternalSize, colPrecision, \
+        colScale, colNullOk in sourceCursor.description:
+    sourceColumns[colName] = (colPos, colType)
+    colPos += 1
+
+# lookup columns on destination table
+cursor.execute("""
+      select
+        column_name,
+        nullable
+      from all_tab_columns
+      where owner = :p_Owner
+        and table_name = :p_Name""",
+      p_Owner = destTableOwner,
+      p_Name = destTableName)
+destColumns = {}
+for name, nullable in cursor.fetchall():
+    destColumns[name] = (nullable == "Y")
+
+# determine the list of key columns to use, if necessary
+keyColumns = []
+if options.checkExists:
+    if options.keyColumns:
+        keyColumns = options.keyColumns.upper().split(",")
+    else:
+        cursor.execute("""
+                select constraint_name
+                from all_constraints
+                where owner = :p_Owner
+                  and table_name = :p_Name
+                  and constraint_type in ('P', 'U')
+                order by constraint_type""",
+                p_Owner = destTableOwner,
+                p_Name = destTableName)
+        row = cursor.fetchone()
+        if not row:
+            raise "No primary or unique constraint found on table"
+        constraintName, = row
+        cursor.execute("""
+                select column_name
+                from all_cons_columns
+                where owner = :p_Owner
+                  and constraint_name = :p_Name""",
+                p_Owner = destTableOwner,
+                p_Name = constraintName)
+        keyColumns = [n for n, in cursor.fetchall()]
+    for name in keyColumns:
+        if name not in sourceColumns:
+            raise "Key column %s not in source query" % name
+
+# match the columns; all of the source or all of the destination columns must
+# match for a valid copy
+bindVariables = {}
+keyBindVariables = {}
+bindVariableXref = []
+destBindVariableXref = []
+for name in sourceColumns:
+    if name in destColumns:
+        colPos, colType = sourceColumns[name]
+        bindVarName = "p_Val_%d" % colPos
+        isLob = str(definedVars[colPos]).startswith("<Lob")
+        if options.checkExists or isLob:
+            bindVariables[bindVarName] = colType
+            bindVariableXref.append((colPos, bindVarName, isLob))
+        else:
+            bindVariables[bindVarName] = definedVars[colPos]
+        destBindVariableXref.append((name, ":%s" % bindVarName))
+        if options.checkExists and name in keyColumns:
+            keyBindVariables[bindVarName] = bindVariables[bindVarName]
+if len(bindVariables) not in (len(sourceColumns), len(destColumns)):
+    raise "All source columns or all destination columns must match by name"
+
+# set up insert cursor
+insertNames = [cx_OracleUtils.IdentifierRepr(n) \
+        for n, v in destBindVariableXref]
+insertValues = [v for n, v in destBindVariableXref]
+statement = "insert into %s.%s (%s) values (%s)" % \
+        (cx_OracleUtils.IdentifierRepr(destTableOwner),
+         cx_OracleUtils.IdentifierRepr(destTableName),
+         ",".join(insertNames), ",".join(insertValues))
+insertCursor = cursor
+insertCursor.bindarraysize = sourceCursor.arraysize
+insertCursor.prepare(statement)
+vars = insertCursor.setinputsizes(**bindVariables)
+insertVars = [(definedVars[p], vars[n], b) for p, n, b in bindVariableXref]
+
+# set up exists cursor
+if options.checkExists:
+    whereClauses = [cx_OracleUtils.WhereClause(n, v, destColumns[n], 1) \
+            for n, v in destBindVariableXref if n in keyColumns]
+    statement = "select count(1) from %s.%s where %s" % \
+            (cx_OracleUtils.IdentifierRepr(destTableOwner),
+             cx_OracleUtils.IdentifierRepr(destTableName),
+             " and ".join(whereClauses))
+    existsCursor = destConnection.cursor()
+    existsCursor.prepare(statement)
+    vars = existsCursor.setinputsizes(**keyBindVariables)
+    existsVars = [(definedVars[p], vars[n], b) \
+            for p, n, b in bindVariableXref if n in vars]
+
+# set up update cursor
+updateCursor = None
+if options.checkExists and len(keyColumns) != len(bindVariables):
+    setClauses = [cx_OracleUtils.IdentifierRepr(n) + " = " + v \
+            for n, v in destBindVariableXref if n not in keyColumns]
+    statement = "update %s.%s set %s where %s" % \
+            (cx_OracleUtils.IdentifierRepr(destTableOwner),
+             cx_OracleUtils.IdentifierRepr(destTableName),
+             ",".join(setClauses), " and ".join(whereClauses))
+    if options.checkModified:
+        additionalWhereClauses = \
+                [cx_OracleUtils.WhereClause(n, v, destColumns[n], 0) \
+                for n, v in destBindVariableXref if n not in keyColumns]
+        statement += " and (%s)" % " or ".join(additionalWhereClauses)
+    updateCursor = destConnection.cursor()
+    updateCursor.bindarraysize = sourceCursor.arraysize
+    updateCursor.prepare(statement)
+    vars = updateCursor.setinputsizes(**bindVariables)
+    updateVars = [(definedVars[p], vars[n], b) for p, n, b in bindVariableXref]
+
+# tell user what is happening
+cx_Logging.Trace("Copying data...")
+cx_Logging.Trace("  Source: %s", sourceSQL)
+cx_Logging.Trace("  Destination: %s", destinationTable)
+
+# skip rows that are not of interest
+while options.skip:
+    cx_Logging.Trace("  Rows left to skip: %s", options.skip)
+    rowsToFetch = min(sourceCursor.arraysize, options.skip)
+    options.skip -= sourceCursor.fetchraw(rowsToFetch)
+
+# initialize counters used in performing the copy
+insertedRows = 0
+modifiedRows = 0
+unmodifiedRows = 0
+insertPos = 0
+updatePos = 0
+lastCommitted = 0
+lastReported = 0
+iter = range(sourceCursor.arraysize)
+reportPoint = options.reportPoint
+commitPoint = options.commitPoint
+if reportPoint is None and commitPoint is not None:
+    reportPoint = commitPoint
+
+# perform the copy
+while True:
+    rowsFetched = sourceCursor.fetchraw()
+    if not rowsFetched:
+        break
+    if not insertVars:
+        insertPos = rowsFetched
+    else:
+        if rowsFetched != sourceCursor.arraysize:
+            iter = range(rowsFetched)
+        for pos in iter:
+            exists = 0
+            if options.checkExists:
+                for definedVar, boundVar, isLob in existsVars:
+                    boundVar.copy(definedVar, pos, 0)
+                vars = existsCursor.execute(None)
+                existsCursor.fetchraw()
+                exists = vars[0].getvalue()
+            if not exists:
+                targetPos = insertPos
+                targetVars = insertVars
+                insertPos += 1
+            elif updateCursor:
+                targetPos = updatePos
+                targetVars = updateVars
+                updatePos += 1
+            else:
+                unmodifiedRows += 1
+                targetVars = []
+            for definedVar, boundVar, isLob in targetVars:
+                if isLob:
+                    boundVar.setvalue(targetPos,
+                           definedVar.getvalue(pos).read())
+                else:
+                    boundVar.copy(definedVar, pos, targetPos)
+    if insertPos:
+        insertCursor.executemanyprepared(insertPos)
+        insertedRows += insertPos
+        insertPos = 0
+    if updatePos:
+        updateCursor.executemanyprepared(updatePos)
+        modifiedRows += updateCursor.rowcount
+        unmodifiedRows += (updatePos - updateCursor.rowcount)
+        updatePos = 0
+    if reportPoint and sourceCursor.rowcount - lastReported >= reportPoint:
+        lastReported = sourceCursor.rowcount
+        cx_Logging.Trace("  %s rows processed", sourceCursor.rowcount)
+    if commitPoint and sourceCursor.rowcount - lastCommitted >= commitPoint:
+        lastCommitted = sourceCursor.rowcount
+        destConnection.commit()
+destConnection.commit()
+
+# print out final statistics
+cx_Logging.Trace("%s rows retrieved from source.", sourceCursor.rowcount)
+cx_Logging.Trace("%s rows created in destination.", insertedRows)
+cx_Logging.Trace("%s rows modified in destination.", modifiedRows)
+cx_Logging.Trace("%s rows unmodified in destination.", unmodifiedRows)
+
+"""Display output from a different Oracle process."""
+
+import cx_Logging
+import cx_LoggingOptions
+import cx_OptionParser
+import cx_OracleDebugger
+import cx_OracleUtils
+
+# parse command line
+parser = cx_OptionParser.OptionParser("DbDebugger")
+parser.AddOption("--pipe-name", default = "DbDebugger",
+        metavar = "NAME", prompt = "Pipe name",
+        help = "use this pipe name for communication with the database")
+parser.AddOption(cx_OracleUtils.SchemaOption())
+cx_LoggingOptions.AddOptions(parser)
+options = parser.Parse()
+cx_LoggingOptions.ProcessOptions(options)
+
+# connect to the database
+connection = cx_OracleUtils.Connect(options.schema)
+cx_Logging.Trace("Connected to %s@%s", connection.username,
+        connection.tnsentry)
+cx_Logging.Trace("Listening on pipe %s...", options.pipeName)
+
+# display the messages
+for message in cx_OracleDebugger.MessageGenerator(connection,
+        options.pipeName):
+    cx_Logging.Trace("%s", message)
+

DescribeObject.py

+"""Describes objects in a database in a way suitable for creating the object
+in a database."""
+
+import cx_LoggingOptions
+import cx_OptionParser
+import cx_OracleObject
+import cx_OracleUtils
+import sys
+
+import Options
+
+# parse command line
+parser = cx_OptionParser.OptionParser("DescribeObject")
+parser.AddOption(cx_OracleUtils.SchemaOption())
+parser.AddOption(Options.NO_COMMENTS)
+parser.AddOption(Options.NO_GRANTS)
+parser.AddOption(Options.NO_RELATED)
+parser.AddOption(Options.NO_TRIGGERS)
+parser.AddOption(Options.DONT_MERGE_GRANTS)
+parser.AddOption(Options.DEFAULT_TABLESPACE)
+parser.AddOption(Options.DEFAULT_STORAGE)
+parser.AddOption(Options.SHOW_FOREIGN_KEYS)
+parser.AddOption(Options.INCLUDE_SEQUENCE_VALUES)
+parser.AddOption(Options.USE_DBA_VIEWS)
+parser.AddOption(Options.MAX_LONG_SIZE)
+cx_LoggingOptions.AddOptions(parser)
+parser.AddArgument("objectName", required = True,
+        help = "the name of the object to describe")
+parser.AddArgument("fileName",
+        help = "the name of the file to populate with the statements or '-'"
+               "to output the statements to stdout; if a file name is not "
+               "specified, the statements will also be output to stdout")
+options = parser.Parse()
+cx_LoggingOptions.ProcessOptions(options)
+
+# set up describe environment
+connection = cx_OracleUtils.Connect(options.schema)
+environment = cx_OracleObject.Environment(connection, options)
+
+# open the file
+if options.fileName is None or options.fileName == "-":
+    outFile = sys.stdout
+else:
+    outFile = file(options.fileName, "w")
+describer = cx_OracleObject.Describer(environment, options, outFile)
+
+# determine the object and its owner
+objectName = options.objectName
+isFullyQualified = "." in objectName
+if isFullyQualified:
+    objectOwner, objectName = objectName.split(".")
+else:
+    objectOwner = connection.username.upper()
+
+# determine the type of object
+objectType = cx_OracleObject.ObjectType(environment, objectOwner, objectName)
+if objectType is None:
+    objectOwner = objectOwner.upper()
+    objectName = objectName.upper()
+    objectType = cx_OracleObject.ObjectType(environment, objectOwner,
+            objectName)
+if objectType is None and not isFullyQualified:
+    cursor = connection.cursor()
+    cursor.execute("""
+            select
+              table_owner,
+              table_name
+            from %s_synonyms
+            where owner = 'PUBLIC'
+              and synonym_name = :objectName""" % environment.ViewPrefix(),
+            objectName = objectName)
+    row = cursor.fetchone()
+    if row is not None:
+        objectOwner, objectName = row
+        objectType = cx_OracleObject.ObjectType(environment, objectOwner,
+                objectName)
+if objectType is None:
+    raise "Object %s.%s does not exist." % (objectOwner, objectName)
+
+# perform the actual describe
+describer.schemas = [objectOwner]
+describer.RetrieveAndExportObject(objectOwner, objectName, objectType)
+

DescribeSchema.py

+"""Generate a script which can be used to recreate all of the objects in an
+Oracle schema in the order necessary to avoid errors."""
+
+import cx_LoggingOptions
+import cx_OptionParser
+import cx_OracleObject
+import cx_OracleUtils
+import sys
+
+import Options
+
+# parse command line
+parser = cx_OptionParser.OptionParser("DescribeSchema")
+parser.AddOption(cx_OracleUtils.SchemaOption())
+parser.AddOption(Options.NO_COMMENTS)
+parser.AddOption(Options.NO_GRANTS)
+parser.AddOption(Options.DONT_MERGE_GRANTS)
+parser.AddOption(Options.DEFAULT_TABLESPACE)
+parser.AddOption(Options.DEFAULT_STORAGE)
+parser.AddOption(Options.INCLUDE_SEQUENCE_VALUES)
+parser.AddOption(Options.NAME_ONLY)
+parser.AddOption(Options.NAMES)
+parser.AddOption(Options.ONLY_TYPES)
+parser.AddOption(Options.ONLY_IF)
+parser.AddOption(Options.USE_DBA_VIEWS)
+parser.AddOption(Options.INCLUDE_ROLES)
+parser.AddOption(Options.INCLUDE_USERS)
+parser.AddOption(Options.MAX_LONG_SIZE)
+cx_LoggingOptions.AddOptions(parser)
+parser.AddArgument("fileName",
+        help = "the name of the file to populate with the statements or '-'"
+               "to output the statements to stdout; if a file name is not "
+               "specified, the statements will also be output to stdout")
+options = parser.Parse()
+cx_LoggingOptions.ProcessOptions(options)
+
+# set up describe environment
+connection = cx_OracleUtils.Connect(options.schema)
+environment = cx_OracleObject.Environment(connection, options)
+
+# open the file
+if options.fileName is None or options.fileName == "-":
+    outFile = sys.stdout
+else:
+    outFile = file(options.fileName, "w")
+describer = cx_OracleObject.Describer(environment, options, outFile)
+
+# describe the schema(s)
+describer.ExportAllObjects()
+
+"""Dump the results of a SQL select statement to a file in CSV format."""
+
+import cx_LoggingOptions
+import cx_OptionParser
+import cx_OracleUtils
+import sys
+
+# parse command line
+parser = cx_OptionParser.OptionParser("DumpCSV")
+parser.AddOption("--record-sep", default = "\n", metavar = "CHAR",
+        help = "record separator to use")
+parser.AddOption("--field-sep", default = ",", metavar = "CHAR",
+        help = "field separator to use")
+parser.AddOption("--string-encloser", default = '"', metavar = "CHAR",
+        help = "character to use for enclosing strings")
+parser.AddOption("--escape-char", default = "\\", metavar = "CHAR",
+        help = "character to use for escaping the string encloser")
+parser.AddOption("--report-point", type = "int", metavar = "N",
+        help = "report point is <n> rows")
+parser.AddOption("--sql-in-file", action = "store_true",
+        help = "SQL parameter is actually a file name in which the SQL is "
+               "found")
+cx_LoggingOptions.AddOptions(parser)
+parser.AddArgument("connectString", required = 1,
+        help = "the string to use for connecting to the database")
+parser.AddArgument("sql", required = 1,
+        help = "the SQL to execute or the name of a file in which the SQL "
+               "is found if the --sql-in-file option is used")
+parser.AddArgument("fileName", required = 1,
+        help = "the name of the file in which to place the output")
+options = parser.Parse()
+cx_LoggingOptions.ProcessOptions(options)
+
+# connect to database
+connection = cx_OracleUtils.Connect(options.connectString)
+cursor = connection.cursor()
+cursor.arraysize = 50
+
+# open output file
+outFile = file(options.fileName, "w")
+
+# determine SQL and execute it
+sql = options.sql
+if options.sqlInFile:
+    sql = file(sql).read()
+cursor.execute(sql)
+
+# define function to return an evaluated string (to support tabs, newlines)
+def EvalString(value):
+    return value.replace("\\t", "\t").replace("\\n", "\n")
+
+# define function to return a string representation of each type
+def StringRep(value):
+    if value is None:
+        return ""
+    elif isinstance(value, str):
+        if not gStringEncloser:
+            return value
+        return gStringEncloser + value.replace(gStringEncloser, \
+                gEscapeCharacter + gStringEncloser) + gStringEncloser
+    return str(value)
+
+# dump the results to the output file
+gFieldSeparator = EvalString(options.fieldSep)
+gRecordSeparator = EvalString(options.recordSep)
+gStringEncloser = EvalString(options.stringEncloser)
+gEscapeCharacter = EvalString(options.escapeChar)
+while True:
+
+    # fetch a single row
+    row = cursor.fetchone()
+    if not row:
+        break
+
+    # dump the row
+    outFile.write(gFieldSeparator.join([StringRep(v) for v in row]))
+    outFile.write(gRecordSeparator)
+
+    # report the number of rows dumped, if desired
+    if options.reportPoint and cursor.rowcount % options.reportPoint == 0:
+        print >> sys.stderr, " ", cursor.rowcount, "rows dumped."
+
+# report the total number of rows dumped
+if not options.reportPoint or cursor.rowcount == 0 or \
+        cursor.rowcount % options.reportPoint != 0:
+    print >> sys.stderr, " ", cursor.rowcount, "rows dumped."
+print >> sys.stderr, "Done."
+
+"""Dump data as SQL statements given a SQL query to execute."""
+
+import cx_LoggingOptions
+import cx_OptionParser
+import cx_OracleUtils
+import datetime
+import os
+import sys
+
+# parse command line
+parser = cx_OptionParser.OptionParser("DumpData")
+parser.AddOption(cx_OracleUtils.SchemaOption())
+parser.AddOption("--sort-by", metavar = "STR",
+        help = "append an order by clause with this value to the query")
+parser.AddOption("--source-query", metavar = "STR",
+        help = "use this query instead of ""select * from <TableName>""")
+cx_LoggingOptions.AddOptions(parser)
+parser.AddArgument("tableName", required = 1,
+        help = "the name of the table to use as the target for the insert "
+               "statements that are generated")
+options = parser.Parse()
+cx_LoggingOptions.ProcessOptions(options)
+
+# perform the work of dumping the data out of the given table
+connection = cx_OracleUtils.Connect(options.schema)
+query = options.sourceQuery
+if not query:
+    query = "select * from " + options.tableName
+    if options.sortBy:
+        query += " order by " + options.sortBy
+cursor = connection.cursor()
+cursor.execute(query)
+description = cursor.description
+format = "insert into %s (\n  %s\n) values (\n  %s\n);\n"
+sequence = range(len(description))
+for row in cursor:
+    names = []
+    values = []
+    for i in sequence:
+        value = row[i]
+        if value is None:
+            continue
+        names.append(description[i][0])
+        if description[i][1] == connection.DATETIME \
+                and not isinstance(value, datetime.datetime):
+            value = datetime.datetime(value.year, value.month, value.day,
+                    value.hour, value.minute, value.second)
+        values.append(cx_OracleUtils.GetConstantRepr(value))
+    if names:
+        sql = "insert into %s (\n  %s\n) values (\n  %s\n);\n" % \
+                (options.tableName, ",\n  ".join(names), ",\n  ".join(values))
+        print sql
+
+"""Export data from a CLOB or BLOB column in a database table to a file."""
+
+import cx_LoggingOptions
+import cx_OptionParser
+import cx_OracleUtils
+import cx_Oracle
+import sys
+
+import Options
+
+# parse command line
+parser = cx_OptionParser.OptionParser("ExportColumn")
+parser.AddOption(cx_OracleUtils.SchemaOption())
+parser.AddOption(Options.BINARY)
+parser.AddOption(Options.STATEMENT_IN_FILE)
+cx_LoggingOptions.AddOptions(parser)
+parser.AddArgument("statement", required = True,
+        help = "the statement to execute OR the name of the file containing "
+               "the statement to execute if the --statement-in-file option "
+               "is specified OR the name of the column in which to import "
+               "the contents of the file in the form [Owner.]Table.Column "
+               "in which case a select statement will be fabricated with the "
+               "values argument making up the where clause")
+parser.AddArgument("fileName", required = True,
+        help = "the name of the file to write the data that is to be exported")
+parser.AddArgument("values", keywords = True,
+        help = "any number of values of the form name=value which will be "
+               "turned into bind variables which will be bound to the "
+               "statement that is to be executed")
+options = parser.Parse()
+cx_LoggingOptions.ProcessOptions(options)
+
+# connect to the database
+connection = cx_OracleUtils.Connect(options.schema)
+cursor = connection.cursor()
+ 
+# verify options
+if options.binary:
+    mode = "wb"
+else:
+    mode = "w"
+if options.statementInFile:
+    options.statement = file(options.statement).read().strip()
+options.isColumn = " " not in options.statement
+if not options.isColumn:
+    statement = options.statement
+else:
+    parts = options.statement.upper().split(".")
+    if len(parts) < 2 or len(parts) > 3:
+        raise "Column name must be of the form [Owner.]Table.Column"
+    if len(parts) == 2:
+        owner = connection.username.upper()
+        table, column = parts
+    else:
+        owner, table, column = parts
+    clauses = ["%s = :%s" % (n, n) for n in options.values]
+    whereClause = ""
+    if clauses:
+        whereClause = " where " + " and ".join(clauses)
+    statement = "select %s from %s.%s%s" % (column, owner, table, whereClause)
+
+# execute the statement and retrieve the data
+cursor.execute(statement, **options.values)
+row = cursor.fetchone()
+if row is None:
+    raise "Row not found."
+clob, = row
+file(options.fileName, mode).write(clob.read())
+print >> sys.stderr, "Column successfully exported."
+
+"""Export data from a set of tables in a format suitable for importing to any
+Oracle database on any platform."""
+
+import cStringIO
+import cx_ExportData
+import cx_LoggingOptions
+import cx_OptionParser
+import cx_OracleUtils
+import os
+import sys
+
+import Options
+
+# parse command line
+parser = cx_OptionParser.OptionParser("ExportData")
+parser.AddOption(cx_OracleUtils.SchemaOption())
+parser.AddOption(Options.ARRAY_SIZE)
+parser.AddOption(Options.REPORT_POINT)
+parser.AddOption(Options.MAX_LONG_SIZE)
+parser.AddOption(Options.SET_ROLE)
+parser.AddOption("--include-schema-name", action = "store_true",
+        help = "include schema name in the exported table list")
+parser.AddOption("--include-tables", action = "append", metavar = "LIST",
+        help = "name of tables to include in the export")
+parser.AddOption("--exclude-tables", action = "append", metavar = "LIST",
+        help = "name of tables to exclude from the export")
+parser.AddOption("--skip", metavar = "N", type = "int",
+        help = "number of rows to skip before starting the export; note that "
+               "this option is only really useful when exporting only one "
+               "table")
+parser.AddOption("--row-limit", metavar = "N", type = "int",
+        help = "number of rows at which the export will stop; note that this "
+               "option is only really useful when exporting only one table")
+cx_LoggingOptions.AddOptions(parser)
+parser.AddArgument("fileName", required = True,
+        help = "the name of the file in which to place the exported data or "
+               "'-' to output the exported data to stdout")
+options = parser.Parse()
+cx_LoggingOptions.ProcessOptions(options)
+
+# connect to the database
+connection = cx_OracleUtils.Connect(options.schema, options.setRole)
+cursor = connection.cursor()
+if options.arraySize:
+    cursor.arraysize = options.arraySize
+if options.maxLongSize:
+    cursor.setoutputsize(options.maxLongSize)
+
+# open the file for the export
+if options.fileName == "-":
+  outFile = sys.stdout
+else:
+  outFile = file(options.fileName, "wb")
+
+# retrieve the set of tables that will make up the export
+tables = []
+exporter = cx_ExportData.Exporter(outFile, cursor, options.reportPoint)
+if options.includeTables:
+    tables = [s.upper() for v in options.includeTables for s in v.split(",")]
+else:
+    excludeTables = []
+    if options.excludeTables:
+        excludeTables = [s.upper() for v in options.excludeTables
+                for s in v.split(",")]
+    tables = [n for n in exporter.TablesInSchema() if n not in excludeTables]
+if options.includeSchemaName:
+    tables = ["%s.%s" % (connection.username, n) for n in tables]
+
+# perform the export
+for table in tables:
+    exporter.ExportTable(table, options.skip, options.rowLimit)
+exporter.FinalizeExport()
+
+"""Export all of the objects in a schema as a set of directories named after
+the type of object containing a set of files named after the object itself."""
+
+import cx_LoggingOptions
+import cx_OptionParser
+import cx_OracleObject
+import cx_OracleUtils
+
+import Options
+
+# parse command line
+parser = cx_OptionParser.OptionParser("ExportObjects")
+parser.AddOption(cx_OracleUtils.SchemaOption())
+parser.AddOption(Options.NO_COMMENTS)
+parser.AddOption(Options.NO_GRANTS)
+parser.AddOption(Options.NO_RELATED)
+parser.AddOption(Options.NO_TRIGGERS)
+parser.AddOption(Options.DONT_MERGE_GRANTS)
+parser.AddOption(Options.DEFAULT_TABLESPACE)
+parser.AddOption(Options.DEFAULT_STORAGE)
+parser.AddOption(Options.USE_DBA_VIEWS)
+parser.AddOption(Options.INCLUDE_SEQUENCE_VALUES)
+parser.AddOption(Options.INCLUDE_ROLES)
+parser.AddOption(Options.INCLUDE_USERS)
+parser.AddOption(Options.SPLIT_RELATED)
+parser.AddOption(Options.NAMES)
+parser.AddOption(Options.ONLY_TYPES)
+parser.AddOption(Options.ONLY_IF)
+parser.AddOption(Options.MAX_LONG_SIZE)
+parser.AddOption("--base-dir", default = ".", metavar = "DIR",
+        help = "base directory in which to place exported objects")
+parser.AddOption("--suppress-owner-dir",
+        default = False,
+        action = "store_true",
+        help = "suppress the creation of the owner directory")
+cx_LoggingOptions.AddOptions(parser)
+options = parser.Parse()
+cx_LoggingOptions.ProcessOptions(options)
+
+# set up describe environment
+connection = cx_OracleUtils.Connect(options.schema)
+environment = cx_OracleObject.Environment(connection, options)
+exporter = cx_OracleObject.Exporter(environment, options, options.baseDir)
+
+# perform the work of exporting the objects
+exporter.ExportAllObjects()
+
+"""Export data from a table in an Oracle database to an XML file."""
+
+import cx_LoggingOptions
+import cx_OptionParser
+import cx_Oracle
+import cx_OracleUtils
+import cx_XML
+import datetime
+import sys
+
+import Options
+
+# parse command line
+parser = cx_OptionParser.OptionParser("ExportXML")
+parser.AddOption(cx_OracleUtils.SchemaOption())
+parser.AddOption(Options.ARRAY_SIZE)
+parser.AddOption(Options.REPORT_POINT)
+parser.AddOption(Options.SET_ROLE)
+parser.AddOption(Options.DATE_FORMAT)
+cx_LoggingOptions.AddOptions(parser)
+parser.AddArgument("fileName", required = True,
+        help = "the name of the file to which to write the data or "
+               "'-' to write the data to stdout")
+parser.AddArgument("tableName", required = True,
+        help = "the name of the table from which to export the data")
+options = parser.Parse()
+cx_LoggingOptions.ProcessOptions(options)
+
+# connect to the database
+connection = cx_OracleUtils.Connect(options.schema, options.setRole)
+cursor = connection.cursor()
+if options.arraySize is not None:
+    cursor.arraysize = options.arraySize
+cursor.execute("select * from %s" % options.tableName)
+names = [item[0] for item in cursor.description]
+
+# write the data to the XML file
+if options.fileName == "-":
+    outputFile = sys.stdout
+else:
+    outputFile = file(options.fileName, "w")
+writer = cx_XML.Writer(outputFile, numSpaces = 4)
+writer.StartTag("ROWSET")
+for row in cursor:
+    writer.StartTag("ROW", num = cursor.rowcount)
+    for name, value in zip(names, row):
+        if value is None:
+            continue
+        if isinstance(value, cx_Oracle.DATETIME):
+            dateValue = datetime.datetime(value.year, value.month, value.day,
+                    value.hour, value.minute, value.second)
+            value = dateValue.strftime(options.dateFormat)
+        else:
+            value = str(value)
+        writer.WriteTagWithValue(name, value)
+    writer.EndTag()
+    if options.reportPoint is not None \
+            and cursor.rowcount % options.reportPoint == 0:
+        print >> sys.stderr, cursor.rowcount, "rows exported."
+writer.EndTag()
+
+if options.reportPoint is None or cursor.rowcount % options.reportPoint != 0:
+    print >> sys.stderr, cursor.rowcount, "rows exported."
+print >> sys.stderr, "Done."
+
+"""Generates a patch for differences in objects in two directories (which may
+have been created with ExportObjects) and ensures that the patch script can
+be executed without fear of encountering errors because of dependencies between
+objects."""
+
+import cx_LoggingOptions
+import cx_OptionParser
+import cx_OracleObject
+import cx_OracleUtils
+import cx_SQL
+import cx_Utils
+import os
+import sys
+
+import Options
+
+# parse command line
+parser = cx_OptionParser.OptionParser("GeneratePatch")
+parser.AddOption(cx_OracleUtils.SchemaOption())
+parser.AddOption(Options.NO_COMMENTS)
+parser.AddOption(Options.NO_GRANTS)
+parser.AddOption(Options.USE_DBA_VIEWS)
+cx_LoggingOptions.AddOptions(parser)
+parser.AddArgument("fromDir", required = 1,
+        help = "the directory containing the source objects")
+parser.AddArgument("toDir", required = 1,
+        help = "the directory containing the target objects or the objects "
+               "which the source objects will be transformed into using the "
+               "generated script")
+options = parser.Parse()
+cx_LoggingOptions.ProcessOptions(options)
+if not os.path.exists(options.fromDir):
+    raise "Source (from directory) not found."
+if not os.path.exists(options.toDir):
+    raise "Target (to directory) not found."
+
+# set up describe environment
+connection = cx_OracleUtils.Connect(options.schema)
+environment = cx_OracleObject.Environment(connection, options)
+describer = cx_OracleObject.Describer(environment, options)
+wantGrants = describer.wantGrants
+
+# define some constants
+CONSTRAINT_TYPES = ["PRIMARY KEY", "UNIQUE CONSTRAINT", "FOREIGN KEY",
+    "CHECK CONSTRAINT"]
+
+# define a function which will return the list of objects in the files
+def ObjectsInFiles(files, baseDir):
+    objs = {}
+    for file_ in files:
+        dir, objName = os.path.split(file_[:-4].upper())
+        dir, objType = os.path.split(dir)
+        dir, objOwner = os.path.split(dir)
+        baseName = os.path.join(objOwner, objType, objName + ".sql").lower()
+        file_ = os.path.join(baseDir, baseName)
+        for statement in cx_SQL.ParseStatementsInFile(file_):
+            if isinstance(statement, cx_SQL.AlterStatement):
+                statement.GetConstraintTypeAndName()
+            if isinstance(statement, cx_SQL.CreateStatement) \
+                    or isinstance(statement, cx_SQL.AlterStatement):
+                objType = statement.ObjectType().upper()
+                objName = statement.ObjectName().upper()
+                objs[(objOwner, objName, objType)] = baseName
+    return objs
+
+# define a function which will return the statements in a given file
+def Statements(baseDir, baseName, objType, objName):
+    found = 0
+    statements = []
+    fileName = os.path.join(baseDir, baseName)
+    for statement in cx_SQL.ParseStatementsInFile(fileName):
+        if isinstance(statement, cx_SQL.CreateStatement) \
+                or isinstance(statement, cx_SQL.AlterStatement):
+            if found:
+                break
+            if isinstance(statement, cx_SQL.AlterStatement):
+                statement.GetConstraintTypeAndName()
+            found = (statement.ObjectType().upper() == objType
+                    and statement.ObjectName().upper() == objName)
+        if found:
+            statements.append(statement)
+    return statements
+
+# define a function to return a dependency
+def DependsOn(statement, objOwner, objName, objType):
+    if objType == "PACKAGE BODY":
+        return (objOwner, objName, "PACKAGE")
+    elif objType == "TYPE BODY":
+        return (objOwner, objName, "TYPE")
+    else:
+        words = statement.SQL().lower().split()
+        if objType == "INDEX":
+            return (objOwner, words[4].upper(), "TABLE")
+        elif objType in ("UNIQUE INDEX", "BITMAP INDEX"):
+            return (objOwner, words[5].upper(), "TABLE")
+        elif objType in CONSTRAINT_TYPES:
+            return (objOwner, words[2].upper(), "TABLE")
+        elif objType == "TRIGGER":
+            index = words.index("on")
+            return (objOwner, words[index].upper(), "TABLE")
+
+# output drop statement for the given object
+def OutputDropStatement(statement, objsToDrop, objOwner, objName, objType,
+        hardDrop):
+    dependsOn = DependsOn(statement[0], objOwner, objName, objType)
+    if dependsOn:
+        refOwner, refName, refType = dependsOn
+        if objsToDrop.has_key((refOwner, refName, refType)):
+            return
+    if objType in ("UNIQUE INDEX", "BITMAP INDEX"):
+        objType = "INDEX"
+    elif objType in CONSTRAINT_TYPES:
+        objType = "CONSTRAINT"
+    describer.SetOwner(objOwner, objType)
+    if not hardDrop and objType == "TABLE":
+        print "--",
+    if objType in CONSTRAINT_TYPES:
+        print "alter table", refName.lower()
+    print "drop", objType.lower(), objName.lower() + ";"
+    print
+
+# define a function which will return the grants in a set of statements
+def ParseGrants(statements):
+    grants = {}
+    for statement in statements:
+        statement = statement.SQL().replace("\n", " ").strip()[:-1]
+        withGrantOption = ""
+        if statement.endswith("with grant option"):
+            withGrantOption = statement[-18:]
+            statement = statement[:-18]
+        pos = statement.index(" on ")
+        privileges = [s.strip() for s in statement[6:pos].split(",")]
+        pos = statement.index(" to ")
+        grantees = [s.strip() for s in statement[pos + 4:].split(",")]
+        for privilege in privileges:
+            for grantee in grantees:
+                grants[(privilege, grantee)] = withGrantOption
+    return grants
+
+# acquire the list of objects that have changed
+print >> sys.stderr, "Acquiring differences..."
+newFiles, modifiedFiles, removedFiles = \
+        cx_Utils.PerformDiff(options.fromDir, options.toDir)
+
+# determine the owner, name and type of object in each file that is of interest
+oldObjs = ObjectsInFiles(modifiedFiles + removedFiles, options.fromDir)
+newObjs = ObjectsInFiles(modifiedFiles + newFiles, options.toDir)
+
+# sort the objects into three arrays for simpler processing
+preSourceObjs = [(o, n, t) for o, n, t in newObjs \
+        if t != "TRIGGER" and t not in cx_OracleObject.SOURCE_TYPES]
+sourceObjs = [(o, n, t) for o, n, t in newObjs \
+        if t in cx_OracleObject.SOURCE_TYPES]
+postSourceObjs = [(o, n, t) for o, n, t in newObjs if t == "TRIGGER"]
+postSourceObjs.sort()
+
+# order the pre source objects by acquiring dependencies
+if preSourceObjs:
+
+    # acquire the dependencies
+    dependencies = []
+    for obj in preSourceObjs:
+        objOwner, objName, objType = obj
+        statements = Statements(options.toDir, newObjs[obj], objType, objName)
+        dependsOn = DependsOn(statements[0], objOwner, objName, objType)
+        if dependsOn:
+            refOwner, refName, refType = dependsOn
+            dependencies.append((objOwner, objName, objType, refOwner, refName,
+                    refType))
+
+    # now order them
+    print >> sys.stderr, "Ordering pre source objects..."
+    preSourceObjs = cx_OracleObject.OrderObjects(preSourceObjs, dependencies)
+
+# order the source objects by retrieving dependencies
+if sourceObjs:
+
+    # determine the list of schemas that are affected
+    currentOwner = describer.currentOwner
+    schemas = {}
+    for objOwner, name, objType in sourceObjs:
+        schemas[objOwner] = None
+    describer.schemas = schemas.keys()
+    describer.currentOwner = currentOwner
+
+    # acquire the list of dependencies from the database
+    print >> sys.stderr, "Acquiring dependencies..."
+    dependencies = describer.RetrieveDependencies()
+
+    # now order them
+    print >> sys.stderr, "Ordering source objects..."
+    sourceObjs = cx_OracleObject.OrderObjects(sourceObjs, dependencies)
+
+# perform the drops
+objsToDrop = {}
+for obj in oldObjs:
+    if obj not in newObjs:
+        objsToDrop[obj] = None
+if objsToDrop:
+    print >> sys.stderr, "Dropping unused objects..."
+    print "-- dropping unused objects"
+    print
+    objs = objsToDrop.keys()
+    objs.sort()
+    for obj in objs:
+        objOwner, objName, objType = obj
+        statements = Statements(options.fromDir, oldObjs[obj], objType,
+                objName)
+        OutputDropStatement(statements, objsToDrop, objOwner, objName, objType,
+                True)
+
+# output the statements for all of the objects
+print >> sys.stderr, "Generating patch for new and modified objects..."
+for obj in preSourceObjs + sourceObjs + postSourceObjs:
+
+    # initialization
+    objOwner, objName, objType = obj
+    outputType = objType.lower()
+    outputName = objName.lower()
+    existed = oldObjs.has_key(obj)
+
+    # retrieve the statements in the files
+    newStatements = Statements(options.toDir, newObjs[obj], objType,
+            objName)
+    if existed:
+        oldStatements = Statements(options.fromDir, oldObjs[obj], objType,
+                objName)
+
+    # handle the case where the object is new or is a trigger
+    if not existed or objType == "TRIGGER":
+      describer.SetOwner(objOwner, objType)
+      if existed:
+          print "-- modifying",
+      else:
+          print "-- creating",
+      print outputType, outputName
+      print
+      for statement in newStatements:
+          if wantGrants or not isinstance(statement, cx_SQL.GrantStatement):
+              statement.Write(sys.stdout)
+
+    # otherwise, perform comparisons and output applicable code to make changes
+    else:
+
+        # compare main object
+        if newStatements[0].SQL() != oldStatements[0].SQL():
+          describer.SetOwner(objOwner, objType)
+          print "-- modifying", outputType, outputName
+          print
+          if objType not in cx_OracleObject.SOURCE_TYPES:
+              OutputDropStatement(oldStatements, objsToDrop, objOwner,
+                      objName, objType, False)
+          newStatements[0].Write(sys.stdout)
+
+        # acquire the grants
+        if not wantGrants:
+            continue
+        newGrants = ParseGrants(newStatements[1:])
+        oldGrants = ParseGrants(oldStatements[1:])
+
+        # determine privileges to be revoked
+        revokes = [r for r in oldGrants if r not in newGrants]
+
+        # determine the privileges to be granted
+        grants = []
+        for row, withGrantOption in newGrants.items():
+            origGrantOption = oldGrants.get(row)
+            if withGrantOption != origGrantOption:
+                grants.append((row[0], row[1], withGrantOption))
+                if origGrantOption == " with grant option":
+                    revokes.append(row)
+
+        # output the privilege changes
+        if grants or revokes:
+            describer.SetOwner(objOwner, objType)
+            print "-- modifying grants for", outputType, outputName
+            print
+            revokes.sort()
+            for privilege, grantee in revokes:
+                print "revoke", privilege, "on", outputName, "from",
+                print grantee + ";"
+            grants.sort()
+            for privilege, grantee, withGrantOption in grants:
+                print "grant", privilege, "on", outputName, "to",
+                print grantee + withGrantOption + ";"
+            print
+
+"""Generate views based on tables in a database."""
+
+import cx_LoggingOptions
+import cx_OptionParser
+import cx_Oracle
+import cx_OracleObject
+import cx_OracleUtils
+import os
+import sys
+
+# parse command line
+parser = cx_OptionParser.OptionParser("GenerateView")
+parser.AddOption(cx_OracleUtils.SchemaOption())
+parser.AddOption("--add-prefix", metavar = "STR",
+        help = "add prefix to name of table to create view")
+parser.AddOption("--remove-prefix", metavar = "STR",
+        help = "remove prefix from name of table to create view")
+parser.AddOption("--add-suffix", metavar = "STR",
+        help = "add suffix to name of table to create view")
+parser.AddOption("--remove-suffix", metavar = "STR",
+        help = "remove suffix from name of table to create view")
+parser.AddOption("--include-tables", action = "append", metavar = "LIST",
+        help = "name of tables to include in the export")
+parser.AddOption("--exclude-tables", action = "append", metavar = "LIST",
+        help = "name of tables to exclude from the export")
+cx_LoggingOptions.AddOptions(parser)
+options = parser.Parse()
+cx_LoggingOptions.ProcessOptions(options)
+
+# connect to the database
+connection = cx_OracleUtils.Connect(options.schema)
+cursor = connection.cursor()
+
+# determine the list of tables to generate the views for
+tables = []
+if options.includeTables:
+    tables = [s.upper() for v in options.includeTables for s in v.split(",")]
+else:
+    excludeTables = []
+    if options.excludeTables:
+        excludeTables = [s.upper() for v in options.excludeTables
+                for s in v.split(",")]
+    cursor.execute("select table_name from user_tables order by table_name")
+    tables = [n for n, in cursor.fetchall() if n not in excludeTables]
+
+# prepare the cursor for retrieving the columns for the table
+cursor.prepare("""
+        select column_name
+        from user_tab_columns
+        where table_name = :p_TableName
+        order by column_id""")
+cursor.setinputsizes(p_TableName = cx_Oracle.STRING)
+
+# output the view syntax for each table
+for tableName in tables:
+    print >> sys.stderr, "Generating view for table", tableName + "..."
+    fromClause = "from %s;" % cx_OracleObject.NameForOutput(tableName)
+    cursor.execute(None, p_TableName = tableName)
+    columnNames = ["  " + cx_OracleObject.NameForOutput(n) \
+            for n, in cursor.fetchall()]
+    if not columnNames:
+        raise "Invalid table name."
+    if options.removePrefix \
+            and tableName.startswith(options.removePrefix.upper()):
+        tableName = tableName[len(options.removePrefix):]
+    if options.addPrefix:
+        tableName = options.addPrefix + tableName
+    if options.removeSuffix \
+            and tableName.endswith(options.removeSuffix.upper()):
+        tableName = tableName[:-len(options.removeSuffix)]
+    if options.addSuffix:
+        tableName += options.addSuffix
+    print "create or replace view " + tableName.lower() + " as"
+    print "select"
+    print ",\n".join(columnNames)
+    print fromClause
+    print
+print >> sys.stderr, "Done."
+
+Changes from 7.3 to 7.4
+ 1) Use cx_Logging to output messages rather than write directly to stderr.
+ 2) Added support for describing comments on tables and columns.
+ 3) Improved output when an exception occurs.
+ 4) cx_Oracle 4.2 is now required.
+ 5) Replace use of executemanyprepared() with executemany() and bind arrays
+    instead of dictionaries which actually improves performance by about 20-25%
+    in some cases.
+ 6) Moved code from module cx_DumpData in project cx_PyOracleLib into DumpData.
+ 7) Provide more meaningful message when source or target directory is missing
+    in GeneratePatch as requested by Micah Friesen.
+ 8) Ignore invalid objects of type "UNDEFINED" when describing objects.
+ 9) Export the roles before the users as the reason the roles are included is
+    because they are administered by the users and the grants will by
+    definition fail.
+10) In ExportObjects, create the directory before any exporting actually takes
+    place in order to handle the situation when no objects are exported as
+    requested by Micah Friesen.
+11) Eliminated identical grants made by different users when describing
+    objects.
+12) Add phrase "(n% of file)" to the reporting message of ImportData when
+    possible as requested by Don Reid.
+13) Display something more reasonable when compiling statements that modify
+    constraints.
+
+Changes from 7.2 to 7.3
+ 1) Added ExportXML and ImportXML utilities for importing and exporting data
+    from an Oracle database as XML.
+ 2) Added support in ExportData for limiting the number of rows exported and
+    skipping some rows first.
+ 3) Added support in CopyData for the case where all the columns on the target
+    table form the primary key.
+ 4) Added support for case sensitive tables and column names in CopyData.
+ 5) Added option --report-point to CopyData which defaults to --commit-point.
+ 6) Added options --log-file, --log-level and --log-prefix to all of the tools
+    and removed --timestamp and --prefix on DbDebugger since the new options
+    cover the functionality of the old ones.
+ 7) Added options --suppress-owner-dir, --include-roles, --include-users,
+    --no-related and --no-triggers to ExportObjects.
+ 8) Added option --sort-by to DumpData to modify the query executed to include
+    a sort clause.
+ 9) Added option --only-if to DescribeSchema and ExportObjects which allows
+    for filtering the objects described by a where clause against dba_objects.
+10) DbDebugger now displays the time to the microsecond if desired.
+11) GeneratePatch now makes sure that owner changes are displayed before
+    displaying anything about the objects for that owner that are being
+    patched.
+12) GeneratePatch now produces consistent results by performing a sort before
+    displaying any results.
+13) Fixed bug in GeneratePatch parsing grants with the "with grant option"
+    specified.
+14) Fixed bug in DescribeObject where connect statements were being displayed.
+
+Changes from 7.1 to 7.2
+ 1) Added new tools ExportColumn and ImportColumn for exporting (importing)
+    CLOB and BLOB columns from (into) an Oracle database.
+ 2) Include package used for debugging.
+ 3) When using DescribeObject, check if the object with the given name matches
+    an object in the database, including case. If the object cannot be found,
+    check for an object in the database by converting the argument to
+    uppercase. Finally, if the name is not found and is not qualified with a
+    schema owner, search the public synonyms for one by that name and describe
+    that object instead.
+ 4) Added feedback to DbDebugger indicating on what database the connection
+    has been established and on what pipe name the debugger is listening.
+ 5) Added optional argument to specify a filename into which to put the output
+    created by DescribeObject. This is of particular use on Windows which
+    doesn't handle redirection very well, particularly in GUI programs.
+
+Changes for 7.1
+ 1) First introduced to the public.
+
+"""Import data from a file into a CLOB or BLOB column in a database table."""
+
+import cx_LoggingOptions
+import cx_OptionParser
+import cx_OracleUtils
+import cx_Oracle
+import sys
+
+import Options
+
+# parse command line
+parser = cx_OptionParser.OptionParser("ImportColumn")
+parser.AddOption(cx_OracleUtils.SchemaOption())
+parser.AddOption(Options.BINARY)
+parser.AddOption(Options.STATEMENT_IN_FILE)
+cx_LoggingOptions.AddOptions(parser)
+parser.AddArgument("statement", required = True,
+        help = "the statement to execute OR the name of the file containing "
+               "the statement to execute if the --statement-in-file option "
+               "is specified OR the name of the column in which to import "
+               "the contents of the file in the form [Owner.]Table.Column "
+               "in which case an insert statement will be attempted and if "
+               "that fails with a unique constraint violation, an update "
+               "statement will be attempted")
+parser.AddArgument("fileName", required = True,
+        help = "the name of the file from which to read the data that "
+               "is to be imported")
+parser.AddArgument("values", keywords = True,
+        help = "any number of values of the form name=value which will be "
+               "turned into bind variables which will be bound to the "
+               "statement that is to be executed")
+options = parser.Parse()
+cx_LoggingOptions.ProcessOptions(options)
+
+# connect to the database
+connection = cx_OracleUtils.Connect(options.schema)
+cursor = connection.cursor()
+
+# verify options
+if options.binary:
+    mode = "rb"
+    bindType = cx_Oracle.LONG_BINARY
+else:
+    mode = "r"
+    bindType = cx_Oracle.LONG_STRING
+data = file(options.fileName, mode).read()
+if options.statementInFile:
+    options.statement = file(options.statement).read().strip()
+options.isColumn = " " not in options.statement
+if not options.isColumn:
+    statement = options.statement
+    options.values["data"] = data
+    cursor.setinputsizes(data = bindType)
+else:
+    parts = options.statement.upper().split(".")
+    if len(parts) < 2 or len(parts) > 3:
+        raise "Column name must be of the form [Owner.]Table.Column"
+    if len(parts) == 2:
+        owner = connection.username.upper()
+        table, column = parts
+    else:
+        owner, table, column = parts
+    clauses = ["%s = :%s" % (n, n) for n in options.values]
+    updateStatement = "update %s.%s set %s = :%s where %s" % \
+            (owner, table, column, column, " and ".join(clauses))
+    options.values[column] = data
+    names = options.values.keys()
+    values = [":%s" % n for n in names]
+    statement = "insert into %s.%s (%s) values (%s)" % \
+            (owner, table, ",".join(names), ",".join(values))
+    initialBinds = {column : bindType}
+    cursor.setinputsizes(**initialBinds)
+
+# execute the statement
+try:
+    cursor.execute(statement, **options.values)
+except cx_Oracle.DatabaseError, e:
+    e, = e.args
+    if e.code == 1 and options.isColumn:
+        cursor.setinputsizes(**initialBinds)
+        cursor.execute(updateStatement, **options.values)
+    else:
+        raise
+connection.commit()
+
+print >> sys.stderr, "Column succesfully imported."
+
+"""Import data from an export file into tables in an Oracle database."""
+
+import cx_ImportData
+import cx_Logging
+import cx_LoggingOptions
+import cx_OptionParser
+import cx_OracleUtils
+import os
+import sys
+
+import Options
+
+# parse command line
+parser = cx_OptionParser.OptionParser("ImportData")
+parser.AddOption(cx_OracleUtils.SchemaOption())
+parser.AddOption(Options.ARRAY_SIZE)
+parser.AddOption(Options.COMMIT_POINT)
+parser.AddOption(Options.REPORT_POINT)
+parser.AddOption(Options.SET_ROLE)
+parser.AddOption("--include-tables", action = "append", metavar = "LIST",
+        help = "name of tables to include in the import")
+parser.AddOption("--exclude-tables", action = "append", metavar = "LIST",
+        help = "name of tables to exclude from the import")
+cx_LoggingOptions.AddOptions(parser)
+parser.AddArgument("fileName", required = 1,
+        help = "the name of the file from which to read the exported data or "
+               "'-' to read the exported data from stdin")
+options = parser.Parse()
+cx_LoggingOptions.ProcessOptions(options)
+
+# set up an importer
+connection = cx_OracleUtils.Connect(options.schema, options.setRole)
+importer = cx_ImportData.Importer(connection)
+if options.arraySize:
+    importer.cursor.arraysize = options.arraySize
+importer.OpenFile(options.fileName)
+if options.reportPoint:
+    importer.reportPoint = options.reportPoint
+if options.commitPoint:
+    importer.commitPoint = options.commitPoint
+    if options.reportPoint is None:
+        importer.reportPoint = options.commitPoint
+
+# set the list of tables for import
+if options.includeTables:
+    options.includeTables = [s.upper() for v in options.includeTables
+            for s in v.split(",")]
+if options.excludeTables:
+    options.excludeTables = [s.upper() for v in options.excludeTables
+            for s in v.split(",")]
+
+# import all of the data
+for tableName, columnNames in importer:
+    checkName = tableName.upper()
+    if options.includeTables and checkName not in options.includeTables \
+            or options.excludeTables and checkName in options.excludeTables:
+        cx_Logging.Trace("Skipping table %s...", tableName)
+        importer.SkipTable()
+    else:
+        cx_Logging.Trace("Importing table %s...", tableName)
+        importer.ImportTable()
+
+"""Import data from an XML file into a table in an Oracle database."""
+
+import cx_LoggingOptions
+import cx_OptionParser
+import cx_Oracle
+import cx_OracleUtils
+import cx_XML
+import _strptime
+import sys
+import time
+import xml.sax
+
+import Options
+
+# parse command line
+parser = cx_OptionParser.OptionParser("ImportXML")
+parser.AddOption(cx_OracleUtils.SchemaOption())
+parser.AddOption(Options.ARRAY_SIZE)
+parser.AddOption(Options.COMMIT_POINT)
+parser.AddOption(Options.REPORT_POINT)
+parser.AddOption(Options.SET_ROLE)
+parser.AddOption(Options.DATE_FORMAT)
+cx_LoggingOptions.AddOptions(parser)
+parser.AddArgument("fileName", required = True,
+        help = "the name of the file from which to read the data or "
+               "'-' to read the exported data from stdin")
+parser.AddArgument("tableName", required = True,
+        help = "the name of the table into which to import the data")
+options = parser.Parse()
+cx_LoggingOptions.ProcessOptions(options)
+
+# define class for managing the import
+class Handler(cx_XML.Parser):
+
+    def __init__(self, options):
+        self.connection = cx_OracleUtils.Connect(options.schema,
+                options.setRole)
+        self.cursor = self.connection.cursor()
+        if options.arraySize:
+            self.cursor.arraysize = options.arraySize
+        self.commitPoint = options.commitPoint
+        self.reportPoint = options.reportPoint
+        self.dateFormat = options.dateFormat
+        self.cursor.execute("select * from %s" % options.tableName)
+        self.columnIndexes = {}
+        self.dateColumns = {}
+        bindVars = []
+        bindVarNames = []
+        columnNames = []
+        for item in self.cursor.description:
+            name, dataType, size, internalSize, prec, scale, nullsOk = item
+            if dataType == cx_Oracle.DATETIME:
+                self.dateColumns[name.upper()] = None
+            else:
+                dataType = cx_Oracle.STRING
+            self.columnIndexes[name.upper()] = len(bindVars)
+            bindVars.append(self.cursor.var(dataType, size))
+            columnNames.append(name)
+            bindVarNames.append(":%s" % len(bindVars))
+        sql = "insert into %s (%s) values (%s)" % \
+                (options.tableName, ",".join(columnNames),
+                 ",".join(bindVarNames))
+        self.cursor.prepare(sql)
+        self.cursor.setinputsizes(*bindVars)
+        self.allowCustomTags = False
+        self.columnValue = None
+        self.rowsImported = 0
+
+    def characters(self, data):
+        if self.columnValue is not None:
+            self.columnValue += data
+
+    def endElement(self, name):
+        if self.columnValue is not None:
+            value = self.columnValue
+            if not value:
+                value = None
+            name = name.upper()
+            columnIndex = self.columnIndexes[name]
+            if value is not None and name in self.dateColumns:
+                dateValue = time.strptime(self.columnValue, self.dateFormat)
+                value = cx_Oracle.Timestamp(*dateValue[:6])
+            else:
+                value = str(self.columnValue)
+            self.row[columnIndex] = value
+            self.columnValue = None
+            self.allowCustomTags = True
+        else:
+            cx_XML.Parser.endElement(self, name)
+
+    def end_ROW(self):
+        self.rowsImported += 1
+        commit = (self.commitPoint \
+                and self.rowsImported % self.commitPoint == 0)
+        if commit or len(self.rowsToInsert) == self.cursor.arraysize:
+            self.cursor.executemany(None, self.rowsToInsert)
+            self.rowsToInsert = []
+            if commit:
+                self.connection.commit()
+        if self.reportPoint and self.rowsImported % self.reportPoint == 0:
+            print "%d rows imported." % self.rowsImported
+        self.allowCustomTags = False
+
+    def end_ROWSET(self):
+        if self.rowsToInsert:
+            self.cursor.executemany(None, self.rowsToInsert)
+        if self.commitPoint is None \
+                or self.rowsImported % self.commitPoint != 0:
+            self.connection.commit()
+        if self.reportPoint is None \
+                or self.rowsImported % self.reportPoint != 0:
+            print "%d rows imported." % self.rowsImported
+
+    def startElement(self, name, attrs):
+        if self.allowCustomTags:
+            self.columnValue = ""
+            self.allowCustomTags = False
+        else:
+            cx_XML.Parser.startElement(self, name, attrs)
+
+    def start_ROW(self, num = None):
+        self.row = [None] * len(self.columnIndexes)
+        self.rowsToInsert.append(self.row)
+        self.allowCustomTags = True
+
+    def start_ROWSET(self):
+        self.rowsToInsert = []
+
+
+# parse the XML data stream
+if options.fileName == "-":
+    inputFile = sys.stdin
+else:
+    inputFile = file(options.fileName, "r")
+handler = Handler(options)
+parser = xml.sax.make_parser()
+parser.setContentHandler(handler)
+parser.parse(inputFile)
+
+print >> sys.stderr, "Done."
+
+Copyright � 2001-2006, Computronix (Canada) Ltd., Edmonton, Alberta, Canada.
+All rights reserved.
+
+License for cx_OracleTools
+
+Redistribution and use in source and binary forms, with or without
+modification, are permitted provided that the following conditions are met:
+
+    1. Redistributions of source code must retain the above copyright notice,
+       this list of conditions, and the disclaimer that follows.
+
+    2. Redistributions in binary form must reproduce the above copyright
+       notice, this list of conditions, and the following disclaimer in the
+       documentation and/or other materials provided with the distribution.
+
+    3. Neither the name of Computronix nor the names of its contributors may
+       be used to endorse or promote products derived from this software
+       without specific prior written permission.
+
+DISCLAIMER:
+THIS SOFTWARE IS PROVIDED BY COMPUTRONIX AND CONTRIBUTORS *AS IS*
+AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO,
+THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
+PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL COMPUTRONIX
+OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
+SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
+LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
+USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED
+AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
+OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
+OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
+SUCH DAMAGE.
+
+Computronix� is a registered trademark of Computronix (Canada) Ltd.
+
+"""Define common options used in describing objects."""
+
+import cx_OptionParser
+import os
+
+ARRAY_SIZE = cx_OptionParser.Option("--array-size", type = "int",
+        metavar = "N", help = "array size is <n> rows")
+
+BINARY = cx_OptionParser.Option("--binary", action = "store_true",
+        help = "the column is a BLOB (or long raw), not a CLOB (or long)")
+
+COMMIT_POINT = cx_OptionParser.Option("--commit-point", metavar = "N",
+        type = "int",
+        help = "number of rows after which a commit should take place")
+
+DATE_FORMAT = cx_OptionParser.Option("--date-format", metavar = "FORMAT",
+        default = "%Y-%m-%d %H:%M:%S",
+        help = "use this format for importing/exporting date values; the "
+               "format specifiers are the same as those used by the C "
+               "function strptime")
+
+DEFAULT_STORAGE = cx_OptionParser.Option("--default-storage", default = True,
+        action = "store_false", dest = "wantStorage",
+        help = "exclude the storage characteristics of the object")
+
+DEFAULT_TABLESPACE = cx_OptionParser.Option("--default-tablespace",
+        default = True, action = "store_false", dest = "wantTablespace",
+        help = "exclude the tablespace in which the object is found")
+
+DONT_MERGE_GRANTS = cx_OptionParser.Option("--dont-merge-grants",
+        default = True, action = "store_false", dest = "mergeGrants",
+        help = "do not merge the grants by grantee and privilege")
+
+INCLUDE_ROLES = cx_OptionParser.Option("--include-roles", default = False,
+        action = "store_true", dest = "includeRoles",
+        help = "include roles in output which are administered by the schema")
+
+INCLUDE_SEQUENCE_VALUES = cx_OptionParser.Option("--include-sequence-values",
+        default = False, action = "store_true", dest = "wantSequenceValues",
+        help = "include sequence values")
+
+INCLUDE_USERS = cx_OptionParser.Option("--include-users", default = False,
+        action = "store_true", dest = "includeUsers",
+        help = "include create user statements")
+
+MAX_LONG_SIZE = cx_OptionParser.Option("--max-long-size", metavar = "N",
+        type = "int", default = 128 * 1024,
+        help = "max long size is <n> bytes")
+
+NAME_ONLY = cx_OptionParser.Option("--object-name-only", default = False,
+        action = "store_true", dest = "nameOnly",
+        help = "export the name only, not the SQL to create the object")
+
+NAMES = cx_OptionParser.Option("--name", default = [],
+        action = "append", dest = "schemas", metavar = "NAME",
+        help = "export objects found in schemas with the given names")
+
+NO_COMMENTS = cx_OptionParser.Option("--no-comments", default = True,
+        action = "store_false", dest = "wantComments",
+        help = "exclude comments made on objects")
+
+NO_GRANTS = cx_OptionParser.Option("--no-grants", default = True,
+        action = "store_false", dest = "wantGrants",
+        help = "exclude grants made on objects")
+
+NO_RELATED = cx_OptionParser.Option("--no-related", default = True,
+        action = "store_false", dest = "wantRelated",
+        help = "exclude related objects")
+
+NO_TRIGGERS = cx_OptionParser.Option("--no-triggers", default = True,
+        action = "store_false", dest = "wantTriggers",
+        help = "exclude related triggers")
+
+ONLY_IF = cx_OptionParser.Option("--only-if", metavar = "CLAUSE",
+        help = "only export objects which match the given criteria; this "
+               "option is expected to form a where clause that can be "
+               "executed against the view *_objects")
+
+ONLY_TYPES = cx_OptionParser.Option("--only-types", default = [],
+        action = "append", dest = "objectTypes", metavar = "TYPES",
+        help = "only export objects of the given types")
+
+REPORT_POINT = cx_OptionParser.Option("--report-point", type = "int",
+        metavar = "N", help = "report point is <n> rows")
+
+SET_ROLE = cx_OptionParser.Option("--set-role", metavar = "ROLE",
+        help = "enable this <role> [identified by <password>] in the database "
+               "immediately after connecting by calling dbms_session.set_role")
+
+SHOW_FOREIGN_KEYS = cx_OptionParser.Option("--show-foreign-keys",
+        default = False, action = "store_true", dest = "wantForeignKeys",
+        help = "include the foreign keys referencing the object")
+
+SPLIT_RELATED = cx_OptionParser.Option("--split-related", default = False,
+        action = "store_true", dest = "splitRelated",
+        help = "split related objects into separate files when exporting")
+
+STATEMENT_IN_FILE = cx_OptionParser.Option("--statement-in-file",
+        action = "store_true",
+        help = "the statement argument is a file name in which to locate the "
+               "statement to execute")
+
+USE_DBA_VIEWS = cx_OptionParser.Option("--use-dba-views", default = False,
+        action = "store_true", dest = "useDbaViews",
+        help = "use DBA views to describe the objects")
+
+cx_OracleTools
+--------------
+This project contains a number of Python scripts that handle Oracle databases.
+Each of these scripts is self documented by the --help or -h option.
+Additional documentation will be forthcoming at a later date. A brief
+description of each script is provided here.
+
+CompileSource - execute statements in a file, checking for errors
+CopyData - copy data from one table or view to another
+DbDebugger - allows simple debugging of PL/SQL
+DescribeObject - describe objects as SQL statements for recreation
+DescribeSchema - describe multiple objects as SQL statements for recreation
+DumpCSV - dump the results of a select statement as comma separated values
+DumpData - dump the results of a select statement as insert statements
+ExportColumn - dump the data from a column into a file
+ExportData - dump the data from a database into a portable dump file
+ExportObjects - describe object as SQL statements for recreation in files
+ExportXML - export data from a table into a simple XML file
+GeneratePatch - generate SQL script to go from one set of objects to another
+GenerateView - generate a view statement for a table
+ImportColumn - import the contents of a file into a column in the database
+ImportData - import the data dumped with ExportData
+ImportXML - import data from an XML file (such as those created by ExportXML)
+RebuildTable - generate SQL script to rebuild the table
+RecompileSource - recompile all invalid objects in the database
+
+This project depends on the cx_PyGenLib and cx_PyOracleLib and cx_Oracle
+projects. These must be installed before using these scripts if you are
+running in source mode. If you are using the binary version, these projects
+can be safely ignored. The binary versions of these tools were built with
+cx_Freeze. Each of these projects can be found at
+
+http://starship.python.net/crew/atuining
+
+This project is released under a free software license. See LICENSE.txt for
+more details.
+
+"""Generate a script for rebuilding a table."""
+
+import cx_LoggingOptions
+import cx_OptionParser
+import cx_OracleObject
+import cx_OracleUtils
+import sys
+
+import Options
+
+# parse command line
+parser = cx_OptionParser.OptionParser("RebuildTable")
+parser.AddOption(cx_OracleUtils.SchemaOption())
+parser.AddOption(Options.NO_COMMENTS)
+parser.AddOption(Options.NO_GRANTS)
+parser.AddOption(Options.NO_RELATED)
+parser.AddOption(Options.NO_TRIGGERS)
+parser.AddOption(Options.DONT_MERGE_GRANTS)
+parser.AddOption(Options.DEFAULT_TABLESPACE)
+parser.AddOption(Options.DEFAULT_STORAGE)
+parser.AddOption(Options.USE_DBA_VIEWS)
+parser.AddOption(Options.MAX_LONG_SIZE)
+parser.AddOption("--with-copydata", action = "store_true",
+        help = "rebuilding the table is done with CopyData")
+cx_LoggingOptions.AddOptions(parser)
+parser.AddArgument("tableName", required = 1,
+        help = "the name of the table to rebuild")
+options = parser.Parse()
+cx_LoggingOptions.ProcessOptions(options)
+
+# set up describe environment
+connection = cx_OracleUtils.Connect(options.schema)
+environment = cx_OracleObject.Environment(connection, options)
+describer = cx_OracleObject.Describer(environment, options)
+
+# determine the object and its owner
+tableName = options.tableName.upper()
+if "." in tableName:
+  tableOwner, tableName = tableName.split(".")
+else:
+  tableOwner = connection.username.upper()
+
+# determine the type of object
+cursor = connection.cursor()
+cursor.execute("""
+        select object_type
+        from %s_objects
+        where owner = :p_Owner
+          and object_name = :p_Name
+          and instr(object_type, 'BODY') = 0""" % environment.ViewPrefix(),
+        p_Owner = tableOwner,
+        p_Name = tableName)
+row = cursor.fetchone()
+if not row:
+    raise "Object %s.%s does not exist." % (tableOwner, tableName)
+objType, = row
+if objType != "TABLE":
+    raise "Object %s.%s is not a table." % (tableOwner, tableName)
+
+# perform the describe
+table = cx_OracleObject.ObjectByType(environment, tableOwner, tableName,
+        objType)
+cursor.execute("""
+        select column_name
+        from %s_tab_columns
+        where owner = :p_Owner
+          and table_name = :p_Name
+        order by column_id""" % environment.ViewPrefix(),
+        p_Owner = tableOwner,
+        p_Name = tableName)
+columnNames = [n for n, in cursor.fetchall()]
+
+# produce the output
+print "whenever sqlerror exit failure"
+print "whenever oserror exit failure"
+print
+print "rename", tableName, "to bk;"
+print
+table.Export(sys.stdout, options.wantTablespace, options.wantStorage)
+if options.wantGrants:
+    table.ExportPrivileges(sys.stdout, options.mergeGrants)
+if options.withCopydata:
+    selectClauses = ", ".join(columnNames)
+    print "!CopyData --no-check-exists --commit-point 250 --array-size 250",
+    print "'select %s from bk'" % selectClauses,
+    print tableName
+else:
+    selectClauses = ",\n  ".join(columnNames)
+    print "insert /*+ append */ into", tableName, "nologging"
+    print "select\n  %s\nfrom bk;" %  selectClauses
+    print
+    print "commit;"
+print
+print "drop table bk cascade constraints;"
+print
+constraints = []
+if options.wantComments:
+    table.ExportComments(sys.stdout)
+if options.wantRelated:
+    for constraint in table.Constraints():
+        constraints.append((constraint.owner, constraint.name))
+        describer.ExportObject(constraint)
+    for index in table.Indexes():
+        describer.ExportObject(index)
+if options.wantTriggers:
+    for trigger in table.Triggers():
+        describer.ExportObject(trigger)
+for constraint in table.ReferencedConstraints():
+    if (constraint.owner, constraint.name) not in constraints:
+        describer.ExportObject(constraint)
+

RecompileSource.py

+"""Recompile all of the invalid source objects in an Oracle database."""
+
+import cx_LoggingOptions
+import cx_OptionParser
+import cx_OracleUtils
+
+# parse command line
+parser = cx_OptionParser.OptionParser("RecompileSource")
+parser.AddOption(cx_OracleUtils.SchemaOption())
+parser.AddOption("--on-error-continue", action = "store_false",
+        dest = "raiseError", default = 1,
+        help = "when an error occurs, continue processing")
+parser.AddOption("--include", metavar = "LIST", action = "append",
+        help = "list of schemas to recompile instead of the entire database")
+parser.AddOption("--exclude", metavar = "LIST", action = "append",
+        help = "list of schemas to exclude from recompile")
+parser.AddOption("--password",
+        help = "password to use for schema connections")
+cx_LoggingOptions.AddOptions(parser)
+options = parser.Parse()
+cx_LoggingOptions.ProcessOptions(options)
+
+# connect to the database
+connection = cx_OracleUtils.Connect(options.schema)
+
+# massage the password option
+password = options.password
+if not password:
+    password = connection.password
+
+# massage the list of schemas to include
+if options.include:
+    includeSchemas = [s.upper() for v in options.include for s in v.split(",")]
+else:
+    cursor = connection.cursor()
+    cursor.arraysize = 25
+    cursor.execute("""
+            select username
+            from dba_users u
+            where exists
+              ( select owner
+                from dba_objects
+                where owner = u.username
+              )""")
+    includeSchemas = [n for n, in cursor.fetchall()]
+
+# massage the list of schemas to exclude
+excludeSchemas = []
+if options.exclude:
+    excludeSchemas = [s.upper() for v in options.exclude for s in v.split(",")]
+
+# perform the recompile
+cx_OracleUtils.RecompileInvalidObjects(connection, includeSchemas,
+        excludeSchemas, password, options.raiseError)
+
+create or replace package pkg_Debug as
+
+  /*---------------------------------------------------------------------------
+   * DESCRIPTION
+   *   Routines used to display output on a separate process for debugging
+   * purposes.
+   *-------------------------------------------------------------------------*/
+
+  /*---------------------------------------------------------------------------
+   * PipeName()
+   *   Returns the pipe name to which debug output will be sent.
+   *-------------------------------------------------------------------------*/
+  function PipeName
+  return varchar2;
+
+  /*---------------------------------------------------------------------------
+   * SetPipeName()
+   *   Set the pipe name to which debug output will be sent.
+   *-------------------------------------------------------------------------*/
+  procedure SetPipeName (
+    a_PipeName				varchar2
+  );
+
+  /*---------------------------------------------------------------------------
+   * Enabled()
+   *   Returns a boolean (single character "Y" or "N") indicating if debug is
+   * currently enabled.
+   *-------------------------------------------------------------------------*/
+  function Enabled
+  return varchar2;
+
+  /*---------------------------------------------------------------------------
+   * Suspended()
+   *   Returns a boolean (single character "Y" or "N") indicating if debug is
+   * currently suspended.
+   *-------------------------------------------------------------------------*/
+  function Suspended
+  return varchar2;
+
+  /*---------------------------------------------------------------------------
+   * Enable()
+   *   Enable debug output. If a pipename is specified that pipe will be used
+   * to log future debug output; otherwise, the currently set pipename will be
+   * used.
+   *-------------------------------------------------------------------------*/
+  procedure Enable (
+    a_PipeName				varchar2 default null
+  );
+
+  /*---------------------------------------------------------------------------
+   * Disable()
+   *   Disable debug output.
+   *-------------------------------------------------------------------------*/
+  procedure Disable;
+
+  /*---------------------------------------------------------------------------
+   * Suspend()
+   *   Suspend debugging. This can be reversed by resuming or enabling again.
+   *-------------------------------------------------------------------------*/
+  procedure Suspend;
+
+  /*---------------------------------------------------------------------------
+   * Resume()
+   *   Resume debugging after a suspension.
+   *-------------------------------------------------------------------------*/
+  procedure Resume;
+
+  /*---------------------------------------------------------------------------
+   * Shutdown()
+   *   Shutdown the session which is receiving debug output. If the pipename
+   * is specified that pipename will be sent the shutdown message; otherwise,
+   * the currently set pipename will be send the message.
+   *-------------------------------------------------------------------------*/
+  procedure Shutdown (
+    a_PipeName				varchar2 default null
+  );
+
+  /*---------------------------------------------------------------------------
+   * PutLine()
+   *   Pass the given string to the debug output session.
+   *-------------------------------------------------------------------------*/
+  procedure PutLine (
+    a_Message				varchar2
+  );
+
+  /*---------------------------------------------------------------------------
+   * PutSingleLine()
+   *   Put the line to the debug output session. If the session is not enabled
+   * currently, it will be enabled long enough to output this line and then
+   * disabled again.
+   *-------------------------------------------------------------------------*/
+  procedure PutSingleLine (
+    a_Message				varchar2
+  );
+
+end pkg_Debug;
+/
+
+grant execute
+on pkg_debug
+to public;
+
+create or replace package body pkg_Debug as
+
+  /*---------------------------------------------------------------------------
+   * Globals
+   *-------------------------------------------------------------------------*/
+  gc_ProtocolVersion			constant varchar2(30) := '2';
+  g_PipeName				varchar2(30) := 'DbDebugger';
+  g_Enabled				boolean := false;
+  g_Suspended				boolean := false;
+  g_TimeOut				number := 20;
+
+  /*---------------------------------------------------------------------------
+   * PipeName() -- PUBLIC
+   *-------------------------------------------------------------------------*/
+  function PipeName
+  return varchar2 is
+  begin
+    return g_PipeName;
+  end;
+
+  /*---------------------------------------------------------------------------
+   * SetPipeName() -- PUBLIC
+   *-------------------------------------------------------------------------*/
+  procedure SetPipeName (
+    a_PipeName				varchar2
+  ) is
+  begin
+    g_PipeName := a_PipeName;
+  end;
+
+  /*---------------------------------------------------------------------------
+   * Enabled() -- PUBLIC
+   *-------------------------------------------------------------------------*/
+  function Enabled
+  return varchar2 is
+  begin
+    if g_Enabled then
+      return 'Y';