获取单表各列空值样本数据时遇ORA-22165错误及缓冲区溢出问题

需求背景 #

从CUSTOMER_PROFILE表中，针对CUST_TYPE、CERT_TYPE_NAME、CERT_NBR、NEW_PARENT_CUST_ID这4列，每列获取4条该列为空时的CUST_ID样本数据。

原代码问题 #

执行以下PL/SQL代码时触发ORA-22165错误；若改用r_emp.count循环则出现缓冲区溢出问题。

原代码 #

DECLARE
  r_emp   SYS.ODCIVARCHAR2LIST;
  t_emp   SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST('CUST_ID');
  v_array SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
    'CUST_TYPE',
    'CERT_TYPE_NAME',
    'CERT_NBR',
    'NEW_PARENT_CUST_ID'
  );
BEGIN
  DBMS_OUTPUT.ENABLE;
  FOR i IN 1..v_array.COUNT LOOP
    FOR j IN 1..t_emp.COUNT LOOP
      EXECUTE IMMEDIATE
        'SELECT '||t_emp(j)||'  FROM CUSTOMER_PROFILE where '||v_array(i)||' is null'
        BULK COLLECT INTO r_emp;
      FOR k IN 1..4 LOOP
        dbms_output.put_line(v_array(i) || ': ' || r_emp(k));
      END LOOP;
    END LOOP;
  END LOOP;
END;
/

错误信息（中文翻译） #

ORA-22165: 指定的索引 [32768] 必须在 [1] 到 [32767] 的范围内
ORA-06512: 在第14行
22165. 00000 -  "指定的索引 [%s] 必须在 [%s] 到 [%s] 的范围内"
*原因:    指定的索引不在要求的范围内。
*操作:    确保指定的索引在要求的范围内。

错误原因分析 #

1. ORA-22165错误：SYS.ODCIVARCHAR2LIST是Oracle内置变长数组，最大容量仅为32767个元素。原代码中BULK COLLECT会把某列所有空值对应的CUST_ID全部收集到r_emp中，当该列空值数量超过32767时，就会触发索引越界错误。
2. 缓冲区溢出：DBMS_OUTPUT默认缓冲区较小（通常为20000字节），如果用r_emp.count循环输出大量数据，会超出缓冲区容量导致溢出。

解决方案 #

核心思路是只获取需要的4条样本数据，避免一次性加载大量数据，同时优化输出逻辑。

方案1：修改动态SQL限制返回行数（推荐） #

直接在动态SQL中添加行限制，只获取前4条数据，这样BULK COLLECT最多收集4条元素，既不会触发集合容量限制，也减少输出数据量。

修改后的代码

DECLARE
  r_emp   SYS.ODCIVARCHAR2LIST;
  t_emp   SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST('CUST_ID');
  v_array SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
    'CUST_TYPE',
    'CERT_TYPE_NAME',
    'CERT_NBR',
    'NEW_PARENT_CUST_ID'
  );
BEGIN
  -- 增大输出缓冲区，避免少量输出时溢出
  DBMS_OUTPUT.ENABLE(buffer_size => 1000000);
  FOR i IN 1..v_array.COUNT LOOP
    FOR j IN 1..t_emp.COUNT LOOP
      EXECUTE IMMEDIATE
        -- Oracle 12c及以上用FETCH FIRST，低版本替换为WHERE ROWNUM <= 4
        'SELECT '||t_emp(j)||' FROM CUSTOMER_PROFILE where '||v_array(i)||' is null FETCH FIRST 4 ROWS ONLY'
        BULK COLLECT INTO r_emp;
      -- 循环时取实际返回行数和4的较小值，避免空值不足4条时报错
      FOR k IN 1..LEAST(r_emp.COUNT, 4) LOOP
        dbms_output.put_line(v_array(i) || ': ' || r_emp(k));
      END LOOP;
    END LOOP;
  END LOOP;
END;
/

关键修改点

• 动态SQL添加FETCH FIRST 4 ROWS ONLY（Oracle 12c+），低版本可替换为WHERE ROWNUM <= 4，限制仅返回4条样本。
• 输出循环使用LEAST(r_emp.COUNT, 4)，处理某列空值不足4条的场景，避免索引越界。
• 可选：增大DBMS_OUTPUT.ENABLE的buffer_size参数，提升输出缓冲区容量。

方案2：使用游标逐行获取数据 #

如果不想使用集合，可以用游标逐行获取4条数据，更节省内存：

DECLARE
  v_cust_id CUSTOMER_PROFILE.CUST_ID%TYPE;
  v_array SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
    'CUST_TYPE',
    'CERT_TYPE_NAME',
    'CERT_NBR',
    'NEW_PARENT_CUST_ID'
  );
  v_sql VARCHAR2(1000);
  c SYS_REFCURSOR;
BEGIN
  DBMS_OUTPUT.ENABLE(buffer_size => 1000000);
  FOR i IN 1..v_array.COUNT LOOP
    v_sql := 'SELECT CUST_ID FROM CUSTOMER_PROFILE where '||v_array(i)||' is null FETCH FIRST 4 ROWS ONLY';
    OPEN c FOR v_sql;
    -- 最多取4条数据
    FOR k IN 1..4 LOOP
      FETCH c INTO v_cust_id;
      EXIT WHEN c%NOTFOUND;
      dbms_output.put_line(v_array(i) || ': ' || v_cust_id);
    END LOOP;
    CLOSE c;
  END LOOP;
END;
/

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