SQL Loader and dates [message #231942] |
Wed, 18 April 2007 21:45 |
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 #231948 is a reply to message #231946] |
Wed, 18 April 2007 22:14 |
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 #231950 is a reply to message #231949] |
Wed, 18 April 2007 22:23 |
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 |
|
BlackSwan
Messages: 26766 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?
|
|
|