Home » RDBMS Server » Server Utilities » SQL Loader and dates
SQL Loader and dates [message #231942] Wed, 18 April 2007 21:45 Go to next message
DMcG
Messages: 51
Registered: May 2005
Location: Auckland, New Zealand
Member
Ok, this one's had me scratching my head for a few hours now.

Data looks like this -
1,UT Welcome Pack,Unit Trust Welcome Investment Pack,,Jun 15 2001  2:29:15:000PM,new 2.3,0000000000d211d0
2,MF Welcome Pack,Master Fund Welcome Investment Pack,,Jun 15 2001  2:29:15:000PM,new 2.3,0000000000d211d6


Table looks like this -
 CREATE TABLE TSCM_LETTER_PACK
   (	LPAK_ID NUMBER(10,0), 
	LPAK_NAME VARCHAR2(15), 
	LPAK_DESCRIPTION VARCHAR2(250), 
	LPAK_EXTRACT_TEMPLATE_ID NUMBER(10,0), 
	LPAK_LAST_UPDATED TIMESTAMP (6), 
	LPAK_LAST_UPDATED_BY VARCHAR2(30), 
	LPAK_TIMESTAMP NUMBER(20,0)
   ) ;


Control file looks like this
load data
infile 'C:\temp\test\tscm_letter_pack.dat'
badfile 'C:\temp\test\tscm_letter_pack.bad'
discardfile 'C:\temp\test\tscm_letter_pack.dis'
truncate into table tscm_letter_pack
fields terminated by ','
(LPAK_ID INTEGER
,LPAK_NAME CHAR
,LPAK_DESCRIPTION CHAR
,LPAK_EXTRACT_TEMPLATE_ID INTEGER
,LPAK_LAST_UPDATED TIMESTAMP(3) "MonDDYYYYHH:MI:SS:FF3PM"
,LPAK_LAST_UPDATED_BY CHAR
,LPAK_TIMESTAMP INTEGER
)

I keep getting a "ORA-01843: not a valid month" error.
If I strip out the columns up to the date - it loads, so I'm gueesing it's something to do with position, but since the strings before it are varchar's I can't predict what position the date will start at.

Any ideas ?


TIA

Dougie
Re: SQL Loader and dates [message #231944 is a reply to message #231942] Wed, 18 April 2007 21:58 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
I don't think your mask matches your data.
Jun 15 2001 2:29:15:000PM
MonDDYYYYHH:MI:SS:FF3PM

Notice the difference?
How many records are read, accepted, and/or bad?
Re: SQL Loader and dates [message #231945 is a reply to message #231944] Wed, 18 April 2007 22:05 Go to previous messageGo to next message
DMcG
Messages: 51
Registered: May 2005
Location: Auckland, New Zealand
Member
No luck. I tried changing the time to 10:29 to get rid of the leading blank but it didn't make any difference. I'm getting a 100% rejection rate Sad.

Dougie

[Updated on: Wed, 18 April 2007 22:05]

Report message to a moderator

Re: SQL Loader and dates [message #231946 is a reply to message #231942] Wed, 18 April 2007 22:08 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
"Jun 15 2001 "
I was refering to the space characters above & NOT in your mask.
Re: SQL Loader and dates [message #231948 is a reply to message #231946] Wed, 18 April 2007 22:14 Go to previous messageGo to next message
DMcG
Messages: 51
Registered: May 2005
Location: Auckland, New Zealand
Member
Sorry,

Tried that too - still no joy. The original mask had spaces in it but I'm thought I saw something about SQL Loader removing whitespace, so I took them out.

Dougie
Re: SQL Loader and dates [message #231949 is a reply to message #231942] Wed, 18 April 2007 22:19 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
I don't like inconsistencies.
LPAK_LAST_UPDATED TIMESTAMP (6)
LPAK_LAST_UPDATED TIMESTAMP (3)

If this were really my problem.
I'd be testing with a single column TIMESTAMP field & a single row to be loaded from a test file.
http://asktom.oracle.com might help, then again might not.
Re: SQL Loader and dates [message #231950 is a reply to message #231949] Wed, 18 April 2007 22:23 Go to previous messageGo to next message
DMcG
Messages: 51
Registered: May 2005
Location: Auckland, New Zealand
Member
Single column timestamp works fine.

That's the problem - stick a char string in front of it and it fails every time. Every example I've found using dates has them as the first value loaded - which is what makes me suspect it's something about positioning.

Dougie
Re: SQL Loader and dates [message #231956 is a reply to message #231942] Wed, 18 April 2007 22:49 Go to previous message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
Ready, fire, aim
load data
infile 'C:\temp\test\tscm_letter_pack.dat'
badfile 'C:\temp\test\tscm_letter_pack.bad'
discardfile 'C:\temp\test\tscm_letter_pack.dis'
truncate into table tscm_letter_pack
fields terminated by ','
(LPAK_ID
,LPAK_NAME
,LPAK_DESCRIPTION 
,LPAK_EXTRACT_TEMPLATE_ID 
,LPAK_LAST_UPDATED TIMESTAMP "Mon DD YYYY HH:MI:SS:FF3PM"
,LPAK_LAST_UPDATED_BY 
,LPAK_TIMESTAMP )

Does this change anything?
Previous Topic: sql * loader
Next Topic: IMP quest
Goto Forum:
  


Current Time: Mon Dec 05 21:31:59 CST 2016

Total time taken to generate the page: 0.13283 seconds