OraFAQ Forum: Server Utilities » SQL*LOAD problem

Home » RDBMS Server » Server Utilities » SQL*LOAD problem - loading date/time

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

SQL*LOAD problem - loading date/time [message #162271]

Thu, 09 March 2006 08:23

pday@tullib.com
Messages: 11
Registered: March 2006
Location: London

Junior Member

Hi

I'm having problems loading a date into my table.

The col in the table is defined as a date.

The ctl file line looks like this

local_entry_date DATE 'DD/MM/YYYY HH:MI',

the data in the flat file looks like this

10-Oct-2005 15:40

and the error in the log file is

Record 1: Rejected - Error on table TRAX_FINES, column LOCAL_ENTRY_DATE.
ORA-01858: a non-numeric character was found where a numeric was expected

I've tried various permatations but nothing works.

If I remove the time element from the data file and set the col to a date the data loads fine. Trouble is I need the time element to load.

Any help gratefully received.

thanks

Report message to a moderator

Re: SQL*LOAD problem - loading date/time [message #162272 is a reply to message #162271]

Thu, 09 March 2006 08:33

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

You need to apply proper formatting

oracle@mutation#sqlldr userid=scott/tiger control=date.ctl

SQL*Loader: Release 9.2.0.4.0 - Production on Thu Mar 9 09:33:09 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Commit point reached - logical record count 3
oracle@mutation#query mutation scott.date_table

C1
---------
10-OCT-05
11-OCT-05
10-OCT-05

oracle@mutation#cat date.ctl
LOAD DATA
infile 'date.data' truncate
INTO TABLE date_table
FIELDS TERMINATED BY "," trailing nullcols
(
c1 "TO_DATE(:c1,'DD-mon-YYYY HH24:MI')"
)
oracle@mutation#cat date.data
10-Oct-2005 15:40
11-Oct-2005 15:40
10-Oct-2005 15:43

Report message to a moderator

Re: SQL*LOAD problem - loading date/time [message #162277 is a reply to message #162271]

Thu, 09 March 2006 08:53

pday@tullib.com
Messages: 11
Registered: March 2006
Location: London

Junior Member

Thanks

because of earlier errors I thought the mask had to be in the format of the data being input rather than the standard dd-mon-yyyy of Oracle (if that makes sense)

Anyway, all data loaded now.

Report message to a moderator

Previous Topic:	Import from Oracle to Access
Next Topic:	SQL*LOAD - master detail record

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 04 20:23:35 CDT 2024