| double quote character [message #182216] |
Thu, 13 July 2006 16:28  |
murur
Messages: 4
Registered: April 2006
|
Junior Member |
|
|
we are getting data files with data value delimited by | (Pipe) character and string values are enclosed by double quotes (")
but there are description column which has double quotes inside as valid character.
Example :
|"38560 BIAS CUT 1 1/2" "|
Is anyone faced similar issue? any idea to resolve this issue
|
|
|
|
|
|
|
|
| Re: double quote character [message #226597 is a reply to message #226561] |
Sun, 25 March 2007 10:12  |
spinky
Messages: 21
Registered: March 2006
|
Junior Member |
|
|
|
Yes, I would like to know how we are going to load this in oracle staging table, because I guess, in the above example if we give OPTIONALLY ENCLOSED BY ' " ' in the control file, then SQL*LOADER doesn't even consider this |"38560 BIAS CUT 1 1/2" "| while loading into table.
|
|
|
|
|
|
|
|
| Re: double quote character [message #226865 is a reply to message #226762] |
Mon, 26 March 2007 16:02 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Here you are: first, this is the table to be populated:SQL> desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(50)
SQL> exit >type sample.txt
1|"My first record"
2|"My second record 1/2" for you " >type test.ctl
load data
infile 'sample.txt'
replace
into table test
fields terminated by "|"
(id ,
name "substr(:name, 2, length(:name) - 2)"
)
OK, let's load it:>sqlldr scott/tiger@ora10 control=test.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Pon O×u 26 22:56:48 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 2 >sqlplus scott/Tiger
SQL> select * from test;
ID NAME
---------- --------------------------------------------------
1 My first record
2 My second record 1/2" for you
SQL> |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
