Home » RDBMS Server » Server Utilities » SQL Loader problem with Date Format
SQL Loader problem with Date Format [message #234150] Mon, 30 April 2007 03:40 Go to next message
gbaliotis
Messages: 8
Registered: April 2007
Location: Greece
Junior Member
Dear all,

I am dealing with a problem in loading data with SQL Loader. The problem is in the date format.

More specifically, I created the following Control File:

file.ctl

LOAD DATA
INFILE 'D:\gbal\chatium.log'
APPEND INTO TABLE CHAT_SL
FIELDS TERMINATED BY WHITESPACE
TRAILING NULLCOLS
(SL1 DATE "Mon DD, YYYY HH:MI:SS FF3AM",
SL2 char,
SL3 DATE "Mon DD, YYYY HH:MI:SS FF3AM",
SL4 char,
SL5 char,
SL6 char,
SL7 char,
SL8 char,
SL9 char,
SL10 char,
SL11 char,
SL12 char,
SL13 char,
SL14 char,
SL15 char)

The data we want to load are in the following file:

Apr 29, 2007 12:05:49 AM 1060615 Apr 29, 2007 12:05:35 AM 306978537730 24026384 chatium.user.userinfo WAP 0
Apr 29, 2007 12:12:51 AM 1061251 Apr 29, 2007 12:12:27 AM 306978537730 24026384 chatium.channel.list WAP 0
Apr 29, 2007 12:12:51 AM 1061264 Apr 29, 2007 12:12:32 AM 306978537730 24026384 chatium.channel.listdetail WAP 0
Apr 29, 2007 12:13:51 AM 1061321 Apr 29, 2007 12:13:31 AM 306978537730 24026384 chatium.user.search WAP 0
Apr 29, 2007 12:13:51 AM 1061330 Apr 29, 2007 12:13:37 AM 306978537730 24026384 chatium.user.userinfo WAP 0

The error log file is the following:

SQL*Loader: Release 9.2.0.1.0 - Production on Mon Apr 30 11:29:16 2007

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

Control File: file.ctl
Data File: D:\gbal\chatium.log
Bad File: chatium.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table CHAT_SL, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
SL1 FIRST * WHT DATE MonDD,YYYYHH:MI:SS
SL2 NEXT * WHT CHARACTER
SL3 NEXT * WHT CHARACTER
SL4 NEXT * WHT CHARACTER
SL5 NEXT * WHT CHARACTER
SL6 NEXT * WHT CHARACTER
SL7 NEXT * WHT CHARACTER
SL8 NEXT * WHT CHARACTER
SL9 NEXT * WHT CHARACTER
SL10 NEXT * WHT CHARACTER
SL11 NEXT * WHT CHARACTER
SL12 NEXT * WHT CHARACTER
SL13 NEXT * WHT CHARACTER
SL14 NEXT * WHT CHARACTER
SL15 NEXT * WHT CHARACTER

Record 1: Rejected - Error on table CHAT_SL, column SL1.
ORA-01840: input value not long enough for date format

Record 2: Rejected - Error on table CHAT_SL, column SL1.
ORA-01840: input value not long enough for date format

Record 3: Rejected - Error on table CHAT_SL, column SL1.
ORA-01840: input value not long enough for date format

Record 4: Rejected - Error on table CHAT_SL, column SL1.
ORA-01840: input value not long enough for date format

I wonder if you could help me.

Thank you very much in advance.

Giorgos Baliotis
Re: SQL Loader problem with Date Format [message #234187 is a reply to message #234150] Mon, 30 April 2007 05:49 Go to previous message
Michel Cadot
Messages: 64731
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your date format indicates "FF3" and your data don't contain this part so the errors "input value not long enough for date format".
Remove "FF3" from your date format to match the input values.

Regards
Michel
Previous Topic: SQLplus Login Info display
Next Topic: Different log file name in the Control file of SQL Loader
Goto Forum:
  


Current Time: Sun Apr 30 16:09:04 CDT 2017

Total time taken to generate the page: 0.18329 seconds