Need some suggestion in here. sqlldr 10.2.0.5
User csv file ( e.g. user.csv ) includes many columns. column16 is a text field and do not required to sqlldr to table. The user.csv is long.
It has CR ( carriage return ) & LF ( line feed ) as end of file.
load data
infile "SONO_input_file_name" "str x'0D0A'"
DISCARDFILE Discard_data.txt
truncate into table CUSTOMTABLE
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
(
,c15 FILLER
,c16 FILLER char(20000)
,c17 FILLER
,complaint
,c19 FILLER
,notes char(4000) "replace(:notes, chr(10), ' ')"
)
the sqlldr failed with the following error : Record 986: Rejected - Error on table CUSTOMTABLE column C16. second enclosure string not present
By reading the User.csv. The second enclosure string is there but the column16 (TEXT column ) has CR & LF as part of the string. So I think sqlldr thinks that is a end of line. so I did this
load data
infile "SONO_input_file_name" "str x'0D0A'"
DISCARDFILE Discard_data.txt
truncate into table CUSTOMTABLE
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
(
,c15 FILLER
,c16 FILLER char(20000) "replace(:c16, chr(13),'')"
,c17 FILLER
,complaint
,c19 FILLER
,notes char(4000) "replace(:notes, chr(10), ' ')"
)
but I got this error :
SQL*Loader-350: Syntax error at line 24.
Expecting "," or ")", found "replace(:notes, chr(10),'')".
,c16 FILLER char(20000) "replace(:notes, chr(10),'')"
^
So ... what should I do? any good suggestion?