如何解决ORA-00904 标识符无效

  • by
ORA-00904 Invalid Identifier - Oracle Database Object Name Recognition Examples

ORA-00904

ORA-00904表示您在语句中使用了不匹配的列名或不正确的语法。通常,错误不仅发生在SELECTINSERT中,还发生在UPDATEDELETECREATE TABLEALTER TABLE和PL/SQL块中。

大多数用户不会相信错误,但事实上SQL解析器从未犯过任何错误。在这篇文章中,您可能会看到一些关于ORA-00904的错误模式,其中一个可能与您的情况相符。由于大多数用户错误是由区分大小写的问题引起的,因此我们必须首先解释松散形式精确形式之间的差异。

通常,Oracle会将大多数标识符排除在密码之外,并将其视为不区分大小写的标识符。但是在ORA-00904无效标识符的情况下应该注意一些特殊用法。

不仅ORA-00904,ORA-00903和ORA-00911也与对象标识符的无效使用有关。更具体地说,ORA-00903警告用户无效的表名,并引发ORA-00911来定位非法字符

松散形式与精确形式

根据Oracle数据库对象名称和限定符,有两种有效的命名形式来创建数据库对象。一个是非引用标识符,另一个是带引号的标识符。为了避免ORA-00904,您必须知道两种命名形式之间的明显差异。

非引用标识符(松散形式)

它们没有标点符号或任何其他特殊处理。只要它们基本上是等效的字符串,我们就可以在SQL语句中不区分大小地灵活使用它们。所以我通常在这篇文章中将其称为松散形式。实际上,非引用标识符都被认为是上层标识符。这个概念将帮助您更好地了解ORA-00904。

带引号的标识符(精确形式)

它们以双引号("")开头和结尾。您可以将几乎所有字符放在双引号中,包括空格和保留字。这种标识符应该与最初定义的完全一致。所以我通常在这篇文章中将其称为精确形式。据我所知,这是ORA-00904的主要来源。

稍后,我们还将讨论有关ORA-00904的更多问题,以区分引用的和未引用的标识符。

ORA-00904的错误模式

在大多数情况下,滥用列名是ORA-00904的主要来源,其余的是语法错误。在这篇文章中,我们将在以下部分讨论ORA-00904的几种错误模式。

  1. SELECT或INSERT中的ORA-00904
  2. WHERE,ORDER BY或GROUP BY中的ORA-00904
  3. CREATE TABLE中的ORA-00904
  4. ALTER TABLE中的ORA-00904
  5. PL/SQL中的ORA-00904

A. SELECT或INSERT中的ORA-00904

SELECTINSERT中列出的任何列都有机会生成此错误。

在本节中,ORA-00904会警告用户出现问题,这可能是由以下原因之一引起的:

  1. 不存在的列
  2. 不存在的功能
  3. 拼写错误的列
  4. 区分大小写的列
  5. 列名称中的空白

1. 由于不存在的列而导致的ORA-00904

通常,我们创建一个表而不使用双引号:

SQL> create table all_names_1 (First_Name varchar2(25), Last_Name varchar2(25));

Table created.

然后我们插入一些数据。

SQL> insert into all_names_1 select distinct first_name, last_name from employees;

107 rows created.

要避免使用ORA-00904,您应该在没有任何引号的情况下查询此表。也就是说,低位或大写的列名都是有效且可接受的。这是因为SQL解析器会将所有标识符视为大写标识符。

SQL> select first_name, last_name from all_names_1 where first_name = 'Ed';

FIRST_NAME                LAST_NAME
------------------------- -------------------------
Ed                        Chen

使用这种通常创建的表格可以保证它不再具有ORA-00904了吗?让我们看看两种常见类型的无效标识符。

第一种情况是选择表中不存在的列。

SQL> select first_name, last_name, num from all_names_1 where first_name = 'Ed';
select first_name, last_name, num from all_names_1 where first_name = 'Ed'
                              *
ERROR at line 1:
ORA-00904: "NUM": invalid identifier
SQL> insert into all_names_1 (first_name, last_name, num) values ('Ed', 'Chen', 100);
insert into all_names_1 (first_name, last_name, num) values ('Ed', 'Chen', 100)
                                                *
