now, tweaked the data file slightly different.
using "\n" to represent the new line character and then from the control file while loading tried to replace "\n" with chr(13)||chr(10)
this approach doesn't work from sql*loader. could you help us to understand?
So here is my control files and data files.
demo@ORA12C> $type d:\ctl2.txt
load data
into table t
truncate
fields terminated by ','
( x ,
y ,
z "replace(:z,'\n',chr(13)||chr(10))")
demo@ORA12C> $type d:\data2.txt
"1","test1","test1"
"2","test2","TESt double quotes \n""NEW"" REgardssdfdsfdsf \nPrasad"
"3","test3","test4"
demo@ORA12C>
here is my data load.
C:\Users\179818>sqlldr demo/demo@ora12c control=d:\ctl2.txt log=d:\log.txt data=d:\data2.txt
SQL*Loader: Release 12.1.0.2.0 - Production on Sat Mar 11 09:02:22 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 2
Commit point reached - logical record count 3
Table T:
3 Rows successfully loaded.
Check the log file:
d:\log.txt
for more information about the load.
C:\Users\179818>exit
demo@ORA12C> select * from t;
X Y Z
---------- -------------------- ----------------------------------------
"1" "test1" "test1"
"2" "test2" "TESt double quotes \n""NEW"" REgardssdf
dsfdsf \nPrasad"
"3" "test3" "test4"
demo@ORA12C>
you could see the "\n" is not getting replaced during loads.
but when tried to invoke this replace from sql it works fine.
demo@ORA12C> column x format a40
demo@ORA12C> select z, replace(z,'\n',chr(13)||chr(10)) x
2 from t ;
Z X
---------------------------------------- ------------------------------
"test1" "test1"
"TESt double quotes \n""NEW"" REgardssdf "TESt double quotes
dsfdsf \nPrasad" ""NEW"" REgardssdfdsfdsf
Prasad"
"test4" "test4"
demo@ORA12C>
while the sql*loader log file shows this.
Table T, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
X FIRST * , CHARACTER
Y NEXT * , CHARACTER
Z NEXT * , CHARACTER
SQL string for column : "replace(:z,'
',chr(13)||chr(10))"
don't understand why the replace() command appear this way in the log file ( the second argument to the replace command "\n" is not shown) why ?