ORA-01401:插入值对于CHAR列太大 [英] ORA-01401: inserted value too large for column CHAR

问题描述

我对SQL Oracle还是很陌生，我的课程目前正在批量加载中.我几乎了解了这个想法，但是在读取它的所有记录时遇到了一些麻烦.

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.

这是我的SQL文件；

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)

然后创建数据文件

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

记录1:已拒绝-表CUSTOMER的CUSTOMERZIP列出错. ORA-01401:插入值对于列而言太大

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

记录2:已拒绝-表CUSTOMER的CUSTOMERZIP列出错. ORA-01401:插入值对于列而言太大

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

记录3:已拒绝-表CUSTOMER的CUSTOMERZIP列出错. ORA-01401:插入值对于列而言太大

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

记录4:已拒绝-表CUSTOMER的CUSTOMERZIP列出错. ORA-01401:插入值对于列而言太大

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

记录5:已拒绝-表CUSTOMER的CUSTOMERZIP列出错. ORA-01401:插入值对于列而言太大

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

表客户:成功加载1行. 5行未加载 数据错误. 0行未加载，因为所有WHEN子句都被加载 失败的. 0行未加载，因为所有字段均为空.

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.

这个问题.我看到是CustomerZip的问题，起初我将其命名为CHAR(5)－之所以这样做是因为我对数据类型的理解是，对于诸如邮政编码之类的数字值，我不会对其进行算术运算，因此最好将其存储为CHAR.另外，我最初并没有使用VARCHAR2(5)，因为看到它是一个邮政编码，我不希望该值变化，它应该始终为5.现在也许我只是误解了这一点.因此，如果有任何人可以清除它，那就太好了.

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.

我的第二个问题是如何解决此问题?"鉴于以上对这些数据类型的理解，为什么CHAR(5)NOR VARCHAR2(5)起作用是没有意义的.由于我都遇到相同的错误.

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.

谢谢您的帮助

推荐答案

您的数据文件包含多余的不可见字符.我们看不到原始文件，但大概是在Windows中创建的，并且具有CRLF 新行分隔符；并且您在仅需要换行(LF)的UNIX/Linux环境中运行SQL * Loader.回车(CR)字符仍在文件中，Oracle认为它们是文件中ZIP字段的一部分.

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.

最后一行没有CRLF(或任何换行标记)，因此该行-仅 该行-ZIP字段被视为5个字符，对于所有其他的被认为是六个，例如98001^M.

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:

• 在基于UNIX的平台上，如果未指定terminator_string，则SQL * Loader默认为换行符\ n.

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

在Windows NT上，如果未指定terminator_string，则SQL * Loader使用\ n或\ r \ n作为记录终止符，这取决于它在数据文件中最先找到的那个.这意味着，如果您知道数据文件中的一个或多个记录已在字段中嵌入\ n，但是希望将\ r \ n用作记录终止符，则必须指定该记录终止符.

On Windows NT, if no terminator_string is specified, then SQL*Loader uses either \n or \r\n 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 \n embedded in a field, but you want \r\n to be used as the record terminator, then you must specify it.

如果在vi或vim之类的编辑中打开数据文件，则会看到那些额外的^M控制字符.

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

有几种方法可以解决此问题.您可以修改文件；最简单的方法是将数据复制并粘贴到在运行SQL * Loader的环境中创建的新文件中.如果您愿意，可以使用一些实用程序来转换行尾，例如dos2unix.或者您的Windows编辑器可能能够在没有CR的情况下保存文件.您也可以按照Ditto的建议，在数据文件中添加一个额外的字段定界符.

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.

或者您可以通过更改INFILE行来告诉SQL * Loader期望CRLF:

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

LOAD DATA
INFILE Customer.dat "str '\r\n'"
INTO TABLE Customer
...

...但是如果您确实提供了在Linux中创建的不带CR字符的文件，则会导致问题.

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

这篇关于ORA-01401:插入值对于CHAR列太大的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！