Home » RDBMS Server » Server Utilities » escape single quotes using escape character in sql loader
escape single quotes using escape character in sql loader [message #437161] Thu, 31 December 2009 06:41 Go to next message
sitesh.mukherjee
Messages: 5
Registered: December 2009
Location: India
Junior Member
All,

I've the data like below

'E001','RAM'
'E002','SHYAM's'

Now I want to load this data into the stage table like below.

EMPNO ENAME
----- -----
E001 RAM
E002 SHYAMS

I'm using the following in the control file.

INFILE '<INFILE>'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY "'"
TRAILING NULLCOLS
(
EMPNO
,ENAME "REPLACE(:ENAME,'\'','')"
)

But it is not works. Giving error like below.
ORA-01756: quoted string not properly ended

Can anybody help me to get rid of this situation ASAP!!!

Thanks in advance
Sitesh
Re: escape single quotes using escape character in sql loader [message #437164 is a reply to message #437161] Thu, 31 December 2009 07:54 Go to previous message
Littlefoot
Messages: 20827
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You are out of luck. Kind of.

You can't use OPTIONALLY ENCLOSED BY <single quote> when there's a single quote character within the input string(s).

Therefore, a possible workaround might be removing OPTIONALLY ENCLOSED from the control file and removing single quotes off the input strings. Something like this:

A test table:
SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------
 EMPNO                                              VARCHAR2(4)
 ENAME                                              VARCHAR2(20)

Contents of the control file:
SQL> $type test.ctl
load data
infile *
replace

into table test
fields terminated by ','
   (empno char "substr(:empno, 2, length(:empno) - 2)",
    ename char "substr(:ename, 2, length(:ename) - 2)"
    )

begindata
'E001','RAM'
'E002','SHYAM's'

SQL*Loader session:
SQL> $sqlldr scott/tiger control=test.ctl log=test.log

SQL*Loader: Release 10.2.0.1.0 - Production on Cet Pro 31 14:53:13 2009

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

Commit point reached - logical record count 1
Commit point reached - logical record count 2

What we've done:
SQL> select * from test;

EMPN ENAME
---- --------------------
E001 RAM
E002 SHYAM's

SQL>
Previous Topic: How to "clean" Oracle database before importing
Next Topic: import from split
Goto Forum:
  


Current Time: Wed Sep 28 07:30:12 CDT 2016

Total time taken to generate the page: 0.27207 seconds