如何修复嵌套表类型的ORA-21780: 对象持续时间超限错误

问题场景 #

当处理20000+数据，或因数据量大使函数被多次迭代调用时，以下返回嵌套表类型的PL/SQL函数会抛出ORA-21780: Maximum number of object durations exceeded error异常：

FUNCTION fn_test(test_cursor IN SYS_REFCURSOR)
  RETURN test_LARGE IS
  lv_data test_LARGE := test_LARGE();
  c_limit PLS_INTEGER := 100;
BEGIN
  
  LOOP
    FETCH test_cursor
      BULK COLLECT INTO lv_data
      LIMIT c_limit;

    EXIT WHEN test_cursor%NOTFOUND;
  END LOOP;

  RETURN lv_data;
END fn_test;

错误原因 #

该异常是因为每次执行BULK COLLECT INTO lv_data时，Oracle会为嵌套表创建新的对象实例。循环迭代次数过多后，累积的对象实例超出了Oracle允许的对象持续时间上限，从而触发错误。

修复方案 #

方案1：使用临时集合分批合并（适合大数据量） #

通过临时集合接收每次fetch的数据，合并到主集合后立即清空临时集合，缩短临时对象的生命周期，避免实例累积：

FUNCTION fn_test(test_cursor IN SYS_REFCURSOR)
  RETURN test_LARGE IS
  lv_data test_LARGE := test_LARGE();
  lv_temp test_LARGE := test_LARGE();
  c_limit PLS_INTEGER := 100;
BEGIN
  
  LOOP
    FETCH test_cursor
      BULK COLLECT INTO lv_temp
      LIMIT c_limit;

    EXIT WHEN lv_temp.COUNT = 0;
    
    -- 将临时集合数据追加到主集合
    lv_data := lv_data MULTISET UNION ALL lv_temp;
    -- 清空临时集合，释放资源
    lv_temp.DELETE;
  END LOOP;

  RETURN lv_data;
END fn_test;

方案2：一次性批量获取（适合内存允许的场景） #

如果数据量在内存承受范围内，可以直接一次性BULK COLLECT所有数据，避免循环创建多个对象实例：

FUNCTION fn_test(test_cursor IN SYS_REFCURSOR)
  RETURN test_LARGE IS
  lv_data test_LARGE := test_LARGE();
BEGIN
  
  FETCH test_cursor
    BULK COLLECT INTO lv_data;

  RETURN lv_data;
END fn_test;

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