Using UD8/CSV with Oracle SQL*Loader

UD8/CSV is ideal for use in data conversion with Oracle. If you use Unfiace to load directly into Oracle, you have very little control over errors, however using UD8/CSV you can re-run the same load time and again, quickly and easily. And since SQL*Loader will typically load the same data from UD8/CSV files much faster than Oracle/Uniface will from TRX or another format.

To govern the way that SQL*Loader interprets a delimited text file you should use a control file.

The control file specifies the separator and the format of dates etc. March Hare recommend that you choose a separator that is unlikely to appear in your data, and use the alert parameter of USYS$UD8_PARAMS to notify you if the separator you have chosen does appear in your data.

Example control file:

LOAD DATA
INFILE 'MASTER.csv'
INTO TABLE TMP_MASTER
FIELDS TERMINATED BY "¶"
TRAILING NULLCOLS
(
MS_SEQ SEQUENCE(1,1),
MS_ITERATION CONSTANT 0,
MS_CONVERSION_STATUS CONSTANT " ",
MS_SORT,
MS_BNAME,
MS_BTITLE,
MS_BADDR1,
MS_BADDR2,
MS_BADDR3 ,
MS_BPCODE  ,
MS_BW_PHONE ,
MS_BH_PHONE  ,
MS_B_FAX,
MS_GTITLE,
MS_GSORT  ,
MS_GNAME,
MS_GADDR1,
MS_GADDR2 ,
MS_GADDR3,
MS_GPCODE ,
MS_GW_PHONE,
MS_GH_PHONE,
MS_G_FAX   ,
MS_WDATE  "TO_DATE(:MS_WDATE,'YYYYMMDD')",
MS_RADDR1,
MS_RADDR2 ,
MS_RADDR3  "TO_DATE(:MS_RADDR3,'YYYYMMDD')" ,
MS_RPCODE,
MS_DADDR1,
MS_DADDR2 ,
MS_DADDR3  ,
MS_DPCODE,
MS_INST ,
MS_INST2 ,
MS_BP_NAME,
MS_BP_PHONE,
MS_GP_NAME  ,
MS_GP_PHONE  ,
MS_RDATE   "TO_DATE(:MS_RDATE,'YYYYMMDD')" ,
MS_STORE,
MS_GCNT,
MS_RCNT,
MS_RAMT,
MS_BCNT ,
MS_BAMT  ,
MS_DELCLOSE  "TO_DATE(:MS_DELCLOSE,'YYYYMMDD')" ,
MS_OCCASION)