Oracle创建嵌套表对象表报ORA-00904无效标识符如何解决

问题复现 #

前置操作：已预定义tcours、tevaluation、tetudiant、tenseignant四个对象类型，依次执行以下SQL创建REF集合类型与tcours对象类型，所有语句执行成功：

create type t_set_ref_cours as table of ref tcours;
create type t_set_ref_evaluation as table of ref tevaluation;
create type t_set_ref_etudiant as table of ref tetudiant;
create type t_set_ref_enseignant as table of ref tenseignant;

create or replace type tcours as object(numero_cours varchar2(5), 
                                        libelle varchar2(50),credit integer, 
                                        est_pre_requis t_set_ref_cours, 
                                        a_pre_requis t_set_ref_cours,
                                        cours_enseigant t_set_ref_enseignant, 
                                        cours_evaluation t_set_ref_evaluation, 
                                        cours_etudiant t_set_ref_etudiant);
/
desc tcours

desc tcours返回结构如下：

Nom              NULL ? Type                          
---------------- ------ ----------------------------- 
NUMERO_COURS            VARCHAR2(5)                   
LIBELLE                 VARCHAR2(50)                  
CREDIT                  INTEGER()                     
EST_PRE_REQUIS          SYSTEM.T_SET_REF_COURS()      
A_PRE_REQUIS            SYSTEM.T_SET_REF_COURS()      
COURS_ENSEIGANT         SYSTEM.T_SET_REF_ENSEIGNANT() 
COURS_EVALUATION        SYSTEM.T_SET_REF_EVALUATION() 
COURS_ETUDIANT          SYSTEM.T_SET_REF_ETUDIANT() 

后续执行创建对象表语句，尝试为嵌套表属性指定独立存储表：

create table cours of tcours (numero_cours primary key)
nested table a_pre_requis     store as table_a_pre_requis,
nested table est_pre_requis   store as table_est_pre_requis,
nested table cours_etudiant   store as table_cours_etudiant,
nested table cours_enseignant store as table_cours_enseignant,
nested table cours_evaluation store as table_cours_evaluation;
/

执行后抛出错误：

Rapport d'erreur -
ORA-00904:  : identificateur non valide
00904. 00000 -  "%s: invalid identifier"

错误根因 #

核心问题是对象属性名拼写不一致：

• 定义tcours对象类型时，关联教师集合的属性名拼写为cours_enseigant（对比正确拼写enseignant，字母g后漏写了n），从desc返回结果可确认，对象中实际存在的属性名是COURS_ENSEIGANT
• 创建对象表的nested table子句中，引用的属性名写为cours_enseignant（拼写正确，但和对象中实际定义的属性名不匹配），Oracle无法在tcours类型中找到该名称的属性，因此抛出无效标识符错误。

修复方案 #

推荐修正对象类型的属性名拼写，保持语义准确，操作步骤如下：

1. 先删除已创建的存在依赖的类型：

drop type t_set_ref_enseignant force;
drop type t_set_ref_cours force;
drop type t_set_ref_evaluation force;
drop type t_set_ref_etudiant force;
drop type tcours force;

2. 重新创建所有类型，修正tcours中教师集合属性的拼写错误，将cours_enseigant改为cours_enseignant：

create type t_set_ref_cours as table of ref tcours;
create type t_set_ref_evaluation as table of ref tevaluation;
create type t_set_ref_etudiant as table of ref tetudiant;
create type t_set_ref_enseignant as table of ref tenseignant;

create or replace type tcours as object(numero_cours varchar2(5), 
                                        libelle varchar2(50),credit integer, 
                                        est_pre_requis t_set_ref_cours, 
                                        a_pre_requis t_set_ref_cours,
                                        cours_enseignant t_set_ref_enseignant, 
                                        cours_evaluation t_set_ref_evaluation, 
                                        cours_etudiant t_set_ref_etudiant);
/

3. 重新执行创建对象表的SQL，即可正常执行成功。

如果不需要修正属性名拼写，也可以直接修改建表语句中的嵌套表属性名，和对象定义保持一致，将nested table cours_enseignant改为nested table cours_enseigant，也能绕过错误，但会永久保留属性名的拼写问题，不推荐使用。

补充说明：由于本次定义的嵌套表存储的是REF类型指针，创建完cours表后如果需要写入REF数据，需要提前创建tevaluation、tetudiant、tenseignant对应的对象表，否则插入数据时会抛出引用不存在的错误。

内容的提问来源于stack exchange，提问作者Novice BL