Re: ORA-01830 and SQL*Loader

Steve wrote:
Hi Todd

try something like this:

...

   timestamp POSITION(14:28) CHAR "to_date(:timestamp, 'MMDDYYYY HH24MISS')" NULLIF
...,

Martin

> Todd Owers wrote in message <72fmvt$jip$1_at_nntp.gulfsouth.verio.net>...
> >I am running into a problem trying to insert into a table using SQL*Loader.
> >The insert fails because of this error: "ORA-01830: date format picture
 ends
> >before converting entire input string." I have checked and double-checked
> >my data file and control file, and as far as I can tell, the input string
> >exactly matches the format mask.
> >
> >Here is the data file. For testing purposes, it has only one line.
> >
> >B-98765-98 ,11101998 000000,456
> >
> >Here is the structure of the table I am inserting into:
> >
> >CREATE TABLE narrative (
> > incident_number varchar2(12) constraint pk_narrative primary
> >key,
> > timestamp date,
> > payroll_number number(3)
> >);
> >
> >Here is the control file:
> >
> >LOAD DATA
> >INFILE 'test.lst'
> >APPEND
> >INTO TABLE narrative
> > (incident_number POSITION(1:12) CHAR NULLIF incident_number=BLANKS,
> > timestamp POSITION(14:28) DATE 'MMDDYYYY HH24MISS' NULLIF
> >timestamp=BLANKS,
> > payroll_number POSITION(30:32) CHAR NULLIF payroll_number=BLANKS
> >)
> >
> >Here is the error message in the log file:
> >
> >Record 1: Rejected - Error on table NARRATIVE, column TIMESTAMP.
> >ORA-01830: date format picture ends before converting entire input string
> >
> >I read the Oracle support bulletins regarding ORA-01830, and they indicate
> >that the input string and format mask must match exactly, with no extra
> >characters in either. As far as I can tell, both the input string and
> >format mask have 15 characters: 8 for the month/day/year, 1 blank space,
 and
> >6 for HH/MI/SS.
> >
> >Can anyone shed some light on this? Thanks in advance for your help.
> >
> >Todd Owers
> >toddo_at_gcr1.com
> >
> >
> >

--
C+C Schaper GmbH
Eckener Str. 2
D-30179 Hannover