SQL 错误：ORA-12899：值对于列来说太大

发布于 2024-10-06 02:02:13 字数 717 浏览 11 评论 0原文

我创建了下表

CREATE TABLE Customers(
  CustomerID varchar2(9) PRIMARY KEY, 
  Customer_Contact varchar2(40) NOT NULL, 
  Address varchar2(20) NOT NULL,
  Post_Code varchar2(7) NOT NULL, 
  Telephone_Number varchar2(11) NOT NULL)

，目前我正在尝试使用 INSERT VALUES 语句。我编写了以下语句

INSERT INTO Customers VALUES(
  501623129, 
  'John Petterson', 
  '-- Singleton Close London', 
  '--- ---', 02082860222)

当我尝试运行该语句时，它给出了以下错误消息。

命令中从第 4 行开始出错：插入客户值（501623129，“大卫·帕特森”， “伦敦辛格尔顿街 30 号”、“SW17” 9JY', 02082860642) 错误报告：SQL 错误：ORA-12899：值太大列“DJ”.“客户”.“地址” （实际：25，最大：20） 12899. 00000 - “值对于列 %s 来说太大（实际值：%s，最大值：%s）”

原文

I have created the following table

CREATE TABLE Customers(
  CustomerID varchar2(9) PRIMARY KEY, 
  Customer_Contact varchar2(40) NOT NULL, 
  Address varchar2(20) NOT NULL,
  Post_Code varchar2(7) NOT NULL, 
  Telephone_Number varchar2(11) NOT NULL)

And I am currently trying to use the INSERT VALUES statement. I have written the following statement

INSERT INTO Customers VALUES(
  501623129, 
  'John Petterson', 
  '-- Singleton Close London', 
  '--- ---', 02082860222)

When I try to run the statement it gives me the following error message.

