How to load data using SQL*Loader

SQL*Loader uses the following files: Loader control file: Contain input format, output tables, and optional conditions Input data files: Contain data in the format defined in the control file Parameter file (optional file): Consist of command line parameters for the load Log file:  Automatically created by SQL loader. Bad file: Contain rejected records. Discard file (if required): Store all records that did not satisfy the selection criteria.

SQL*Loader Control File Example LOAD DATA  INFILE ’ /SQL_LOADER / Employee_Details.csv’  BADFILE ’ Employee_Details.bad’  DISCARDFILE ’ Employee_Details.dsc’  APPEND  INTO TABLE LGHR.employee  WHEN (57) = ’.’  FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS  (EMP_NO , ename "UPPER(:ename)", DOJ "TO_TIMESTAMP(:DOJ,'MM/DD/YYYY HH:MI:SS AM')", sal CHAR TERMINATED BY WHITESPACE  "TO_NUMBER(:sal,’$99,999.99’)", FLAG TERMINATED BY WHITESPACE )

SQL*Loader Command     sqlldr userid=system/manager control=LGHR_employee_table.ctl log=LGHR_employee_table.log

  

Control file explanation: 1. LOAD DATA specifies the beginning of a new data load. 2. INFILE specifies the name of a file containing data that you want to load. 3. BADFILE specifies the name of a file into which rejected records are placed. 4. DISCARDFILE specifies the name of a file into which discarded records are placed. 5. APPEND for loading data into a table that is not empty. To load data into a table that is empty, you use the INSERT keyword. 6. INTO TABLE for identify tables, fields, and data types. It defines the relationship between records in the data file and tables in the database. 7. WHEN specifies one or more field conditions that each record must match. In this example SQL*Loader will only load the record if the 57th character is a decimal point. 8. TRAILING NULLCOLS tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns. 9. The remainder of the control file contains the field list, which provides information about column formats in the table that is being loaded.