ORA-00904从表中插入/查询数据时 [英] ORA-00904 when inserting/querying data from tables

问题描述

我遇到了流程错误：

Error starting at line 1 in command:
INSERT INTO driver (registration, make, model, gvw, year, body) VALUES('4585 AW','ALBION','RIEVER',20321,1963, ' ');
Error at Command Line:1 Column:53
Error report:
SQL Error: ORA-00904: "BODY": invalid identifier
00904. 00000 -  "%s: invalid identifier"

当我执行以下操作时

INSERT INTO driver 
(registration, make, model, gvw, year) 
VALUES
('4585 AW','ALBION','RIEVER',20321,1963, ' ');

所以我暂时删除了主体数据，然后给出错误

So I temporally deleted the body data and then give the error

Error starting at line 1 in command:
INSERT INTO driver (registration, make, model, gvw, year) VALUES('4585 AW','ALBION','RIEVER',20321,1963)
Error at Command Line:1 Column:53
Error report:
SQL Error: ORA-00904: "YEAR": invalid identifier
00904. 00000 -  "%s: invalid identifier"

但我收到错误。如何解决这个问题？

I have columns both called year and body, yet I'm getting errors. How can I fix this?

推荐答案

似乎你在DDL中用引号括起列名。

Seems like you specified the column names under quotes in your DDL.

Oracle将引号下的标识符视为区分大小写，因此创建列名为body或year的表将无法解析查询中的正文或年份

Oracle considers identifiers under quotes as a case sensitive, so creating a table with a column name as "body" or "year" will not resolve body or year in your query, because when you omit the quotes, it considers the upper case variant.

因此，当你有这个：

 create table driver_case (registration varchar2(60), 
                      make varchar2(60),
                      model varchar2(60),
                      gvw number,
                      "year" number, 
                      "body" varchar2(60));

然后尝试

select year, body from driver_case

< YEAR，BODY（记住，没有带引号的标识符转换为大写）与表中的year，body不同。

then Oracle tries to find column "YEAR", "BODY" (remember, without an identifier without quotes gets converted to upper case) which is not the same as "year", "body" in your table.

您的解决方案？

• 不要在DDL的引号下提及列名


• 如果您忽略上述要点，那么您必须在所有DML语句的引号下提及列名称。

• Don't mention column names under quotes in the DDL
• If you disregard the above point, then you must mention the column names under quotes in all your DML statements.

我在此 SQL小提示中演示了上述要点

I demonstrate the above point in this SQL Fiddle

这篇关于ORA-00904从表中插入/查询数据时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！