One general use of an external table is to employ SQL to load data from an OS file into a regular database table. This facilitates the loading of large amounts of data from files into the database. Almost anything you can do with SQL*Loader, you can achieve with external tables.

An important difference is that SQL*Loader loads data into a table, and external tables do not need to do this. Another important difference is that SQL*Loader can work with files accessible on any database user’s local computer, whereas external tables work only with files accessible directly from the database server.

External tables are more flexible and intuitive than SQL*Loader. Additionally, you can obtain very good performance when loading data with external tables by using direct path and parallel features, and you can even partition external tables.

A quick comparison of how data is loaded into the database via SQL*Loader and external tables highlights the usage. You can use the following steps to load and transform data with SQL*Loader:

1. Create a parameter file that SQL*Loader uses to interpret the format of the data in the OS file.

2. Create a regular database table into which SQL*Loader will insert records. The data will be staged here until they can be further processed.

3. Run the SQL*Loader sqlldr utility to load data from the OS file into the database table (created in step 2). When loading data, SQL*Loader has some features that allow you to transform data. This step requires you correctly map the parameter file to the table and corresponding columns and may take a few attempts.

4. Create another table that will contain the completely transformed data.

5. Run SQL to extract the data from the staging table from step 2, and then transform and insert the data into the production table from step 4.

Compare the previous SQL*Loader list to the following steps for loading and transforming data, using external tables:

1. Execute a CREATE TABLE…ORGANIZATION EXTERNAL script that maps the structure of the OS file to table columns. After this script is run, you can directly use SQL to query the contents of the OS file.

2. Create a regular table to hold the completely transformed data or insert it into an existing table.

3. Run SQL statements to load and fully transform the data from the external table from step 1 into the table created in step 2.

For many companies, SQL*Loader underpins large data-loading operations. It continues to be a good tool for that task. However, you may want to investigate using external tables. External tables have the following advantages:

•     Loading data with external tables is more straightforward and requires fewer steps.

•     The interface for creating and loading from external tables is SQL*Plus or your favorite SQL tool.

•     You can view data (via SQL) in an external table before they’re loaded into a database table.

•     You can load, transform, and aggregate the data without an intermediate staging table. For large amounts of data, this can be a huge space savings.