SQL*Loader is a high-speed data loading utility that loads data from external files into tables in an Oracle database. SQL*Loader accepts input data in a variety of formats, can perform filtering, and can load data into multiple Oracle database tables during the same load session.
Load Methods :
SQL*Loader provides three methods for loading data: Conventional Path Load, Direct Path Load, and External Table Load.
Conventional Path Load:
Conventional path load builds an array of rows to be inserted and uses the SQL INSERT statement to load the data. During conventional path loads, input records are parsed according to the field specifications, and each data field is copied to its corresponding bind array. When the bind array is full (or no more data is left to read), an array insert is executed.
Direct Path Load:
A direct path load builds blocks of data in memory and saves these blocks directly into the extents allocated for the table being loaded. A direct path load uses the field specifications to build whole Oracle blocks of data, and write the blocks directly to Oracle data files, bypassing much of the data processing that normally takes place. Direct path load is much faster than conventional load, but entails some restrictions.
A parallel direct path load allows multiple direct path load sessions to concurrently load the same data segments. Parallel direct path is more restrictive than direct path.
External Table Load:
An external table load creates an external table for data in a datafile and executes INSERT statements to insert the data from the datafile into the target table.
There are two advantages of using external table loads over conventional path and direct path loads:
- An external table load attempts to load datafiles in parallel. If a datafile is big enough, it will attempt to load that file in parallel.
- An external table load allows modification of the data being loaded by using SQL functions and PL/SQL functions as part of the INSERT statement that is used to create the external table.
File Types :
SQL*Loader Control File
The control file is a text file written in a language that SQL*Loader understands. The control file tells SQL*Loader where to find the data, how to parse and interpret the data, and where to insert the data.
Input Data and Datafiles:
SQL*Loader reads data from one or more files specified in the control file. From SQL*Loader’s perspective, the data in the datafile is organized as records. A particular datafile can be in fixed record format, variable record format, or stream record format. The chosen format depends on the data and depends on the flexibility and performance necessary for the job.
Control file – information about the data — where to load it
data file – contains data
par file – (optional) contain the user id, password and location of the control file
Control file options:
We can use the following option for loading data into the oracle table
Replace – used to replace the existing rows with the new rows inserted.
Insert – used to insert into empty table.
Append – used to add up the rows along with the already existing rows.
Bad file – Records with formatting errors or that cause Oracle errors will be stored in bad file.
Discard file – Records not satisfying a WHEN clause will be stored in discard
Getting Started, an Example:
Say, for example, that you’ve got an Excel spreadsheet with State data already in it. You’ve got 50 rows of data – each containing the State Abbreviation, State Name, an [optional] unofficial State Slogan, and the number of State Residents Who Drink Bottled Water.
(Is 50 rows of data really sufficient to justify this exercise? That’s debatable, but let’s say you’ve thought it over and you DO want to SQL*load your data into a 4-column Oracle table at UW-Stevens Point. The remote table is called sp.mystates.)
Here’s what you do:
1) Create your data file. This is easy. Save your Excel spreadsheet data AS a Comma-Separated-Variable (*.csv) file. This will automatically put commas between each of the four data elements. In addition, if any of the data elements already contain a comma, the Save AS *.csv step will optionally and automatically enclose that data in double quotes.
So, after your Save AS command, you might have a file named C:\MyStates.csv that contains data like this:
AR, Arkansas, We are sure proud of Bill, 0
WI, Wisconsin, Rose Bowl Champions Again, 5
CA, California,”Dude? You want, like, another hit of Oxygen?”, 90203049
2) Create your control file. Using any text editor, create a file (say, C:\mystates.ctl) containing these lines:
INTO TABLE sp.mystates
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
Nbr_Residents_WDBW INTEGER EXTERNAL)
The REPLACE keyword says, “Remove any existing rows before starting the load.” There’s also an INSERT [into empty table] and APPEND [to existing rows] option.
State_Abbrev, State_Name, State_Slogan, and Nbr_Residents_WDBW are the actual column names defined in the sp.mystates table.
Because the first three items are of character datatype, it was not necessary to further describe them – character is the default. The fourth column is numeric data – it totals the number of state residents who drink bottled water. The INTEGER EXTERNAL describes the datatype in the C:\mystates.csv input file.
Notice there is some missing data in the data file — Colorado has no state slogan. The TRAILING NULLCOLS statement handles the missing data; it tells SQL*Loader to load any missing data as NULL values. There are, as we said earlier, lots of available options described in the Utilities User’s Guide.
• You must have SQL*Loader and SQL*Net installed on your machine. The SQL*Loader program may have a version number included as part of its name, something like sqlldr73.exe or sqlldr80.exe. Or maybe it will be just sqlldr.exe. You can look for it in your ORAWIN95 or ORANT \BIN directory. If it’s not installed, you can get the Oracle Client Software installation CD and install “UTILITIES”.
• You must have the target database (say, it’s called ‘UWTEST’) configured as SQL*Net service in your local tnsnames.ora file. This is pretty standard stuff; it’s probably already there.
• You must have authorization to modify the sp.mystates table (INSERT, or DELETE and INSERT if you’re using the REPLACE option in the control file. In the example below we assume that user SCOTT with password TIGER has appropriate authorization.
At an MS-DOS prompt (or the Start, Run menu) , execute SQL*Loader as follows:
Sqlldr scott/tiger@UWTEST control=C: \mystates.ctl
When the load completes, look in the file C:\mystates.log. This log file will contain information about how many rows were loaded, how many rows — if any — were NOT loaded, and other information that may be useful to reassure or debug.