ERROR at line 1:
ORA-00904: "NUM": invalid identifier
ORA-00904 Toad for Oracle中的无效标识符

ORA-00904 Toad for Oracle中的无效标识符

我们知道,列NUM是无效列,因为它不存在于表中。我们最好通过描述表来检查定义。

SQL> desc all_names_1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FIRST_NAME                                         VARCHAR2(25)
 LAST_NAME                                          VARCHAR2(25)

如果您对表中缺少该列感到惊讶,则有可能已被其他人删除。

2. 由于不存在的函数而导致的ORA-00904

这种模式很少见,我们应该讨论不存在的函数调用。让我们看看我们如何复制ORA-00904。

SQL> select first_name, month(hire_date) hire_month from employees where last_name = 'Chen';
select first_name, month(hire_date) hire_month from employees where last_name = 'Chen'
                   *
ERROR at line 1:
ORA-00904: "MONTH": invalid identifier

SQL解析器首先尝试匹配名为MONTH的函数,但没有找到任何内容,因此它转向匹配列,也没有匹配,SQL解析器别无选择,只能抛出ORA-00904。这让我想起MySQL确实有MONTH功能,但Oracle没有。不存在功能的原因相同:ORA-00904:"DATEDIFF":标识符无效

ORA-00904的解决方案是使用正确Oracle的函数名为EXTRACT来获取列的月份值。

SQL> select first_name, extract(month from hire_date) hire_month from employees where last_name = 'Chen';

FIRST_NAME           HIRE_MONTH
-------------------- ----------
Ed                            9

函数 DATEDIFF在Oracle中不起作用

请注意,您在MySQL中使用的某些日期时间函数,如DATEDIFFYEARMONTHNOWSUBSTRING,在Oracle中不是有效的函数或标识符。您可以查看Oracle SQL语言参考:函数,以防止ORA-00904。

3. 由于拼写错误的列而导致的ORA-00904

第三种情况是选择拼写错误的列名称,这是ORA-00904最常见的模式。

SQL> select first name, last_name from all_names_1 where first_name = 'Ed';
select first name, last_name from all_names_1 where first_name = 'Ed'
       *
ERROR at line 1:
ORA-00904: "FIRST": invalid identifier

在SQL语句中,我故意通过错过FIRST和NAME之间的下划线使列名无效。因此,SQL解析器将语句翻译为:

从表ALL_NAMES_1中查询名为FIRST的列并显示为NAME,和列LAST_NAME以及其他条件。
当然,没有名为FIRST的有效列,甚至没有First Name。有效且真实的标识符是FIRST_NAME。我们应该再次检查拼写,然后更正标识符。

为避免列名称出现拼写错误,您可以使用SQL Developer,Toad for Oracle或PL/SQL Developer等GUI工具来方便您自动填充列名。有关SQL Developer的示例:

SQL Developer Editor中的自动填充列名称,以避免ORA-00904无效的标识符

SQL Developer Editor中的自动填充列名称,以避免ORA-00904无效的标识符

正如您所看到的,我们提供了一些字母,SQL开发人员的编辑将负责其余的工作。

4. 区分大小写的列

在某些情况下,SQL解析器通过抛出ORA-00904来抱怨缺少的列,但表中存在列。怎么会发生这种情况?您需要知道的是如何使用精确的表单来表达SQL语句中的列。

要使用确切形式的标识符,我们必须使用双引号来包装列名,这会通知数据库创建我们提供的确切名称。究竟怎么样?至少,我们应该将它们视为区分大小写的标识符。

SQL> create table all_names_2 ("First_Name" varchar2(25), "Last_Name" varchar2(25));

Table created.

然后我们插入一些数据。

SQL> insert into all_names_2 select distinct first_name, last_name from employees;

107 rows created.

从现在开始,我们再也不能像过去那样松散地使用表了。否则,我们很有可能在语句中使用无效标识符,然后获取ORA-00904。

如果不在列上添加双引号,我们就得到了ORA-00904。

SQL> select First_Name, Last_Name from all_names_2 where First_Name = 'Ed';
select First_Name, Last_Name from all_names_2 where First_Name = 'Ed'
                                                    *
