Oracle PL/SQL中EXECUTE IMMEDIATE使用集合作为表的拼接报错咨询

报错根因 #

你遇到的Wrong number or types of arguments in call to ||错误，核心原因是直接将PL/SQL集合变量v_node和字符串做拼接操作，PL/SQL不支持集合类型与字符串直接拼接，除此之外你的代码还有多处语法、逻辑错误，完整问题列表如下：

所有问题清单 #

• 集合类型使用错误：你定义的是INDEX BY PLS_INTEGER的关联数组，这类集合不能直接用TABLE()函数在SQL语句中调用，需要改成嵌套表类型
• 类型定义语法错误：
  • 记录定义中node_rowid Hr node_rowid%type少了点号，应为Hr.node_rowid%type
  • 字段名拼写错误Node_+type应为node_type
• 动态SQL拼接错误：
  • 表别名拼接时缺空格：V_tab || 'R '会生成UC_UBRR非法表名，应为V_tab || ' R '
  • 条件语句多了无效字符a：node_type = ' UBR' a里的a是多余字符
  • 集合变量直接拼接进SQL字符串，应当作绑定变量传入
• 变量名拼写错误：声明的变量是V_nrf_flg，USING子句里误写为v_nrf_flag
• 集合作为绑定变量传入时，12c以下版本的Oracle要求集合类型定义在SQL层级（而非PL/SQL块内）

修复后代码 #

-- 如果你用的是Oracle 12c以下版本，需要先在SQL层执行以下语句创建类型
-- CREATE OR REPLACE TYPE t_approved_node AS OBJECT (
--   node_rowid ROWID,
--   node_type VARCHAR2(100)
-- );
-- /
-- CREATE OR REPLACE TYPE t_val AS TABLE OF t_approved_node;
-- /

Declare
-- 12c及以上版本可以直接在块内定义嵌套表类型
Type t_approved_node is record( 
  node_rowid Hr.node_rowid%type, 
  node_type hr.node_type%type
);
Type t_val is table of t_approved_node; -- 去掉INDEX BY改为嵌套表

V_node t_val;
V_tab varchar2(20);
V_col varchar2(400);
V_nrf_flg hr.hr_flag%type;
V_ubrf_flg hr.hr_flag%type := 3;
V_col_str varchar2(4000);

Begin
  Select hr_flag into v_nrf_flg from hr where rownum = 1; -- 补充rownum限制避免单行查询返回多行报错

  Select h.node_rowid, h.node_type bulk collect into v_node 
  from hr h, hr_attr_wfm haw
  Where h.hr_relation_id = haw.uc_hr_relation_id
  And h.node_type = 'UBR';

  V_tab := 'UC_UBR';
  Select listagg(column_name, ',') within group(order by column_id)
  Into v_col from user_tab_columns where table_name = v_tab;

  V_col_str := regexp_replace(v_col, 'HR_FLAG', to_char(v_ubrf_flg)); -- 显式类型转换避免隐式报错

  Execute immediate 'insert into ' || v_tab || '(' || V_col || ') '
    || 'select '|| v_col_str || ' from ' || V_tab || ' R '
    || q'[ where node_type = 'UBR' and hr_flag =:1 and exists( ]'
    || 'select 1 from table(:2) y where y.node_rowid = R.node_rowid )'
  Using v_nrf_flg, v_node; -- 集合作为第二个绑定变量传入

End;
/

关键说明 #

• 集合变量不再拼接进SQL，改为绑定变量传入，既解决了拼接报错，也避免了SQL注入风险、提升了执行效率
• 修正了所有拼写、语法错误，补充了边界处理逻辑避免运行时异常

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