ORA-01830 and SQL*Loader

From: Todd Owers <toddo_at_gcr1.com>
Date: 1998/11/12
Message-ID: <72fmvt$jip$1_at_nntp.gulfsouth.verio.net>#1/1


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.

[Quoted] 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
[Quoted]   (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 Thu Nov 12 1998 - 00:00:00 CET

Original text of this message