ORA-01830 and SQL*Loader
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