SQL*Loader Issue [message #124718] |
Tue, 21 June 2005 01:57 |
krishna_900
Messages: 11 Registered: June 2005
|
Junior Member |
|
|
Hi,
Control file structure.
LOAD DATA
INFILE 'tardis.txt'
BADFILE 'tardisbad.txt'
DISCARDFILE 'tardisdis.txt'
TRUNCATE
INTO TABLE TARDIS
WHEN ORGANISATION_ADDRESS !=BLANKS
FIELDS TERMINATED BY ',' optionally enclosed BY '"'
TRAILING NULLCOLS
(FSA_REGULATED_FIRM_ORDER_ID SEQUENCE(MAX,1),
FSA_REFERENCE_NUMBER,
ORGANISATION_NAME ,
ORGANISATION_ADDRESS CHAR(1000)
)
Input file data
312779,"Rescue "U" Limited","5b Vermont House Washington Tyne and Wear NE37 2SQ United Kingdom"
Since delimiter character (“) is repeated in between the attribute data sql loader is rejecting the record.
Any thoughts to handle this situation?
Thanks & Regards
Krishna
|
|
|
Re: SQL*Loader Issue [message #124723 is a reply to message #124718] |
Tue, 21 June 2005 02:36 |
|
Barbara Boehmer
Messages: 9101 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You could remove the optionally enclosed by '"' from your control file, and use a function to remove the double qutoes, as shown below.
-- test.ctl:
LOAD DATA
INFILE 'tardis.txt'
BADFILE 'tardisbad.txt'
DISCARDFILE 'tardisdis.txt'
TRUNCATE
INTO TABLE TARDIS
WHEN ORGANISATION_ADDRESS !=BLANKS
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(FSA_REGULATED_FIRM_ORDER_ID SEQUENCE(MAX,1),
FSA_REFERENCE_NUMBER,
ORGANISATION_NAME "no_quotes (:ORGANISATION_NAME)",
ORGANISATION_ADDRESS "no_quotes (:ORGANISATION_ADDRESS)"
)
scott@ORA92> CREATE TABLE tardis
2 (FSA_REGULATED_FIRM_ORDER_ID NUMBER,
3 FSA_REFERENCE_NUMBER NUMBER,
4 ORGANISATION_NAME VARCHAR2(25),
5 ORGANISATION_ADDRESS VARCHAR2(70))
6 /
Table created.
scott@ORA92> CREATE OR REPLACE FUNCTION no_quotes
2 (p_string IN VARCHAR2)
3 RETURN VARCHAR2
4 AS
5 BEGIN
6 RETURN LTRIM (RTRIM (p_string, '"'), '"');
7 END no_quotes;
8 /
Function created.
scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log
scott@ORA92> SELECT * FROM tardis
2 /
FSA_REGULATED_FIRM_ORDER_ID FSA_REFERENCE_NUMBER ORGANISATION_NAME ORGANISATION_ADDRESS
--------------------------- -------------------- ------------------------- ----------------------------------------------------------------------
1 312779 Rescue "U" Limited 5b Vermont House Washington Tyne and Wear NE37 2SQ United Kingdom
scott@ORA92>
|
|
|
|
|
|
Re: SQL*Loader Issue [message #124797 is a reply to message #124730] |
Tue, 21 June 2005 10:13 |
|
vamsi kasina
Messages: 2112 Registered: October 2003 Location: Cincinnati, OH
|
Senior Member |
|
|
Hi krishna,
If you pass the data as
312779,"Rescue ""U"" Limited","5b Vermont House Washington Tyne and Wear NE37 2SQ United Kingdom"
It should work.
by
vamsi
|
|
|