Error starting at line 4 in command:
INSERT INTO Customers
VALUES(501623129, 'David Patterson',
'30 Singleton Close London', 'SW17
9JY', 02082860642) Error report: SQL
Error: ORA-12899: value too large for
column "DJ"."CUSTOMERS"."ADDRESS"
(actual: 25, maximum: 20)
12899. 00000 - "value too large for column %s (actual: %s, maximum: %s)"

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

七颜 2024-10-13 02:02:13

ORA-12899: value too large for column "DJ"."CUSTOMERS"."ADDRESS" (actual: 25, maximum: 2

告诉你错误是什么。地址最多可容纳 20 个字符，您将传递 25 个字符。

ORA-12899: value too large for column "DJ"."CUSTOMERS"."ADDRESS" (actual: 25, maximum: 2

Tells you what the error is. Address can hold maximum of 20 characters, you are passing 25 characters.

回复收藏 0 原文

青衫负雪 2024-10-13 02:02:13

如前所述，错误消息向您显示了确切的问题：您正在将 25 个字符传递到设置为容纳 20 个字符的字段中。您可能还需要考虑更精确地定义列。您可以定义 VARCHAR2 列是否存储一定数量的字节或字符。您将来可能会遇到问题，尝试在字段中插入多字节字符，例如长度为 5 个字符，但它无法容纳 5 个字节： 'ÀÈÌÕÛ'

下面是一个示例：

CREATE TABLE Customers(CustomerID  VARCHAR2(9 BYTE), ...

或

CREATE TABLE Customers(CustomerID  VARCHAR2(9 CHAR), ...

As mentioned, the error message shows you the exact problem: you are passing 25 characters into a field set up to hold 20. You might also want to consider defining the columns a little more precisely. You can define whether the VARCHAR2 column will store a certain number of bytes or characters. You may encounter a problem in the future where you try to insert a multi byte character into the field, for example this is 5 characters in length but it won't fit into 5 bytes: 'ÀÈÌÕÛ'

Here is an example:

CREATE TABLE Customers(CustomerID  VARCHAR2(9 BYTE), ...

CREATE TABLE Customers(CustomerID  VARCHAR2(9 CHAR), ...

回复收藏 0 原文

彩虹直至黑白 2024-10-13 02:02:13

这个答案仍然在 ORA-12899 的列表中排名靠前，并且上面有很多无用的评论，即使它们已经过时了。对于任何试图找出加载数据时出现此问题的专业人士来说，最有帮助的评论是#4。

有些字符的长度超过 1 个字节，在 SQL Server 上尤其如此。适合 SQLServer 中 varchar(20) 的内容不适合 Oracle 中类似的 varchar2(20)。

昨天，我在使用 Attunity 驱动程序加载 Oracle 数据库的 SSIS 中遇到了这个错误，我想我可以节省人们一些时间。

回复收藏 0 原文

醉城メ夜风 2024-10-13 02:02:13

就我而言，我使用 C# OracleCommand 和 OracleParameter，并将所有参数 Size 属性设置为每列的最大长度，然后错误已解决。

OracleParameter parm1 = new OracleParameter();
param1.OracleDbType = OracleDbType.Varchar2;
param1.Value = "test1";
param1.Size = 8;

OracleParameter parm2 = new OracleParameter();
param2.OracleDbType = OracleDbType.Varchar2;
param2.Value = "test1";
param2.Size = 12;

In my case I'm using C# OracleCommand with OracleParameter, and I set all the the parameters Size property to max length of each column, then the error solved.

OracleParameter parm1 = new OracleParameter();
param1.OracleDbType = OracleDbType.Varchar2;
param1.Value = "test1";
param1.Size = 8;

OracleParameter parm2 = new OracleParameter();
param2.OracleDbType = OracleDbType.Varchar2;
param2.Value = "test1";
param2.Size = 12;

回复收藏 0 原文

離殇 2024-10-13 02:02:13

我只是想在这里分享来自 Google 的最佳结果...

我试图添加这样的虚拟列

alter table t1 add (
    export_code varchar2(3 char) as (case when country is null then code else substr(code, 3) end) virtual
);

，而 Oracle 抱怨：

ORA-12899: value too large for column "EXPORT_CODE" (actual: 12, maximum: 480)

问题是，我的 code 列是 varchar2(120 ) 因此 substr 最多 120 个字符。

可以发现，在创建临时表时：

create table t2 as
    select case when country is null then code else substr(code, 3) end as export_code from t1;

一旦我意识到， desc 返回

Name        Null? Type          
----------- ----- ------------- 
EXPORT_CODE       VARCHAR2(120)

，解决方案是使用 export_code varchar2(120 char) 或进行强制转换：

alter table t1 add (
  export_code varchar2(3 char) as ( cast (case when country is null then code else substr(code, 3) end as varchar2(3 char) ) ) virtual
);

I just wanted to share here as the top result from Google...

I was trying to add virtual column like this

alter table t1 add (
    export_code varchar2(3 char) as (case when country is null then code else substr(code, 3) end) virtual
);

and oracle complained:

ORA-12899: value too large for column "EXPORT_CODE" (actual: 12, maximum: 480)

The problem is, that my code column is varchar2(120) so substr is up to 120 characters.

One can find, when creating a temporary table:

create table t2 as
    select case when country is null then code else substr(code, 3) end as export_code from t1;

and desc returns

Name        Null? Type          
----------- ----- ------------- 
EXPORT_CODE       VARCHAR2(120)

once I realized, the solution is to use export_code varchar2(120 char) or do the cast:

alter table t1 add (
  export_code varchar2(3 char) as ( cast (case when country is null then code else substr(code, 3) end as varchar2(3 char) ) ) virtual
);

回复收藏 0 原文

凉世弥音 2024-10-13 02:02:13

示例：1和2表可用

1表删除条目并选择nor 2表记录并插入到no 1表。当删除时，没有 1 个表没有第二个表记录示例 emp id 不可用意味着出现此错误

回复收藏 0 原文

三寸金莲 2024-10-13 02:02:13

试试这个：

insert into CUSROMER_POST (CUS_NUM_BOX,CUS_NAE3_CUSTOMER,CUS_NAME,CUS_ID_OFFICE,CUS_DAET,CUS_MOLAHADA,CUS_PHON,CUS_EMIL,CUS_ADDRES) values (:P8_CUS_NUM_BOX,:P8_CUS_NAE3_CUSTOMER,:P8_CUS_NAME,:P8_CUS_ID_OFFICE,:P8_CUS_DAET,:P8_CUS_MOLAHADA,:P8_CUS_PHON,:P8_CUS_EMIL,:P8_CUS_ADDRES);

Try this:

insert into CUSROMER_POST (CUS_NUM_BOX,CUS_NAE3_CUSTOMER,CUS_NAME,CUS_ID_OFFICE,CUS_DAET,CUS_MOLAHADA,CUS_PHON,CUS_EMIL,CUS_ADDRES) values (:P8_CUS_NUM_BOX,:P8_CUS_NAE3_CUSTOMER,:P8_CUS_NAME,:P8_CUS_ID_OFFICE,:P8_CUS_DAET,:P8_CUS_MOLAHADA,:P8_CUS_PHON,:P8_CUS_EMIL,:P8_CUS_ADDRES);

回复收藏 0 原文

~没有更多了~