Home » RDBMS Server » Server Utilities » SQL*Loader Issue
SQL*Loader Issue [message #124718] Tue, 21 June 2005 01:57 Go to next message
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 Go to previous messageGo to next message
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 #124727 is a reply to message #124723] Tue, 21 June 2005 02:50 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Hi Barbara,
Is there any problem, if the data contains ','?

by
vamsi
Re: SQL*Loader Issue [message #124730 is a reply to message #124723] Tue, 21 June 2005 03:01 Go to previous messageGo to next message
krishna_900
Messages: 11
Registered: June 2005
Junior Member

Dear Barbara,

Thanks for Quick solution.
But we do have one problem here. Input data may contain comma character (",") in between. Thatz the reason for surrounding double quotes.

Thanks & Regards
Krishna

Re: SQL*Loader Issue [message #124731 is a reply to message #124727] Tue, 21 June 2005 03:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
Senior Member
Yes, that would be a problem, since the comma is your delimiter. I don't know whether you have any control over what format you get the data in, but if you can get it in a fixed format file, instead of delimited, that would work.
Re: SQL*Loader Issue [message #124797 is a reply to message #124730] Tue, 21 June 2005 10:13 Go to previous message
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
Previous Topic: export and import huge amount of records
Next Topic: Catexp.sql version 9
Goto Forum:
  


Current Time: Wed Dec 25 19:40:45 CST 2024