Loading data with SQL*Loader and Data Pump

In this recipe we will see how to load data from text files in the Oracle database using two different tools—SQL*Loader and External Tables. We will also see how to use the Data Pump to transfer data between different Oracle databases.

Getting ready

In order to test the functionalities provided by the SQL*Loader, we need some text files containing data. The text used in these recipes has the United States census participation rates data freely available at the following site:

http://2010.census.gov/cgi-bin/staterates.cgi

The direct link to the file used in the recipe is at:

http://2010.census.gov/2010census/take10map/downloads/participationrates2010.txt

To test the recipe, download the file from the above link and save it to a directory accessible from the database server. In the recipe code we will use /oracle/home/ as the path for the file.

In the same directory, create a text file, using your preferred text editor, naming it loaddata.ldr, and copy the...