Databases Reference
In-Depth Information
How it works...
We have a text file,
participationrates2010.txt
, located in the
/home/oracle/
folder, and we want to load the data contained within it to a table in our Oracle database.
A small excerpt from the file is as follows:
06003 ||Alpine County, CA ||County ||0.71 ||0.22
06091 ||Sierra County, CA ||County ||0.50 ||0.45
06027 ||Inyo County, CA ||County ||0.79 ||0.78
06049 ||Modoc County, CA ||County ||0.62 ||0.57
06043 ||Mariposa County, CA ||County ||0.62 ||0.57
06011 ||Colusa County, CA ||County ||0.72 ||0.71
06105 ||Trinity County, CA ||County ||0.58 ||0.48
06021 ||Glenn County, CA ||County ||0.73 ||0.76
06015 ||Del Norte County, CA ||County ||0.71 ||0.74
06035 ||Lassen County, CA ||County ||0.55 ||0.67
06051 ||Mono County, CA ||County ||0.32 ||0.27
06063 ||Plumas County, CA ||County ||0.52 ||0.54
06069 ||San Benito County, CA ||County ||0.74 ||0.75
06005 ||Amador County, CA ||County ||0.72 ||0.71
06093 ||Siskiyou County, CA ||County ||0.72 ||0.72
In this text file, fields are delimited by a space followed by double pipes, containing both
alphanumeric and numeric data.
The first step to load the data in the database is to create the destination table, as in step 2.
We also need to define the so-called control file, in order to instruct SQL*Loader on the file
format and eventually, on filtering. In the
Getting ready
section of this recipe we defined the
loaddata.ldr
control file, in which we indicated the type of operation (load data into table
MY_IMPORT_DATA
), the file type fields separated by), and the order of the fields in the file.
This is a very simple version of the control file. We will not investigate more details about
different options available for filtering, formatting, and other more advanced features of
SQL*Loader, which can be easily examined in the Oracle documentation.
In step 3, run the
sqlldr
executable, passing the credentials to log into the database, the
name of the control file to use (the
control
parameter), the file containing data (parameter
data
), while suppressing on-screen messages. The parameter
ERRORS=10
will block the load
process if 10 errors occur during the load process.
