PLSQL错误PLS-00457求助：动态插入集合数据编译失败

错误原因 #

你定义的TEST_FS_ARRAY_TYPE是PL/SQL专属的关联数组（带INDEX BY BINARY_INTEGER），这种类型仅在PL/SQL引擎中有效，SQL引擎无法识别。当你在动态SQL的table(:TEST_FS_ARRAY_OBJ)中使用该类型时，SQL引擎无法解析，因此抛出PLS-00457错误。

解决方案 #

要让SQL引擎识别集合类型，必须使用SQL级别的集合类型（如嵌套表或VARRAY），具体步骤如下：

1. 创建SQL级别的对象类型和嵌套表类型

CREATE OR REPLACE TYPE VISION.TEMP_TEST_TABLE_ROW AS OBJECT (
  A NUMBER(10),
  B NUMBER(10)
);
/

CREATE OR REPLACE TYPE VISION.TEMP_TEST_TABLE_NT AS TABLE OF VISION.TEMP_TEST_TABLE_ROW;
/

2. 修改存储过程，替换集合类型并调整逻辑

CREATE OR REPLACE PROCEDURE VISION.PR_TEST_FORALL AUTHID CURRENT_USER Is

v_SQL1 varchar2(1000) := 'select rownum, rownum from dual connect by rownum <= 11000';
-- 动态指定表名的示例（如果需要）
v_target_table varchar2(100) := 'TEMP_TEST_TABLE';

TYPE generic_Looper_CurType IS REF CURSOR;
generic_Looper_Cursor    generic_Looper_CurType;
        
-- 使用SQL级嵌套表类型，初始化空集合
TEST_FS_ARRAY_OBJ VISION.TEMP_TEST_TABLE_NT := VISION.TEMP_TEST_TABLE_NT();
        
FETCH_SIZE NUMBER := 10000;
BEGIN

open generic_Looper_Cursor for v_SQL1;

  loop
    -- 批量收集时自动转换为OBJECT类型存入嵌套表
    FETCH generic_Looper_Cursor BULK COLLECT
      INTO TEST_FS_ARRAY_OBJ LIMIT fetch_size;
      
    -- 动态SQL中使用table函数处理SQL级集合
    execute immediate
    'insert into ' || v_target_table || ' select t.a, t.b from table(:TEST_FS_ARRAY_OBJ) t'
    using TEST_FS_ARRAY_OBJ;
      
    EXIT WHEN generic_Looper_Cursor%NOTFOUND;
   
  END LOOP;
  
  COMMIT; -- 统一提交，减少频繁提交的性能损耗
  
End;
/

额外优化说明 #

• 原代码中存在重复的COMMIT语句，建议移除循环内的提交，改为循环结束后一次性提交，提升性能。
• 如果需要动态指定表，只需将目标表名存入变量，在动态SQL中拼接即可（如示例中的v_target_table）。

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