I have data extract from sql server where data in most columns have carriage return and line feeds. I need to load them into oracle with the carriage return and line feed; basically I have to mirror the data form sql server 2012 to oracle 11g.
below is the sample of my extract file
[#BOR#][#EOC#]109[#EOC#]4[#EOC#]testdata_Duplicate[#EOC#]testdata_Duplicate from chat[#EOC#]this
is
carriage return field[#EOC]test2[#EOR#]
Here [#EOC#] is column delimiter, [#EOR#] is row delimiter. [#BOR#] indicates the beginning of row. Initially my loads failed to due to blank lines in the flat file(data extract). Then I used [#BOR#] with continueIf preserve clause so that sqlldr will not treat blank lines(cr/lf) as physical row.
with [#BOR#] as a filler column my load works fine but carriage return or line feed are not loaded into oracle tables.
My ctl file is as below
load data
truncate CONTINUEIF NEXT preserve (1:7) <> "[#BOR#]"
into table sch1.tbl1 fields
terminated by '[#EOC#]' trailing nullcols
( field filler,
a_id integer external,
h_id integer external,
title char(128),
descn char(4000),
risk char(4000),
comment char(4000) terminated by '[#EOR#]')
In oracle sch1.tbl1 table column risk has data as 'this is carriage return field' instead of
'this
is
carriage return field'
I tried to replace char(10) with string [#crlf#] and use replace function in ctl like as below
load data
truncate CONTINUEIF NEXT preserve (1:7) <> "[#BOR#]"
into table sch1.tbl1
fields terminated by '[#EOC#]'
trailing nullcols
( field filler,
a_id integer external,
h_id integer external,
title char(128),
descn char(4000),
risk char(4000) "replace(:risk,[#crlf#],chr(10))"
comment char(4000) terminated by '[#EOR#]')
the sql loader errors out stating SQL*Loader-309: No SQL string allowed as part of field specification; I believe because my columns are CLOB data type I am not able to use replace function.
Please help me to load data from sql server with cr/lnFeed into oracle tables using sqlloader. Thank you in advance.