OraFAQ Forum: Server Utilities » SQL Loader and dates

Home » RDBMS Server » Server Utilities » SQL Loader and dates

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: SQL Loader and dates [message #231944 is a reply to message #231942]

Wed, 18 April 2007 21:58

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: SQL Loader and dates [message #231945 is a reply to message #231944]

Wed, 18 April 2007 22:05

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

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

"Jun 15 2001 "
I was refering to the space characters above & NOT in your mask.

Report message to a moderator

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

Report message to a moderator

Re: SQL Loader and dates [message #231949 is a reply to message #231942]

Wed, 18 April 2007 22:19

BlackSwan
Messages: 26766
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.

Report message to a moderator

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

Report message to a moderator

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?

Report message to a moderator

Previous Topic:	sql * loader
Next Topic:	IMP quest

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 02 15:51:18 CDT 2024