OraFAQ Forum: Server Utilities » escape single quotes using escape character in sql loader

Home » RDBMS Server » Server Utilities » escape single quotes using escape character in sql loader

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

escape single quotes using escape character in sql loader [message #437161]

Thu, 31 December 2009 06:41

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

Report message to a moderator

Re: escape single quotes using escape character in sql loader [message #437164 is a reply to message #437161]

Thu, 31 December 2009 07:54

Littlefoot
Messages: 21823
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>

Report message to a moderator

Previous Topic:	How to "clean" Oracle database before importing
Next Topic:	import from split

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 11 03:46:27 CST 2025