如何在Oracle Bulk Collect中动态使用列名？

你提供的代码执行时触发如下错误：

SET serverout ON 

DECLARE
  r_emp   SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST();
  t_emp   SYS.ODCINUMBERLIST := SYS.ODCINUMBERLIST('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
  r_emp.extend;
    EXECUTE IMMEDIATE
      'SELECT '||t_emp||'  FROM CUSTOMER_PROFILE where '||v_array(i)||' is null'
      BULK COLLECT INTO r_emp(i);
      for k in 1..r_emp(i).count loop
    dbms_output.put_line(v_array(i) || ': ' || r_emp(k));
    end loop;
  END LOOP;
END;

错误报告：

ORA-06550: line 15, column 7:

PLS-00306: wrong number or types of arguments in call to '||'

错误原因分析 #

1. 集合类型直接拼接字符串：t_emp是SYS.ODCINUMBERLIST集合对象，不能直接与字符串用||运算符拼接，需引用集合内的具体元素（如t_emp(1)，因为你仅初始化了一个列名字符串）。
2. Bulk Collect目标类型不匹配：r_emp定义为单级数字列表，但你试图将批量查询结果存入r_emp(i)（单个数字元素），不符合Bulk Collect需接收集合类型变量的要求。
3. 内层循环元素引用错误：即使修正r_emp类型，r_emp(k)的写法也无法正确访问嵌套集合内的元素，需改为r_emp(i)(k)。

修正后的代码 #

SET serverout ON 

DECLARE
  -- 存储列名的字符串集合
  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'
  );
  -- 临时集合，存储单次动态查询的结果
  l_temp_results SYS.ODCINUMBERLIST;
BEGIN
  DBMS_OUTPUT.ENABLE;
  FOR i IN 1..v_array.COUNT LOOP
    -- 动态SQL拼接时引用集合的具体元素
    EXECUTE IMMEDIATE
      'SELECT ' || t_emp(1) || ' FROM CUSTOMER_PROFILE WHERE ' || v_array(i) || ' IS NULL'
      BULK COLLECT INTO l_temp_results;
      
    -- 输出当前列对应的空值记录
    IF l_temp_results.COUNT > 0 THEN
      FOR k IN 1..l_temp_results.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE(v_array(i) || ': ' || l_temp_results(k));
      END LOOP;
    ELSE
      DBMS_OUTPUT.PUT_LINE(v_array(i) || ': 无空值记录');
    END IF;
  END LOOP;
END;
/

关键修改点 #

• 将t_emp改为SYS.ODCIVARCHAR2LIST，贴合存储列名字符串的实际用途。
• 使用临时集合l_temp_results存储单次查询结果，避免嵌套集合的复杂操作。
• 动态SQL中用t_emp(1)获取列名字符串，解决拼接类型不匹配问题。
• 增加空结果判断，避免无数据时触发循环错误。

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