Often while using SQL loader we are faced with situation where the data file you have has special character specified by chr(1) or (^A) as fields delimiter and at the same time special character chr(2) or (^B).
To resolve this, we need to specifiy the two delimiters (fields and rows) in your control file using the following -
-----------------------------------------------
OPTIONS(LOAD=10)
LOAD DATA
INFILE 'data.txt' "str '^B'" -- Your data file
BADFILE 'data.bad'
DISCARDFILE 'data.dsc'
DISCARDMAX 1000
APPEND
INTO TABLE mytable
FIELDS TERMINATED BY '^A'
TRAILING NULLCOLS
(
your comma separated column names here ...
)
-----------------------------------------------
While typing this control file in linux say in your "vi" editor use this key sequence - V
Now, once your run this you will load data from data file correctly using the above specified delimiteres.
|