SQL*Loader is the primary method for quickly populating Oracle tables with data from external files. It has a powerful data parsing engine that puts little limitation on the format of the data in the datafile. SQL*Loader is invoked when you specify the sqlldr command or use the Enterprise Manager interface.
SQL*Loader is an integral feature of Oracle databases and is available in all configurations.
SQL*Loader can be used to do the following:
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.
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.
LOB data can be lengthy enough that it makes sense to load it from a LOBFILE. LOB data instances are still considered to be in fields, but these fields are not organized into records. Therefore, the processing overhead of dealing with records is avoided. This type of or organization of data is ideal for LOB loading.
You can use SQL*Loader to bulk load objects, collections, and LOBs. SQL*Loader supports the following bulk loads:
SQL*Loader provides three methods to load data: Conventional Path, Direct Path, and External Table.
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.
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 datafiles, 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.
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:
SQL*Loader is a high-speed data loading utility that loads data from external files into tables in an Oracle database. It provides database administrators with the fast performance and flexibility required to get load jobs conducted as quickly and efficiently as possible.