By Administrator on 2010-01-24 16:50:32
Category - Data Loading
SQL Loader (sqlldr) - fields delimited by chr(1) or (^A) and records delimited by chr(2) or (^B)
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.


Please login first to post a comment on this thread OR to create a new thread...
Google best Oracle sites right from here >