Nick,
You may be overcomplicating this... It works for me as below using a
very simple example:

SQL> create table datecheck (adate timestamp);

Data.dat file:

1-jan-2007 01:02:03.023
10-jan-2007 15:16:17.987

Control file (data.ctl):

OPTIONS (ERRORS=99999, DIRECT=TRUE)
UNRECOVERABLE load data
append
into table datecheck
FIELDS TERMINATED BY ',' optionally enclosed by '"'
trailing nullcols
(adate timestamp 'DD-MON-YYYY HH24:MI:SS.FF3')

Command:

sqlldr / control=data.ctl

Result:

SQL> select * from datecheck;

ADATE
------------------------------------------------------------------------
---
01-JAN-07 01.02.03.023000
10-JAN-07 15.16.17.987000

Works for me (10G on Unix though). Try the above approach.

HTH
John.




-----Original Message-----
From: Trincia Nicholas F.
[mailto:oracledba-ezmlmshield-x42431805.[Email address protected]
Sent: 28 November 2007 21:41
To: LazyDBA Discussion
Subject: SQL Loader question

We are trying to do a SQL load of some VMS T4 metric data and are
struggling with configuring our control file. The core problem is the
source data date formats for sample_dt_tm uses a single digit for days 1
through 9. We don't want to transform the data, but instead want
SQL*ldr to do it for us. We are using Oracle 9.2.0.5.0. We are losing
our hair on this issue. Any help to keep all follicles in place would
be a "hairsaver".

Nick



Here's the relevant portion of the control file
````````````````````````````````````````````````````````````````````
OPTIONS (SKIP = 4)
LOAD DATA
INFILE 'PERFDATA:[T4_V4.PROD1]T4_PROD1_20060531_COMP2.CSV'
INTO TABLE t4_data
APPEND
FIELDS TERMINATED BY ',' optionally enclosed by '"'
TRAILING NULLCOLS
(Sample_dt_tm "to_date(replace(substr(:Sample_dt_tm,1,1), ' ', '0') ||
substr(:Sample_dt_tm,2,20), 'DD-MON-YYYY HH24:MI:SS')",
more fields here....,
more fields here....,
more fields here....,
more fields here....,
more fields here....,
more fields here....,
LOADSEQ SEQUENCE (MAX,1)
)

The header of the source datafile (.csv) looks like this:
````````````````````````````````````````````````````````````````````````
```````````````````
PROD1, T4EXTR Version: T4 Version : V4.0,OpenVMS V7.3-2,T4$MON_EXTRACT
31-MAY-2006,31-MAY-2006
06:00,06:00
[MON]Sample Time,[MON.SYST]Direct I/O Rate,[MON.SYST]Buffered I/O Rate,


The records in the .csv datafile looks like this (I've pared it down to
a 2 record file)
````````````````````````````````````````````````````````````````````````
``````````````````````````````````````````````
31-MAY-2006 23:55:00.00,4117.15,252.86,93.86,14.90,585,382.99,2,170.83
1-JUN-2006 00:00:00.00,3949.26,179.41,175.91,27.99,585,346.87,3,152.44

The problem is that we get the message: (from the resulting logfile)
````````````````````````````````````````````````````````````````````````
`````````````````````````````````````
Record 2: Rejected - Error on table T4_DATA, column SAMPLE_DT_TM.
ORA-01830: date format picture ends before converting entire input
string

When we try to load records with a leading space as the first char in
the date, hence the

Sample_dt_tm "to_date(replace(substr(:Sample_dt_tm,1,1), ' ', '0') ||
substr(:Sample_dt_tm,2,19), 'DD-MON-YYYY HH24:MI:SS')",
Dates with 2 digits in the first two positions load with no problem

In the control file we've also tried:

Sample_dt_tm "to_date(Ltrim(substr(:Sample_dt_tm,1,20), 'DD-MON-YYYY
HH24:MI:SS'))"

A host of other approaches have been attempted, but with no luck.




thanks, Nick

______________________________________
Nick Trincia
Database Administrator
Information Systems, Christiana Care
office: 302-327-5605
Cell: 302-981-7801



---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



-----------------------------------------
Information in this email including any attachments may be
privileged, confidential and is intended exclusively for the
addressee. The views expressed may not be official policy, but the
personal views of the originator. If you have received it in error,
please notify the sender by return e-mail and delete it from your
system. You should not reproduce, distribute, store, retransmit,
use or disclose its contents to anyone.

Please note we reserve the right to monitor all e-mail
communication through our internal and external networks.

SKY and the SKY marks are trade marks of British Sky Broadcasting
Group plc and are used under licence. British Sky Broadcasting
Limited (Registration No. 2906991), Sky Interactive Limited
(Registration No. 3554332), Sky-In-Home Service Limited
(Registration No. 2067075) and Sky Subscribers Services Limited
(Registration No. 2340150) are direct or indirect subsidiaries of
British Sky Broadcasting Group plc (Registration No. 2247735). All
of the companies mentioned in this paragraph are incorporated in
England and Wales and share the same registered office at Grant
Way, Isleworth, Middlesex TW7 5QD.


Oracle LazyDBA home page