database - ORA-01401: inserted value too large for column CHAR

Question

I'm pretty new to SQL Oracle and my class is going over Bulk Loading at the moment. I pretty much get the idea however I am having a little trouble getting it to read all of my records.

This is my SQL File;

PROMPT Creating Table 'CUSTOMER'
CREATE TABLE CUSTOMER
(CustomerPhoneKey CHAR(10) PRIMARY KEY
,CustomerLastName VARCHAR(15)
,CustomerFirstName VARCHAR(15)
,CustomerAddress1 VARCHAR(15)
,CutomerAddress2 VARCHAR(30)
,CustomerCity VARCHAR(15)
,CustomerState VARCHAR(5)
,CustomerZip VARCHAR(5)
);

Quick and easy. Now This is my Control File to load in the data

LOAD DATA
INFILE Customer.dat
INTO TABLE Customer
FIELDS TERMINATED BY"|"
(CustomerPhoneKey, CustomerLastName, CustomerFirstName, CustomerAddress1 , CutomerAddress2, CustomerCity, CustomerState, CustomerZip)

Then the Data File

2065552123|Lamont|Jason|NULL|161 South Western Ave|NULL|NULL|98001
2065553252|Johnston|Mark|Apt. 304|1215 Terrace Avenue|Seattle|WA|98001
2065552963|Lewis|Clark|NULL|520 East Lake Way|NULL|NULL|98002
2065553213|Anderson|Karl|Apt 10|222 Southern Street|NULL|NULL|98001
2065552217|Wong|Frank|NULL|2832 Washington Ave|Seattle|WA|98002
2065556623|Jimenez|Maria|Apt 13 B|1200 Norton Way|NULL|NULL|98003

The problem is, that only the last record

2065556623|Jimenez|Maria|Apt 13 B|1200 Norton Way|NULL|NULL|98003

is being loaded in. The rest are in my bad file

So I took a look at my log file and the errors I'm getting are

Record 1: Rejected - Error on table CUSTOMER, column CUSTOMERZIP. ORA-01401: inserted value too large for column

Record 2: Rejected - Error on table CUSTOMER, column CUSTOMERZIP. ORA-01401: inserted value too large for column

Record 3: Rejected - Error on table CUSTOMER, column CUSTOMERZIP. ORA-01401: inserted value too large for column

Record 4: Rejected - Error on table CUSTOMER, column CUSTOMERZIP. ORA-01401: inserted value too large for column

Record 5: Rejected - Error on table CUSTOMER, column CUSTOMERZIP. ORA-01401: inserted value too large for column

Table CUSTOMER: 1 Row successfully loaded. 5 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null.

Onto the question. I see that CustomerZip is the problem, and initially I had it as CHAR(5) -- I did this because my understanding of the data type, is that for numeric values like a zip code, I would not be doing arithmetic operations with it, so it would be better to store it as CHAR. Also I did not use VARCHAR2 (5) initially, because seeing as it is a zip code, I don't want the value to vary, It should always be 5. Now maybe I'm just misunderstanding this. So if there is anyone that can clear that up, that would be awesome.

My second question, is "How do I fix this problem?" Given the above understanding of these data types, it doesn't make sense why CHAR(5) NOR VARCHAR2(5) work. As I am getting the same errors for both.

It makes even less sense that one record(the last one) actually works.

Thank you for the help in advance

Answer 1

Your data file has extra, invisible characters. We can't see the original but presumably it was created in Windows and has CRLF new line separators; and you're running SQL*Loader in a UNIX/Linux environment that is only expecting line feed (LF). The carriage return (CR) characters are still in the file, and Oracle is seeing them as part of the ZIP field in the file.

The last line doesn't have a CRLF (or any new-line marker), so on that line - and only that line - the ZIP field is being seen as 5 characters, For all the others it's being seen as six, e.g. 98001^M.

You can read more about the default behaviour in the documentation:

• On UNIX-based platforms, if no terminator_string is specified, then SQL*Loader defaults to the line feed character, .

• On Windows NT, if no terminator_string is specified, then SQL*Loader uses either or as the record terminator, depending on which one it finds first in the data file. This means that if you know that one or more records in your data file has embedded in a field, but you want to be used as the record terminator, then you must specify it.

If you open the data file in an edit like vi or vim, you'll see those extra ^M control characters.

There are several ways to fix this. You can modify the file; the simplest thing to do that is copy and paste the data into a new file created in the environment you'll run SQL*Loader in. There are utilities to convert line endings if you prefer, e.g. dos2unix. Or your Windows editor may be able to save the file without the CRs. You could also add an extra field delimiter to the data file, as Ditto suggested.

Or you could tell SQL*Loader to expect CRLF by changing the INFILE line:

LOAD DATA
INFILE Customer.dat "str '
'"
INTO TABLE Customer
...

... though that will then cause problems if you do supply a file created in Linux, without the CR characters.