ERROR at line 1:
ORA-00904: "FIRST_NAME": invalid identifier
SQL> insert into all_names_2 (First_Name, Last_Name) values ('Ed', 'Chen');
insert into all_names_2 (First_Name, Last_Name) values ('Ed', 'Chen')
                                     *
ERROR at line 1:
ORA-00904: "LAST_NAME": invalid identifier

尽管列名与其定义完全相同,但由于列上缺少双引号,我们仍然得到ORA-00904。这是因为未引用的标识符First_Name被松散地识别为FIRST_NAME,这是一个较高的标识符。因此,SQL解析器无法在表中找到匹配的标识符。最终,它提出了ORA-00904以提醒假列。

解决方法

解决方法很简单,我们应该按照我们在创建表格时提供的方式引用它们,而不是非引用形式。

SQL> select "First_Name", "Last_Name" from all_names_2 where "First_Name" = 'Ed';

First_Name                Last_Name
------------------------- -------------------------
Ed                        Chen
SQL> insert into all_names_2 ("First_Name", "Last_Name") values ('Ed', 'Chen');

1 row created.

我们使用确切的表单来使语句有效。我们可以看到,使用精确的表单非常不方便,我们应该关注标识符的每一个细节,以防止它们被无效。

5. 列名称中的空白

值得注意的是,确切的形式允许我们创建填充空白的列,这些列在没有ORA-00904失效问题的情况下有效。让我们先看一下普通表。

SQL> create table all_names_3 (First_Name varchar2(25), Last_Name varchar2(25), Num int, Create_Date date default sysdate);

Table created.

SQL> insert into all_names_3 (first_name, last_name, num) select first_name, last_name, count(*) from employees group by first_name, last_name;

107 rows created.

SQL> select first_name, last_name, num, create_date from all_names_3 where first_name = 'Ed';

FIRST_NAME                LAST_NAME                        NUM CREATE_DA
------------------------- ------------------------- ---------- ---------
Ed                        Chen                               1 12-MAR-19

接下来,让我们看一个奇怪但有效的案例,列名中有空格。

SQL> create table all_names_4 ("First_Name" varchar2(25), "Last_Name" varchar2(25), " " int, "  " date default sysdate);

Table created.

SQL> insert into all_names_4 ("First_Name", "Last_Name", " ") select first_name, last_name, count(*) from employees group by first_name, last_name;

107 rows created.

SQL> select "First_Name", "Last_Name", " ", "  " from all_names_4 where "First_Name" = 'Ed';

First_Name                Last_Name
------------------------- ------------------------- ---------- ---------
Ed                        Chen                               1 12-MAR-19

我们可以看到,我为第三列使用了一个空格,第四列使用了两个空格,只要您按照规则查询表,它们就可以在没有ORA-00904的情况下正常工作。

有任何名称冲突或失效?没有,这是因为一个空白与同一个表的命名空间中的两个空格不同,特别是当我们使用精确形式来定义列时。

即使我们没有得到任何ORA-00904和无效问题,因为我们仔细而恰当地使用了表格,但确切风格的命名确实令人困惑。尝试描述奇怪但有效的表格:

SQL> desc all_names_4;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 First_Name                                         VARCHAR2(25)
 Last_Name                                          VARCHAR2(25)
                                                    NUMBER(38)
                                                    DATE

因此,它在第三和第四列上显示的信息很少。对于一个新成员,他完全不知道这里发生了什么。此外,一旦查询这样一个奇数表时抛出ORA-00904,没有人能够对其进行故障排除。

这提醒我,我曾尝试在Unix操作系统中删除空名或空白名称的文件。我花了很长时间才解决它。

B. WHERE,ORDER BY或GROUP BY中的ORA-00904

我们上面讨论的不匹配的列问题(如不存在的列不存在的函数拼写错误的列区分大小写的列)也可能发生在WHEREORDER BYGROUP BY子句中。

在本节中,我们将特别讨论如何在WHEREGROUP BY子句中使用列别名。

  1. 滥用列别名

1. 由于滥用列别名而导致的ORA-00904

您可以在ORDER BYORDER BY子句中使用列别名,如下所示。

SQL> column did format 9999;
SQL> column eid format 9999;
SQL> select department_id as did, employee_id as eid from employees order by did;

  DID   EID
