Need help loading Dates using sqlloader 1

[rogers42]

Hi Folks,

I am having problems loading some data in my Oracle table. Can somebody please look at the following code and point out my mistake ?

Table Defination
================
table_1
(FLAP_TIME DATE)

sqlloader control file
======================
FLAP_TIME "to_dateFLAP_TIME,'Mon DD HH24:MI:SS')"

Sample Data
===============
May 27 15:02:36
May 29 04:03:57

Sql Loader Error
=================
Record 1: Rejected - Error on table TABLE_1, column FLAP_TIME.
ORA-01843: not a valid month

Thanks in advance.

rogers42

 

[LKBrwnDBA]

There is nothing wrong with Oracle, you have an incorrectly defined control file, check out this one that works:

#---- Control file:
LOAD DATA
INFILE *
INTO TABLE TABLE_1 TRUNCATE
TRAILING NULLCOLS 
(FLAP_TIME POSITION(1:15)   "TO_DATE(:FLAP_TIME,'MON DD HH24:MI:SS')")
BEGINDATA
May 27 15:02:36
May 29 04:03:57
# Execution:
$ sqlldr scott/tiger control=t1.ctl

SQL*Loader: Release 10.2.0.3.0 - Production on Fri May 30 10:52:49 2008

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

Commit point reached - logical record count 2

$ cat t1.log

SQL*Loader: Release 10.2.0.3.0 - Production on Fri May 30 10:52:49 2008

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

Control File:   t1.ctl
Data File:      t1.ctl
  Bad File:     t1.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 TABLE_1, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
FLAP_TIME                            1:15    15           CHARACTER            
    SQL string for column : "TO_DATE(:FLAP_TIME,'MON DD HH24:MI:SS')"


Table TABLE_1:
  2 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                   1152 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             2
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Fri May 30 10:52:49 2008
Run ended on Fri May 30 10:52:50 2008

Elapsed time was:     00:00:00.39
CPU time was:         00:00:00.05
$ sqlplus scott/tiger

SQL*Plus: Release 10.2.0.3.0 - Production on Fri May 30 10:51:13 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from table_1;

FLAP_TIME
---------
27-MAY-08
29-MAY-08

SQL>

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[rogers42]

Right, you are. There is nothing wrong with the control file.

I just realized, the raw data, the table columns, and the control file variable list did not align properly. Once I had added the missing table column and had updated the control file variable list, everything worked perfectly.

Thanks again for your help

rogers42

 

[SantaMufasa]

Rogers42, I've noticed that in the 14 months (and 8 threads you have received help with) since you have been a Tek-Tips member, you have overlooked (inadvertently, I'm sure) the convention we use for thanking those who provide us excellent help, as LKBrwnDBA has done for you: we click the link, above, Thank LKBrwnDBA for this valuable post!

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[rogers42]

Not following the conventional way of thanking my fellow forum members is definitely inadvertent and is not meant as disrespect.

Thanks for pointing it out. I have acted upon your feedback.

rogers42