sql loader- files with carriage return, line feed load into oracle with cr/lf

Question

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.

Answer 1

Here is the solution that works for me. Instead of replacing the carriage return/line feed(cr/lf) in the extracted flat file with [#crlf#] I retain the cr/lf in the extracted data file. And then I changed my ctl file to handle the cr/lf with INFILE Clause with file name and " str '\n' ". For Unix env we need \n where in for windows we can use either \n or \r\n.

see below

load data INFILE 'filename.dat' "str '\n'" 
    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#]')

I tested it and data loaded with cr\lf.. I need to do more detailed testing, as of now I have tested one table I have many more. Meanwhile if any one has better solution I would be more than happy to try it out.