What is External Tables in Oracle 10g with Example? ~ Oracle Apps Knowledge Sharing

Tuesday, February 8, 2011

What is External Tables in Oracle 10g with Example?

Brush Up

External Tables let you query data in a flat file as though the file were an Oracle table. In 9i, only read operations were permitted; in 10g, you can also write out data to an external table, although you can't write to an existing table.

While external tables can be queried, they're not usable in many ways regular Oracle tables are. You cannot perform any DML operations on external tables other than table creation; one consequence is that you can't create an index on an external table. External tables are largely used as a convenient way of moving data into and out of the database.

Oracle uses SQL*Loader functionality, through the ORACLE_LOADER access driver to move data from the flat file into the database; it uses a Data Pump access driver to move data out of the db into a file in an Oracle-proprietary format, and back into the database from files of that format. While there are some behaviour differences and restrictions, you can think of external tables as a convenient, SQL-based way to use SQL*Loader and Data Pump functionality.

For example, suppose that you receive a daily .csv report from another department. Instead of writing a SQL*Loader script to import each day's .csv file into your database, you can simply create an external table and write an "insert ... select" SQL query to insert the data directly into your tables. Place the day's CSV file in the location specified in the external table definition, run the query, and you're done.

Example Implementation Steps for importing a simple .csv File into the Database:

Step1: Create a Directory in OS

Since an external table's data is in the operating system, its data file needs to be in a place Oracle can access it. So the first step is to create a directory and grant access to it. First create the directory in the operating system, or choose an existing directory. It must be a real directory, not a symlink. Make sure that the OS user that the Oracle binaries run as has read-write access to this directory.

Step2: Create a Directory in Oracle linking to the Directory Created in STEP 1

Run the below script to Create a Directory in Oracle.

Step3: Place the Data File in the OS Directory

Put the external table's data file in the data directory.

In this example, I'll use the following CSV file:

Step4: Creation of External Table in Oracle Schema

The CREATE TABLE statement for an external table has two parts. The first part, like a normal CREATE TABLE, has the table name and field specs. This is followed by a block of syntax specific to external tables, which lets you tell Oracle how to interpret the data in the external file.

At this point, Oracle hasn't actually tried to load any data. It doesn't attempt to check the validity of many of the external-table-specific parameters you pass it. The CREATE TABLE statement will succeed even if the external data file you specify doesn't actually exist.

With the create table statement, you've created table metadata in the data dictionary and instructed Oracle how to direct the ORACLE_LOADER access driver to parse the data in the datafile.

Step5: Kick Off the Load by Accessing the External Table

Oracle used the ORACLE_LOADER driver to process the file, and just as with SQL*Loader, it's created a log file that you can inspect to see what just happened. The log file -- and the "bad" and "discard" files -- will have been written to the directory you specified as the "default directory" in your CREATE TABLE statement, and the file names default to tablename_ospid :

If Oracle was unable to process the data given the access parameters you specified, you'll get an error on the command line and in the log file, and there will also be a bad and/or discard file.

You may want to configure separate directories for the SQL*Loader output files -- the LOG file, the DISCARD file and the BAD file -- as well as for the external table data. You can lump all four in the same directory, as we did in the previous example, although it's a bad idea: a naming mishap could have you overwriting one external table's data file with another's bad file. I like to have one directory for data files, and one for log/bad/discard files:

Alternatively, you can set up the table so that no log, discard or bad files are generated. SELECTing data from the table will still fail if the maximum number of rejects is exceeded, just as in SQL*Loader.

Step 6: Loading Data into Actual Database Tables from External Tables

Where external tables really shine are in the ease with which you can load their data into your tables. A particularly nice feature is that you can use any valid function that the current Oracle user has rights on to transform the raw data before loading it into your database tables.