----- -----
   10   200
   20   201
...

但是,您既不能直接在WHERE子句中使用列别名:

SQL> select department_id as did, employee_id as eid from employees where did > 50;
select department_id as did, employee_id as eid from employees where did > 50
                                                                     *
ERROR at line 1:
ORA-00904: "DID": invalid identifier

也不能在GROUP BY子句中使用:

SQL> select department_id as did, count(employee_id) as cnt from employees group by did;
select department_id as did, count(employee_id) as cnt from employees group by did
                                                                               *
ERROR at line 1:
ORA-00904: "DID": invalid identifier

我们都知道列DID是什么,但SQL解析器并不知道。它抛出ORA-00904来提醒问题。

解决方案

当然,您可以使用实际列名来随时抑制ORA-00904。这种方法会更加稳定。

为了能够在WHERE子句中使用列别名,您应该通过外部SELECT包装查询。

SQL> select * from (select department_id as did, employee_id as eid from employees) where did > 50;

  DID   EID
----- -----
   90   100
   90   101
...

对于想要使用列别名的GROUP BY子句,这有点棘手。

SQL> select did, count(eid) cnt from (select department_id as did, employee_id eid from employees) group by did;

  DID        CNT
----- ----------
  100          6
   30          6
...

也就是说,如果您坚持使用列别名,则外部SELECT是此错误模式中ORA-00904的解决方案,它可以将所有列别名视为实际列名。

某些SELECT子条款中,列别名c_alias有更多微妙的用法,例如search_clausecycle_clauseorder_by_clause。.

C. CREATE TABLE中的ORA-00904

CREATE TABLE语句中有几种可能的ORA-00904模式。

  1. 滥用保留字
  2. 错误地添加了额外的逗号
  3. 以数字开始

1. 由于滥用保留字而导致的ORA-00904

像ORA-00904这样的错误可能发生在各种对象创建中。让我们看一些错误类型的标识符来阐明命名规则。

使用保留字是疯狂的,涉及它们的一切可能最终变得不可预测和令人讨厌。您永远不应该使用它们来命名数据库对象。

SQL> create table t1 (audit int);
create table t1 (audit int)
                 *
ERROR at line 1:
ORA-00904: : invalid identifier

我们再次看到了ORA-00904,但这次SQL解析器无法告诉我们列名,并将无效字符串留空。

SQL> create table t1 ("audit" int);

Table created.

有可能,您的用户可能会在完全无意识的情况下创建此类数据库对象。这是因为他们的工具可以处理剩下的工作。

2. 由于错误地添加了额外的逗号而导致的ORA-00904

在语句中错误地添加了一个额外的逗号使得SQL解析器不知道该怎么做,让我们看看一些例子:

SQL> create table t1 (c1 number, c2 date,);
create table t1 (c1 number, c2 date,)
                                    *
ERROR at line 1:
ORA-00904: : invalid identifier
SQL> create table t1 (c1 number,, c2 date);
create table t1 (c1 number,, c2 date)
                           *
ERROR at line 1:
ORA-00904: : invalid identifier

如您所见,列的列表中有一个额外的逗号。SQL解析器知道额外逗号后面并没有任何内容,但它最终留下ORA-00904让您去修复这些无效的使用。

正如我们所说的那样,只要您使用确切的表单来创建表,使用纯空格定义列是可接受且有意义的。但是,没有任何内容则是另一个想法,这完全是无稽之谈。难怪ORA-00904被抛出。

要解决此问题,我们应删除额外的逗号以使该语句有效。

SQL> create table t1 (c1 number, c2 date);

Table created.

3. 由于以数字开始而导致的ORA-00904

一个以数字开头的表名会怎么样?

SQL> create table t1 (12345678 int);
create table t1 (12345678 int)
                 *
ERROR at line 1:
ORA-00904: : invalid identifier

ORA-00904警告你,这在这里是非法的。所以我在标识符之前添加了一个字母“c”来修复ORA-00904。

SQL> create table t1 (c12345678 int);

Table created.

对于正在使用的任何无效字符,会引发ORA-00911以提示此问题,如下所示。

SQL> create table $t1 (c12345678 int);
create table $t1 (c12345678 int)
             *
