The Phoenix utility is something I've written and re-written several times over the years, and it's pretty handy in a Unix environment with Oracle. I make it available here as a tarball ( phoenix.tar - 51KB), and the following text is a formatted copy of the README.phoenix file (also included in the tarball).

The build instructions reference the BE-ENV.sh script (included in the tarball) and there are environmental tweaks you'll need to make to get your compilation to work - I figure that if you've read this far, you know how to compile a Pro*C program in your environment. I use the phoenix utility on Solaris 9 and Oracle 9 - your mileage may vary on other versions of Solaris and Oracle.

OVERVIEW

The Phoenix utility is quite possibly the fastest Oracle data extraction utility around. It's also a lot easier to use than SQL*Plus.

This program takes SQL queries on stdin (multiple queries are supported, but I don't see why you'd want to do that) and writes the results out as a tab-delimited stdout stream.

This utility is primarily intended for batch use, in which this program extracts a lot of Oracle data into a delimited file for processing in shell scripts.

The program employs Dynamic SQL Method 4 to accomodate whatever query you can concoct - if it's a valid SELECT, this program can handle it. SQL commands may be up to 8K in size, and you can raise that limit if you need to by changing a couple of constants.

The default setting is to retrieve up to 128 columns without complaint, and the code will automatically detect queries that yield more columns - after a brief complaint, the code will reallocate memory on the fly to handle the extra columns.

The code performs array fetches instead of single-row fetches, which greatly increases throughput - the default setting fetches 100 rows at a time, and that's configurable via command-line option. If you set this value too high on the command-line and the code can't allocate enough memory, the program will complain and then drop back to fetching one row at a time.

JUST HOW FAST IS THIS THING, ANYWAY?

I set up a little drag race and ran Phoenix against SQL*Plus on a copy of the production POD database containing 44,644 rows in the PATCHMAIN table. Here's the SQL:

SELECT PID, PATCHID, PATCH_REVISION, Y2K_FLAG, SECURITY_FLAG, POINT_PATCH_FLAG, ENCRYPTION_PATCH_FLAG, RESTRICTED_FLAG, RELEASED_DATE, SUBMITTED_DATE, ORIGIN_ID, PATCH_REQUESTOR, DEVELOPMENT_ENGINEER, ESCALATION_NUMBER, CC_LIST, SOURCE_FLAG, PATCH_TYPE, PATCH_SIZE, PRODUCT_ID, PATCH_PUBLIC_DATE, OBSOLETE_FLAG, BADPATCH_FLAG, RECOMMENDED_FLAG, RECOMMENDED_REQUESTOR, RECOMMENDED_DATE, PATCH_STATE, LAST_MODIFIED_DATE, COMPRESSION FROM PATCHMAIN;

With the default 'gulp' set at 100 rows per fetch, Phoenix fetched 44,644 rows from PATCHMAIN into an output file in THREE SECONDS. Pool old SQL*Plus took FOURTEEN MINUTES and FOUR SECONDS to execute the same query. Granted, I could speed up SQL*Plus by turning off the column headings and so on, but still - there's just no comparison.

I don't have a big enough database to show you the huge difference a bigger 'gulp size' makes - trust me, though, on million-row tables the turbocharging effect of setting the gulp size to 1,000 or so is quite spectacular.

COMPILATION

1. 'cd' to the top of the test workspace
2. Source BE-ENV.sh ( '. BE-ENV.sh' )
3. 'cd' to the pro_c directory
4. Run 'make'
5. Run 'make install' if you'd like a copy in the bin subdirectory

EXECUTION

Here's a simple example:

Naturally, your application will use more complex SQL spanning multiple lines and doing more interesting things. Your SQL statements can be up to 8K in size (that's a lot of SQL!), and you can raise that limit by tinkering in the source code.

WHY IS PHOENIX STATICALLY LINKED?

I link Phoenix statically (even though that results in a HUGE executable) so that it is a little more portable. Once you've got it compiled for Solaris 9, for example, you can copy it to another Solaris 9 machine and run it without having to hassle with some of the finer points of dynamic linking. After copying the binary to a new system, use the standard /var/opt/oracle/oraset script to establish the basic Oracle environment and Phoenix is ready to run.

WHO NEEDS THE PHOENIX?

Anyone who wants a scriptable, automation-friendly Oracle data extraction tool that provides neatly delimited output in a big hurry. Anyone who has worked to format the output of 'sqlplus' into machine-readable form. Anyone who would rather drive a Ferrari than a Volkswagen.

THE FLYING FICKLE FINGER OF FATE

All right, yes, I know most of you are too young to remember "Laugh-In." Never mind - anyway, have you been frustrated by un-helpful Oracle messaging when you screw up your SQL? Things like "undefined column" or "syntax error" that don't tell you WHICH column in your SQL is bad or EXACTLY WHERE other errors are located?

When Phoenix encounters bad SQL, it prints out the entire SQL statement along with the error message - AND it points precisely to the cause of the problem! The Flying Fickle Finger of Fate (actually a caret, or "^") zeroes in on the error, so that you can get it right the very next time without playing Twenty Questions to find out exactly which part of your statement is incorrect.

This feature alone is worth the price of admission if you're writing non-trivial SQL queries.

EXTENDED ERROR MESSAGING

The standard sqlca.sqlerrm.sqlerrmc string only shows 70 characters of an Oracle error message. Cute, but not always as helpful as you'd like. Phoenix takes the extra step and retrieves the entire Oracle error text, which can be up to 255 characters long and often sheds quite a bit more light on the problem. You're welcome.

WHY "PHOENIX?"

Because I wrote it and that's its name, that's why.

I've done this several times over the years for various different project teams, and this incarnation is called "Phoenix" because it is the latest re-invention, re-assembly, and re-integration of the oddest, fastest, and most interesting Pro*C program I've ever written.