Databases Reference
In-Depth Information
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 following text in this file:
LOAD DATA
INTO TABLE MY_IMPORT_DATA
FIELDS TERMINATED BY ' ||'
(
GEO_ID,
PLACE_NAME,
TYPE,
PCT_2000,
PCT_2010
)
We will execute SQL*Plus from the
/oracle/home/
directory, to avoid specifying the complete
path in our command line for
loaddata.ldr
and
participationrates2010.txt
files.