ERROR at line 1:
ORA-00911: invalid character

实际上,美元符号“$”对于标识符是有效的,只是不要将它放在对象名称的开头。有关特殊字符限制的更多信息,您应该选择下一个规则。

尽管ORA-00911是对象命名的非法用法,但它并不像ORA-00904那样明显和具体。现在让我们来看看如何在对象命名上使用特殊字符。

特殊字符

只允许$,_和#。对于列名,使用不可接受的特殊字符“?”引发ORA-00911无效字符。

SQL> create table t1 (c12345678?production int);
create table t1 (c12345678?production int)
                          *
ERROR at line 1:
ORA-00911: invalid character

取代ORA-00904,我们看到ORA-00911通知我们问号是无效字符。所以我将“?”改为“$”,“#”或“_”来纠正问题。

美元符号

SQL> create table t1 (c12345678$production int);

Table created.

数字标志

SQL> create table t2 (c12345678#production int);

Table created.

下划线

SQL> create table t3 (c12345678_production int);

Table created.

实际上,使用字符串作为标识符是很正常的。而不是空格,经常使用下划线并建议用于分隔字符串中有意义的单词。

D. ALTER TABLE中的ORA-00904

类似的问题不存在的列不存在的功能拼写错误的列区分大小写列,在这里也适用。因此,我宁愿在本节讨论一些罕见的情况。

ALTER TABLE语句中有几种ORA-00904模式。

  1. ALTER TABLE ADD 列
  2. ALTER TABLE MODIFY 列
  3. ALTER TABLE ADD CONSTRAINT NOT NULL

1. ORA-00904在ALTER TABLE ADD 列中

ALTER DATABASE ADD列语句中的典型ORA-00904 如下所示:

SQL> alter table t1 add column c2 date;
alter table t1 add column c2 date
                   *
ERROR at line 1:
ORA-00904: : invalid identifier

ORA-00904专门定位在保留字COLUMN并告诉您它不是有效的标识符。实际上,这是一个语法错误。您不必在语句中添加保留字COLUMN。所以我们通过删除关键字来纠正它。

SQL> alter table t1 add c2 date;

Table altered.

此外,在ADD之后,您不应该在该位置使用任何保留字。

2. ORA-00904在ALTER TABLE MODIFY 列中

让我们在ALTER DATABASE MODIFY列语句中看一种ORA-00904的情况。

SQL> alter table all_names_1 modify (column fname varchar2(30));
alter table all_names_1 modify (column fname varchar2(30))
                                *
ERROR at line 1:
ORA-00904: : invalid identifier

同样的原因,您不必在语句中添加保留字COLUMN。请删除它。

删除保留字后,我们仍然得到了ORA-00904,这是因为我故意使用了无效的列名。

SQL> alter table all_names_1 modify (fname varchar2(30));
alter table all_names_1 modify (fname varchar2(30))
                                *
ERROR at line 1:
ORA-00904: "FNAME": invalid identifier

表格中未找到列FNAME。有效的列名称应为FIRST_NAME

SQL> alter table all_names_1 modify (first_name varchar2(30));

Table altered.

3. ORA-00904在ALTER TABLE ADD CONSTRAINT NOT NULL中

SQL> create table t1 (c1 number);

Table created.

您可以在ALTER TABLE ADD CONSTRAINT语句中的列上添加UNIQUE约束,如下所示:

SQL> alter table t1 add constraint c1_unique UNIQUE (c1);

Table altered.

但是您不能以相同的方式在列上添加NOT NULL约束。

SQL> alter table t1 add constraint c1_nn NOT NULL (c1);
alter table t1 add constraint c1_nn NOT NULL (c1)
                                    *
ERROR at line 1:
ORA-00904: : invalid identifier

如您所见,SQL解析器抛出ORA-00904以警告语句中使用的标识符名称是非法的。实际上,这是一个语法错误。

为什么?NOT NULL是一个约束,不是吗?当然,NOT NULL是某种约束,但它更像是一个列属性,我们以错误的方式使用它。我们先把它当作约束。

解决方案1:将其视为约束

基本上,Oracle中约束子句有4种子条款

  • inline_constraint
  • out_of_line_constraint
  • inline_ref_constraint
  • out_of_line_ref_constraint

在上面生成ORA-00904的语句中,它是一个out_of_line_constraint子句,它不允许使用NOT NULL。相反,您必须将inline_constraint用于NOT NULL。这就是你在添加约束NOT NULL时看到ORA-00904的原因。

SQL> alter table t1 modify (c1 constraint c1_nn NOT NULL);

Table altered.

我们来检查约束名称。

SQL> select constraint_name from user_constraints where table_name = 'T1';

CONSTRAINT_NAME
------------------------------
C1_UNIQUE
C1_NN

SQL> alter table t1 drop constraint C1_NN;

Table altered.

与其他约束不同,NOT NULL不能是复合(多列)约束,它仅用于其列。

解决方案2:将其视为列属性

严格地说,NOT NULL是一个列属性,因此您不必将其视为约束,即便它可以是约束。

例如,我们可以将列修改为NOT NULL,如下所示:

SQL> alter table t1 modify (c1 NOT NULL);

Table altered.

我们可以看到,我将它视为列属性,而不涉及任何约束子句。我们来检查约束名称。

SQL> select constraint_name from user_constraints where table_name = 'T1';

CONSTRAINT_NAME
------------------------------
C1_UNIQUE
SYS_C0011427

因此,数据库为约束提供了系统生成的名称。让我们进一步看看我们如何放弃约束。

SQL> alter table t1 modify (c1 NULL);

Table altered.

SQL> select constraint_name from user_constraints where table_name = 'T1';

CONSTRAINT_NAME
------------------------------
C1_UNIQUE

也就是说,我们不必知道约束名称就可以删除它,只需恢复属性即可。

E. PL/SQL中的ORA-00904

到目前为止,我们在PL/SQL中有两个错误模式ORA-00904。

  1. PL/SQL中的变量声明顺序不正确
  2. 不带引号的字符串

1. 由于PL/SQL中的变量声明顺序不正确而导致的ORA-00904

假设我们想使用一个涉及这样的变量的游标:

SQL> declare
  2    cursor c1 is select employee_id from hr.employees where department_id = v_num;
  3    v_num number;
  4  begin
  5    v_num := 110;
  6    open c1;
  7  end;
  8  /
  cursor c1 is select employee_id from hr.employees where department_id = v_num;
                                                                          *
ERROR at line 2:
ORA-06550: line 2, column 75:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 2, column 75:
PL/SQL: ORA-00904: "V_NUM": invalid identifier

ORA-06550: line 2, column 16:
PL/SQL: SQL Statement ignored

我们看到了PL/SQL引擎抛出的ORA-00904。其中,ORA-06550表示第2行第75列的标识符在匿名PL/SQL块中被非法使用。

这个错误非常明显,我们在声明它们之前不能使用变量。换句话说,我们必须先声明变量。所以我交换第2和第3行:

SQL> declare
  2    v_num number;
  3    cursor c1 is select employee_id from hr.employees where department_id = v_num;
  4  begin
  5    v_num := 110;
  6    open c1;
  7  end;
  8  /

PL/SQL procedure successfully completed.

2. 由于不带引号的字符串而导致的ORA-00904

在PL/SQL中使用EXECUTE IMMEDIATE时,您可能想知道如何在语句中引用字符串。

SQL> begin
  2    execute immediate 'select nvl(first_name, NO_VALUE) from employees';
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-00904: "NO_VALUE": invalid identifier
ORA-06512: at line 2

在上面的语句中,我们想用函数NVLNO_VALUE字符串替换所有NULL值,但我们犹豫是在语句中引用字符串,该语句现在也是一个字符串。结果,我们得到了ORA-00904。现在问题是,如何引用外部字符串中的字符串以使其正确?

解决方案

答案是,只需将每个单引号复制为两个单引号,以便在字符串中转义单个引号,无论您希望在另一个字符串中显示字符串,尤其是在EXECUTE IMMEDIATE中。

SQL> begin
  2    execute immediate 'select nvl(first_name, ''NO_VALUE'') from employees';
  3  end;
  4  /

PL/SQL procedure successfully completed.

现在代码块很好。

Leave a Reply

Your email address will not be published. Required fields are marked *