Home » RDBMS Server » Server Utilities » Re: SQL Loader loads all fields with double quotes into staging table (split from unrelated hijacke
Re: SQL Loader loads all fields with double quotes into staging table (split from unrelated hijacke [message #473092] Wed, 25 August 2010 16:56 Go to next message
lachu23
Messages: 7
Registered: June 2010
Junior Member
I am having a similar problem like above ONLY in UNIX box where my datafile is delimited by "|". The last field is ITM_CMNT declared as VARCHAR2(60) in Oracle. When I have exactly 60bytes in the last field it rejects the record saying actual 61 and max allowed is 60. If i reduce it to < 60bytes then it is stored as a value enclosed with double quotes. The enclosing double quote is on the next line. Something like this...

"PROC,RAM,FLPY,HD,ACT MTX CLR DSP,D/PCMCIA,TRKBAL,LIT ION BA
"

Expected: the one below is exactly 60bytes.

PROC,RAM,FLPY,HD,ACT MTX CLR DSP,D/PCMCIA,TRKBAL,LIT ION BAT

LOAD DATA
INFILE * 
INTO TABLE TMPTLI_LAWSON_ITM_MST
TRUNCATE
FIELDS TERMINATED BY "|" 
(ITM_NO, HAZ_MAT_CD, ITM_SHRT_DS, ITM_SON "TRIM(:ITM_SON)", ADDED_DT DATE "YYYY-MM-DD", 
AVL_CD , ITM_CST_AMT, ITM_SLL_AMT, 
EXCHG_PRC_AMT , ITM_UOM "TRIM(:ITM_UOM)", 
PCK_QTY INTEGER EXTERNAL, SPC_HNDL_CD "TRIM(:SPC_HNDL_CD)", EFF_DT DATE "YYYY-MM-DD", 
ITM_CMNT "TRIM(:ITM_CMNT)")

[Updated on: Thu, 26 August 2010 00:49] by Moderator

Report message to a moderator

Re: SQL Loader loads all fields with double quotes into staging table [message #473093 is a reply to message #473092] Wed, 25 August 2010 17:10 Go to previous messageGo to next message
BlackSwan
Messages: 22513
Registered: January 2009
Senior Member
the last field is NOT terminated by "|"
Re: SQL Loader loads all fields with double quotes into staging table [message #473099 is a reply to message #473093] Wed, 25 August 2010 17:36 Go to previous messageGo to next message
lachu23
Messages: 7
Registered: June 2010
Junior Member
When we say terminated by "|" data should be A|B|C or A|B|C| ?
Re: SQL Loader loads all fields with double quotes into staging table [message #473100 is a reply to message #473099] Wed, 25 August 2010 17:50 Go to previous messageGo to next message
BlackSwan
Messages: 22513
Registered: January 2009
Senior Member
>When we say terminated by "|" data should be A|B|C or A|B|C| ?

why is easier for you to ask here instead of TESTING the results, YOURSELF?
Re: SQL Loader loads all fields with double quotes into staging table (split from unrelated hijacke [message #473112 is a reply to message #473092] Thu, 26 August 2010 00:51 Go to previous message
Michel Cadot
Messages: 58604
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you know you can write the colum,n list in several lines?
It is easier to read and debug with one field per line than all the fields in a single 1000 character line.

Regards
Michel
Previous Topic: SQL Loader
Next Topic: SQL loader
Goto Forum:
  


Current Time: Mon Jul 28 11:39:28 CDT 2014

Total time taken to generate the page: 0.10041 seconds