SQL LOADER By john niveth Jan 26, 2006  digg!     Print     Email to Friend Note: This article was written for educational purpose only. Please refer to the related vendor documentation for detail.

SQL LOADER Means 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 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. 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. Input files 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