Home » Developer & Programmer » Data Integration » double quote character
double quote character [message #182216] Thu, 13 July 2006 16:28 Go to next message
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 #226420 is a reply to message #182216] Fri, 23 March 2007 11:16 Go to previous messageGo to next message
spinky
Messages: 21
Registered: March 2006
Junior Member
Could you please post it, if you got any solution for the same?
Re: double quote character [message #226561 is a reply to message #182216] Sun, 25 March 2007 02:32 Go to previous messageGo to next message
kiran
Messages: 503
Registered: July 2000
Senior Member
Your issue is on how to load these values in staging area?Can you be more clear on your question please...

-Kiran.
Re: double quote character [message #226597 is a reply to message #226561] Sun, 25 March 2007 10:12 Go to previous messageGo to next message
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 #226601 is a reply to message #226597] Sun, 25 March 2007 15:19 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you apply
SUBSTR(descriptor_column, 2, LENGTH(descriptor_column) - 2)
to the descriptor column?
Re: double quote character [message #226762 is a reply to message #226601] Mon, 26 March 2007 08:32 Go to previous messageGo to next message
spinky
Messages: 21
Registered: March 2006
Junior Member
How will I apply this in the Control file(which is run through SQL*LOADER)?
Re: double quote character [message #226865 is a reply to message #226762] Mon, 26 March 2007 16:02 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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
Now let's see how sample data looks like (note pipe delimiter and double quotes):
>type sample.txt
1|"My first record"
2|"My second record 1/2" for you "
This is the control file; note the SUBSTR function applied to the 'name' column:
>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
Finally, let's check what we have done:
>sqlplus scott/Tiger

SQL> select * from test;

        ID NAME
---------- --------------------------------------------------
         1 My first record
         2 My second record 1/2" for you

SQL>
Re: double quote character [message #227195 is a reply to message #226865] Tue, 27 March 2007 08:51 Go to previous message
spinky
Messages: 21
Registered: March 2006
Junior Member
Great!!!
Thank You.
Previous Topic: OWB 10.2 Books
Next Topic: Error Message Warehouse Builder:
Goto Forum:
  


Current Time: Thu Mar 28 16:12:59 CDT 2024