oraddlscript: Solving ODP.Net version conflicts

I got feedback on oraddlscript that exceptions are raised due to incompatible versions of Oracle.DataAccess.dll. I could reproduce these exceptions on a non-development machine.

After installing Oracle Data Access Components (ODAC) 11.1.0.6.21 using the install command

install.bat all c:\oracle\odp odac

the problem was solved, and I could connect to the database server.

Oracle Data Access Components (ODAC) are available for download from Oracle here.

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.

Version 0.98 of dbscript Released

The latest version 0.98 of dbscript supports PostgreSQL databases in its documentation generation capabilities.

After importing the database dictionary (via direct connection using ADO.Net and Npgsql) can document a PostgreSQL database in all currently supported documentation format:

MediaWiki

Data Diagram (PNG)

HTML

ScrewTurn wiki

Integration support for PostgreSQL had some consequences: More and more functionality is handled separately for each database engine.

Database import was obviously the first one, since the data access classes (SqlConnection, SqlCommand) in .Net are different for every database library. Same goes for the database dictionary, which is best retrieve from the native system catalogs.

For import and upload, data access classes have been introduced to distinguish the different object types and their properties of each database engine. I mention work on the data access classes in a series of articles already.

In version 0.98, XML generation and object script generation are implemented separately. This results in XSL style sheets being now related to certain a database engine.

For Oracle, XML and object script generation have been updated, and the XSL style sheets have been adjusted to Oracle-specific objects and properties. The results were documented earlier.

The latest version of dbscript is available for download here.

Please leave comments and feedback.

Generating Database Documentation for ScrewTurn wikis

Updated XSL style sheets for dbscript documentation generators creating ScrewTurn content.

See here for sample output:

MS SQL AdventureWorks

Oracle Demo Schema

PostgreSQL OpenNMS

ScrewTurn also allows external Page Providers (instead of stored static pages)

MS SQL AdventureWorks (by Page Provider)

Documenting Oracle Databases

I described the capabilities of dbscript to generate MediaWiki content and a single HTML page documenting an Oracle database in a previous post.

During development of PostgreSQL support (MediaWiki, HTML) it became clear that an XSL style sheet (among other things) needed to become specific to a database engine.

I therefore updated the XSL style sheets for Oracle support, and these are the results:

• MediaWiki documentation of the Oracle Demo Shema (Sales, HR)
• Single HTML file documenting the Oracle Demo Schema

Reading LONG Oracle columns in .Net

If you use ODP.Net to access an Oracle database from .Net, and select LONG text columns, the DataReader’s GetString() method will return an empty string.

To work around this behavior, you need to set the InitialLONGFetchSize to a non-zero value, and use the OracleDataReader’s GetOracleString() method, and use ToString() to convert the result into a .Net string.

Another possibility is to set InitialLONGFetchSize to -1, so that GetString() works as expected.

The linked documentation also explains how to access LONG RAW data.

Documenting Oracle Databases

The latest version of dbscript 0.96 now supports importing schema information from Oracle databases.

Version 0.96 introduces the separation of various database engines (previous versions assumed MSSQL Server only), and a dbscript Project must now explicitly define which database engine it is based on (MSSQL or Oracle).

In the database properties form for “live” databases, you can define the Oracle connection in two ways:

• set “Server” to the instance name listed inside tnsnames.ora and leave “Catalog” empty
• set “Server” to server:port, and “Catalog” to the service name (as in tnsnames.ora)

A new field called “Import Options” allows you to define the scope of the schema import:

• “USER” (the default value)
• “ALL:” + list of user names
• “DBA:” + list of user names

The user specified in “Username” needs to have access to the ALL_ and DBA_ catalog views, respectively.

Sample Output based on the HR Demo Database

Wiki documentation of Oracle Demo Schema

Single HTML documentation of Oracle Demo Schema

Data Diagram of Oracle Demo Schema:

To access Oracle databases, dbscript requires ODP.Net from the Oracle Data Access Components 11g to be installed on the web server.

dbscript is available for download here.

What’s new in Oracle?

What’s the difference between 2 Oracle database versions?

The table below tries to answer this question, and lists links to Oracle documentation referring to

• New Features
• New Items in (Database) Reference
• New Items in SQL Reference
• New Items in PL/SQL Reference (Language, Packages and Types)

Version	New Features	Database Reference	SQL Reference	PL/SQL Reference
8i (8.1.5)			New
8iR2 (8.1.6)	New
9i (9.0.1)	New	New	New
9iR2 (9.2)	New	New A96536 Book PDF	New
10gR1 (10.1)	New	New B10755 Book PDF	New	New
10gR2 (10.2)	New B14214	New B14237 Book PDF	New B14200 Book PDF	PL/SQL Packages (all versions)
11gR1 (11.1)	New	New B28320 Book PDF	New B28286 Book PDF	PL/SQL Packages

Introducing SchemaFind

A couple of years ago I wrote this small utility to search definitions of tables, views and stored procedures in a live MSSQL or Oracle database. I recently updated the MSSQL interface to support the new object types of SQL Server 2005.

The search functionality covers object names, column names, and object definitions in T-SQL and PL/SQL code, respectively.

You can read more about SchemaFind and download it here.