The SQL Loader is a facility that allows you to load data files blazingly fast. It is able to do as data files are directly written to disk without any overhead.
It needs two files: a control file and a data file. The process generates a log file, that provides information on whether process has succeeded or not. It also generates a file on records that could not be loaded and a file that indicates whether some files could not be used.
The sql loader is invoked at terminal level by a command that given on the server. It may look like:
SQLLDR CONTROL=C:\Users\Administrator\Dropbox\Uitwisseling\gg_DATA_TABLE.ctl, LOG=sample.log, BAD=baz.bad, DATA=C:\Users\Administrator\Dropbox\Uitwisseling\gg_DATA_TABLE.ldr USERID=scott/binvegni, ERRORS=999, LOAD=500000, DISCARD=toss.dsc, DISCARDMAX=5
In this command, ome may distinguish the control file that ends on .ctl. Likewise the data file can de seen as ending on .ldr. Another feature is the limit on the number of records. Setting this number at a low level, invokes only a low number of records being loaded.
Luckily for us, the common Oracle clients, like SQL Developer and TOAD are able to generate these files automatically. Hence the files are generated and only some trivial actions must be undertaken such as copying the files in the correct location (accessible from teh server) and aboven SQL Loader script should be adjusted in accordance with the directory- and file names.
Playing with this facility showed me that varchar fields were easy to load, but with both numerical fields and date files one may easily run into issues as the layout between different instances might be different. For me, it took quite some time to adjust the scripts as to have date fields and numeric fields being loaded without issues. I then discovered the value of the log file. Allthough exact reason for a rejected record was difficult to detect, it gave ample information on the records that were rejected and the fields that started the rejection.
Just as an example, let me add the final code:
OPTIONS (SKIP=1) LOAD DATA TRUNCATE INTO TABLE "SCOTT"."ONLINEFORECAST" FIELDS TERMINATED BY'|' OPTIONALLY ENCLOSED BY '"' AND '"' TRAILING NULLCOLS ( "NASA_NUM" , "DATUM" DATE "DD-MM-YY" , "SUM_CE_AANTAL" , "AANTAL_ORDERS" , "AANTAL_MET_ACTIE_IND" "to_number(:AANTAL_MET_ACTIE_IND,'99999.99')" )
To finalise, another SQL Loader job I recently created:
ctl – Copy