PL/SQL存储过程UPDATE语句报ORA-00902无效数据类型错误求助

问题描述 #

编写存储过程时，UPDATE语句抛出ORA-00902: invalid datatype错误，相关代码如下：

建表语句 #

CREATE TABLE "TABLE1" 
(   
    "USER_ID" NUMBER, 
    "COMMENTS" VARCHAR2(20 BYTE)
)

存储过程代码 #

PROCEDURE pr_test
AS
    v_table UserRecordTable;
    v_user_id_list SYS.ODCINUMBERLIST;
BEGIN
    select USER_ID bulk collect into v_table 
    from TABLE1;

    select USER_ID bulk collect into v_user_id_list 
    from TABLE1 
    where USER_ID in (select USERID FROM TABLE(v_table)); -- 此处无问题

FOR i IN 1..v_user_id_list.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Column1: ' || v_user_id_list(i)); -- 打印正常
END LOOP;

UPDATE TABLE1    -- 此行抛出错误
SET COMMENTS= 'NO NO'
where USER_ID in (select USERID FROM TABLE(v_table));
END pr_der_test;

包规范中的类型定义 #

TYPE UserRecordType IS RECORD (
    USERID TABLE1.USER_ID%TYPE
  ); 
TYPE UserRecordTable IS TABLE OF UserRecordType;

错误原因 #

你定义的UserRecordType是PL/SQL专属的RECORD类型，这种类型仅在PL/SQL引擎中有效，无法被SQL引擎识别。当你在UPDATE语句的SQL部分使用TABLE(v_table)时，SQL引擎无法解析这个自定义RECORD集合的类型，因此抛出无效数据类型错误。

解决方案 #

提供三种可行的解决思路：

方案1：改用SQL级别的对象类型和集合类型 #

创建SQL级别的对象和集合类型（而非PL/SQL的RECORD），让SQL引擎可以识别：

1. 先创建SQL对象类型和集合类型：

CREATE OR REPLACE TYPE UserRecordObj AS OBJECT (
    USERID NUMBER
);
/
CREATE OR REPLACE TYPE UserRecordTable AS TABLE OF UserRecordObj;
/

2. 修改包规范，使用上述SQL级别的UserRecordTable类型，替换原来的PL/SQL RECORD定义。

3. 调整存储过程中的BULK COLLECT语句，正确填充对象集合：

select UserRecordObj(USER_ID) bulk collect into v_table 
from TABLE1;

方案2：直接使用内置集合类型SYS.ODCINUMBERLIST #

既然只需要存储USER_ID，可跳过自定义RECORD集合，直接用Oracle内置的SYS.ODCINUMBERLIST简化代码：

PROCEDURE pr_test
AS
    v_user_id_list SYS.ODCINUMBERLIST;
BEGIN
    select USER_ID bulk collect into v_user_id_list 
    from TABLE1;

    FOR i IN 1..v_user_id_list.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Column1: ' || v_user_id_list(i));
    END LOOP;

    UPDATE TABLE1    
    SET COMMENTS= 'NO NO'
    where USER_ID in (select column_value FROM TABLE(v_user_id_list));
END pr_test;

方案3：使用FORALL批量更新替代SQL中的集合查询 #

如果必须保留自定义RECORD集合，可用PL/SQL的FORALL语句实现批量更新，避免在SQL中直接引用PL/SQL集合：

PROCEDURE pr_test
AS
    v_table UserRecordTable;
BEGIN
    select USER_ID bulk collect into v_table 
    from TABLE1;

    FORALL i IN 1..v_table.COUNT
        UPDATE TABLE1
        SET COMMENTS = 'NO NO'
        WHERE USER_ID = v_table(i).USERID;
END pr_test;

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