Oracle报错‘column not allowed here’，转义字符串后仍未解决求助

错误原因 #

你写的动态SQL语句里直接引用了PL/SQL变量（v_day、cur_d、v_week），但动态SQL是在Oracle的SQL引擎中执行的，SQL引擎无法识别PL/SQL块内的局部变量，会把这些变量名当成表的列名，而你的mytable里并没有这些列，所以触发"column not allowed here"错误。

解决方案 #

方案1：使用绑定变量（推荐） #

用:变量名的形式在动态SQL中定义绑定变量，然后在执行动态SQL时传入PL/SQL变量的值，这是最安全、性能最优的方式，还能避免SQL注入。

修改后的代码如下：

v_query := 'insert into mytable(date_id,date_name,date_date,day_id,year_id,quarter_id,month_id,year_month_id,week_id,year_week_id,weekend_flag,etl_load_time,etl_update_time,source_system_id) values 
(:v_day, to_char(:v_day,''yy/mm/dd''), :v_day, :cur_d, to_number(to_char(:v_day,''yyyy'')), to_number(to_char(:v_day, ''Q'')),
to_number(to_char(:v_day, ''MM'')), to_number(to_char(:v_day, ''YYYYMM'')), :v_week,
to_number(to_char(:v_day,''iyyyiw'')),
case when to_char(:v_day,''DY'') in (''SAT'',''SUN'') then 1 else 0 end, sysdate, sysdate, 10)';

-- 执行动态SQL，传入绑定变量的值
execute immediate v_query using v_day, v_day, v_day, cur_d, v_day, v_day, v_day, v_day, v_week, v_day;

注意：绑定变量的顺序要和动态SQL中出现的顺序完全对应，每个:v_day都需要在using子句中传入一次。

方案2：字符串拼接（不推荐） #

将PL/SQL变量的值直接拼接到动态SQL字符串中，但这种方式存在SQL注入风险，且当变量是日期类型时需要注意格式转换，容易出错。

修改后的代码示例：

v_query := 'insert into mytable(date_id,date_name,date_date,day_id,year_id,quarter_id,month_id,year_month_id,week_id,year_week_id,weekend_flag,etl_load_time,etl_update_time,source_system_id) values 
(''' || to_char(v_day, 'YYYY-MM-DD') || ''', to_char(''' || to_char(v_day, 'YYYY-MM-DD') || ''',''yy/mm/dd''), ''' || to_char(v_day, 'YYYY-MM-DD') || ''', ' || cur_d || ', to_number(to_char(''' || to_char(v_day, 'YYYY-MM-DD') || ''',''yyyy'')), to_number(to_char(''' || to_char(v_day, 'YYYY-MM-DD') || ''', ''Q'')),
to_number(to_char(''' || to_char(v_day, 'YYYY-MM-DD') || ''', ''MM'')), to_number(to_char(''' || to_char(v_day, 'YYYY-MM-DD') || ''', ''YYYYMM'')), ' || v_week || ',
to_number(to_char(''' || to_char(v_day, 'YYYY-MM-DD') || ''',''iyyyiw'')),
case when to_char(''' || to_char(v_day, 'YYYY-MM-DD') || ''',''DY'') in (''SAT'',''SUN'') then 1 else 0 end, sysdate, sysdate, 10)';

execute immediate v_query;

注意：日期类型变量需要用to_char转换成Oracle能识别的字符串格式，并用单引号包裹；数字类型变量直接拼接即可，不需要单引号。

额外提示 #

• 动态SQL中字符串的单引号转义是对的（用两个单引号表示一个单引号），这个部分没有问题。
• 优先使用绑定变量方案，无论是安全性还是性能都更优。

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