Oracle Bulk Collect操作报ORA-21700对象不存在或标记为删除问题咨询

问题场景 #

代码逻辑为将入参传入的PL/SQL关联数组ARRAY1，通过TABLE()函数转成表结构后用BULK COLLECT截取部分字段存入ARRAY2，执行时抛出21700错误，相关代码与类型声明如下：

PROCEDURE PROC1(ARRAY1 IN T_ARRAY1 )
    ARRAY2 T_ARRAY2;
IS
    SELECT COL1,COL2
    BULK COLLECT INTO ARRAY2
    FROM TABLE(ARRAY1);
END;

TYPE T_ARRAY1_REC IS RECORD(COL1 NUMBER,COL2 NUMBER,COL3 NUMBER);    
TYPE T_ARRAY1 IS TABLE OF T_ARRAY1_REC INDEX BY BINARY_INTEGER;

TYPE T_ARRAY2_REC IS RECORD(COL1 NUMBER,COL2 NUMBER);    
TYPE T_ARRAY2 IS TABLE OF T_ARRAY2_REC INDEX BY BINARY_INTEGER;

核心触发原因 #

• 类型作用域不兼容：TABLE()集合函数只能识别SQL层全局定义的嵌套表、VARRAY类型，当前使用的T_ARRAY1、T_ARRAY2都是PL/SQL作用域下声明的关联数组（带INDEX BY BINARY_INTEGER的索引表），属于PL/SQL引擎私有内存结构，SQL引擎无法访问、解析这类类型定义，自然判定对象不存在抛出21700错误。
• 内存指针访问异常：就算把类型换成SQL层可见的嵌套表，11g及更早Oracle版本中，IN模式传入的集合参数如果没有在当前存储过程内做过显式初始化、内存分配，SQL引擎调用TABLE()访问时拿不到集合的有效内存地址，也会抛出对象已删除/不存在的同类错误。
• 额外逻辑隐患：两个集合的记录结构字段数不一致（T_ARRAY1_REC为3字段、T_ARRAY2_REC为2字段），就算类型兼容问题解决，直接查询也会触发类型不匹配报错，无法走到正常赋值逻辑。

正确实现方案 #

PL/SQL关联数组之间的批量字段拷贝不需要绕SQL层调用TABLE()函数，直接用PL/SQL原生遍历赋值效率更高，也不会出现跨引擎访问的类型错误：

PROCEDURE PROC1(ARRAY1 IN T_ARRAY1 )
IS
    ARRAY2 T_ARRAY2;
BEGIN
    -- 判空后遍历赋值，兼容稀疏关联数组场景
    IF ARRAY1.COUNT > 0 THEN
        FOR idx IN ARRAY1.FIRST .. ARRAY1.LAST LOOP
            IF ARRAY1.EXISTS(idx) THEN
                ARRAY2(idx).COL1 := ARRAY1(idx).COL1;
                ARRAY2(idx).COL2 := ARRAY1(idx).COL2;
            END IF;
        END LOOP;
    END IF;
END;
/

如果必须使用BULK COLLECT语法、且要通过SQL引擎处理集合，需要先把所有集合、记录类型都创建为SQL层全局对象，不能只在PL/SQL块/包内声明：

-- SQL层创建对象类型、嵌套表类型
CREATE OR REPLACE TYPE T_ARRAY1_REC AS OBJECT(COL1 NUMBER,COL2 NUMBER,COL3 NUMBER);
/
CREATE OR REPLACE TYPE T_ARRAY1 IS TABLE OF T_ARRAY1_REC;
/
CREATE OR REPLACE TYPE T_ARRAY2_REC AS OBJECT(COL1 NUMBER,COL2 NUMBER);
/
CREATE OR REPLACE TYPE T_ARRAY2 IS TABLE OF T_ARRAY2_REC;
/

类型创建为SQL全局对象后，原TABLE()函数的写法才能正常被SQL引擎识别执行。

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