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)