Home » RDBMS Server » Server Utilities » escape double-quotes in sqlldr
icon4.gif  escape double-quotes in sqlldr [message #234474] Tue, 01 May 2007 13:26 Go to next message
ozzy80
Messages: 55
Registered: June 2005
Member
Hello,

My data file has double-quotes (") and single-quotes (') in it. They represent IN and FT, respectivily.

I tryed using the REPLACE function in the control file, but the getting error with the double-quote...

Sample data from data file:
:116780:,:2"X12"-14' DF/L #2&BTR S-DRY   S4S:,:


Contril file:
LOAD DATA
  CHARACTERSET 'UTF8'
  LENGTH SEMANTICS CHAR
  INFILE 'd:\batch\data\ITEM.dat'
  BADFILE 'd:\batch\data\ITEM.bad'
  DISCARDFILE 'd:\batch\data\ITEM.dsc'
  APPEND INTO TABLE ITEM
  FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY ':'
  TRAILING NULLCOLS
(ITEM
,DESCR "replace(replace(:DESCR,'"','IN'),'''','FT')"
 ) 


Error:
SQL*Loader: Release 10.2.0.1.0 - Production on Tue May 1 14:24:43 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL*Loader-350: Syntax error at line 19.
Expecting "," or ")", found ",".
,DESCR                      "replace(replace(:DESCR,'"','IN'),'''','FT')"
                                         ^


Please let me know how can I achieve this in the control file.

Let me know if you have any questions.

Thanks,
~Ozzy
Re: escape double-quotes in sqlldr [message #234475 is a reply to message #234474] Tue, 01 May 2007 13:35 Go to previous messageGo to next message
ozzy80
Messages: 55
Registered: June 2005
Member
Thanks, I got my answer... if anyone interested...

DESCR "replace(replace(:DESCR,'\"','IN'),'''','FT')"

the '\' will help you escape the double-quotes..

~Ozzy


[EDITED by LF: removed unnecessary quote of the whole previous message]

[Updated on: Tue, 28 October 2014 01:14] by Moderator

Report message to a moderator

Re: escape double-quotes in sqlldr [message #626422 is a reply to message #234475] Mon, 27 October 2014 14:28 Go to previous message
alcalina
Messages: 3
Registered: August 2011
Junior Member
It helped me a lot. tks very much
Previous Topic: Is there a way to import different database dump into a RAC database
Next Topic: Import taking forever to finish
Goto Forum:
  


Current Time: Sun Dec 11 00:34:24 CST 2016

Total time taken to generate the page: 0.04631 seconds