About SqlLoader

SqlLoader is a utility provided by oracle, for loading data into database. This is analogous to db2export/import provided by db2. Most databases provide such utilities. Typical applications of such utilities: Migration of data (data only), creating a file based backup (as disk space is cheaper than the SAN disk space).

There are two prominent types of sql loader functionality.

• Conventional loading
  
• Direct path loading
  

Conventional loading

Sample command:

 msg=`sqlldr $oracleuser/$oraclepasswd@$ORACLE_SID control=${controlFile} data=${csvFile} direct=false bad=${badFile} log=${logFile} errors=${errors} rows=100000`

Conventional load acts as if someone is firing insert commands using oracle client. The indexes are preserved. Other users are not affected while the load happens.

Direct path loading

Sample command: (Note the direct=true part)

 msg=`sqlldr $oracleuser/$oraclepasswd@$ORACLE_SID control=${controlFile} data=${csvFile} direct=true bad=${badFile} log=${logFile} errors=${errors} skip_index_maintenance=false `

Sqlloader tries to write data directly into database, we can configure it to skip logging, bypass indices and write data directly. However this affects other users/transactions that are trying to read/write data to the same table.

Behind the scenes

Sql loader takes two major inputs. Control file, that dictates the nature of load. Data file that contains the data.
Sample control file for direct path load:

unrecoverable
load data
 APPEND

 into table vendor_data.ds_sp_data
 REENABLE DISABLED_CONSTRAINTS
 fields terminated by "," optionally enclosed by '|'
 trailing nullcols
(type_id FILLER, FEED_DATA_SOURCE FILLER, LINE_NUM, batch_id,  corp_actions_id, split_factor, ric CHAR(20) , record_type CHAR(2) , ds_type_id CHAR(8) , adjust_volume CHAR(1) , ds_file_date DATE "YYYY-MM-DD", split_date DATE "YYYY-MM-DD", file_code CHAR(4) , file_ext CHAR(1) , split_factor_val)

Sample control file for conventional load

OPTIONS (SKIP=1)
load data
 APPEND
 into table vendor_data.feed_run_status_record_type
REENABLE DISABLED_CONSTRAINTS
fields terminated by "," optionally enclosed by '|'
trailing nullcols
(ETL_SEP FILLER,RUN_ID, TYPE_ID, REGION_CODE, FEED_ID, FILE_NAME CHAR(255), RECORD_TYPE, RECORD_COUNT, FAILURE_COUNT, INSERT_COUNT,  STATUS_CODE, EXCEPTION CHAR(3000))

Sample of data loaded by the above:
(Using '------' as line separator of visual illustration only, it is not part of the data)

|21825|,|/u/datamgmt/TEST6/data/datascope/data//18251217.M|,|2|,|414146|,|SFE|,||,|1|,|21825|,|IRF|,|FUT|,|XSFE|,|M|,|NBBc1=|,|RY|,||,|NZ BANKBILL DEC9|,|2010-12-17|,|N|,|1825|,|0|
--------
|21825|,|/u/datamgmt/TEST6/data/datascope/data//18251217.M|,|3|,|414146|,|SFE|,||,|1|,|21825|,|IRF|,|FUT|,|XSFE|,|M|,|NBBc2=|,|RY|,||,|NZ BANKBILL MAR0|,|2010-12-17|,|N|,|1825|,|0|
--------
|21825|,|/u/datamgmt/TEST6/data/datascope/data//18251217.M|,|4|,|414146|,|SFE|,||,|1|,|21825|,|IRF|,|FUT|,|XSFE|,|M|,|NBBc3=|,|RY|,||,|NZ BANKBILL JUN0|,|2010-12-17|,|N|,|1825|,|0|

Note:

1. 'unrecoverable' is supported only in direct path load
   
2. APPEND is faster than INSERT
   
3. FILLER columns are skipped
   
4. CHAR(x) fields help sqlldr load fields that exceed default varchar limit of 127
   
5. SKIP indicates the number of lines to skip
   

A good article on SqlLoader