Generate external table definitions from SQL*Loader

by Bob Watkins | Oct 03, 2006 2:28:00 PM

Tags: Databases, Bob Watkins, SQL*Loader, Oracle Corp., external table, Oracle Tips Newsletter

Takeaway: External tables in Oracle enable you to query text files with the full power of the SQL language. Here is a quick way to create them using scripts generated by SQL*Loader.

External tables, introduced in Oracle 9i, enable you to map a sequential text file to an Oracle table definition. Once the external table is defined, you can use the full power of the SQL SELECT statement—including parallel querying—to process it.

External tables are based on SQL*Loader, but the syntax to define them is different. If you're already familiar with SQL*Loader, there is a shortcut: use SQL*Loader itself to generate a script that sets up the external table definition for you.

The parameter EXTERNAL_TABLE=GENERATE_ONLY, when added to a SQL*Loader command line, will translate the control file used in the run into a SQL script and output it to the log file of the run. You can edit the log file or copy and paste the code into a new script.

Listing A shows a SQL*Loader control file designed to load variable-length text records into the database. Executing the following command does not cause rows to be loaded, but it does output an external table script to the log file. Listing B shows that output.

sqlldr scott/tiger CONTROL=loademp.ctl
EXTERNAL_TABLE=GENERATE_ONLY

First, the script creates a directory object that points to the directory containing the input file. Directory objects give short, platform-neutral names to operating system-specific full pathnames. The CREATE DIRECTORY command makes the association. You'll need the CREATE ANY DIRECTORY system privilege to do this, or have a DBA run it for you and grant permissions. Also, the system-generated name is awkward; it's a good idea to edit the script to give it a more meaningful name before you run it.

Next, the script contains the CREATE TABLE statement itself. The statement references the directory object (edit this if you changed the name above), the file to be loaded, and the ORGANIZATION EXTERNAL clause. It also contains the syntax that describes the text file's layout.

Finally, the script contains an example INSERT statement that SELECTS from the external table in a subquery, and statements to drop both the external table and directory when you are done with them.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site.

Print/View all Posts Comments on this article

Nice tip!yparesh@...  | 10/04/06

Point External Table To Symbolic LinkJohnnySacks  | 10/04/06