Re: ORA-01830 and SQL*Loader

Have you tried adding a blank line (extra newline) to the end of your data and control files?

Steve

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
>
>
>
Received on Wed Nov 18 1998 - 00:00:00 CST