OraFAQ Forum: Server Utilities » Problem with milliseconds in sqlldr

Home » RDBMS Server » Server Utilities » Problem with milliseconds in sqlldr

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

Problem with milliseconds in sqlldr [message #170862]

Fri, 05 May 2006 09:59

Zakkhalid
Messages: 47
Registered: April 2005

Member

Hi
I'm trying to load a timestamp 2006-04-20 08:50:06,971 using a control file

so
table x
(timstamp date))

Data
2006-04-20 08:51:13,739
2006-04-20 08:51:39,532
2006-04-20 08:59:51,112
2006-04-20 09:00:04,327
2006-04-20 09:03:43,020
2006-04-20 09:15:40,099

and the controlfile has the following line,

timestamp DATE "YYYY-MM-DD HH24:MI:SS" "TO_DATE(TO_CHAR(SUBSTR(:TIMESTAMP,1,19),'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS
')"

but it returns ORA-01722: invalid number

thanking you all inadvance

Report message to a moderator

Re: Problem with milliseconds in sqlldr [message #171746 is a reply to message #170862]

Thu, 11 May 2006 05:57

Frank Naude
Messages: 4580
Registered: April 1998

Senior Member

Try this:

SQL> create table x (timestamp date);

Table created.

load data
infile 'x.dat'
append into table x
fields terminated BY ','
(
  timestamp DATE "YYYY-MM-DD HH24:MI:SS"
)

sqlldr scott/tiger control=x.ctl

SQL*Loader: Release 10.2.0.2.0 - Production on Thu May 11 12:42:25 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 6

SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> select * from x;

TIMESTAMP
-------------------
2006-04-20 08:51:13
2006-04-20 08:51:39
2006-04-20 08:59:51
2006-04-20 09:00:04
2006-04-20 09:03:43
2006-04-20 09:15:40

6 rows selected.

Report message to a moderator