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


Oracle LazyDBA home page