oracle 12c 下动态sql执行的问题

Question

在存储过程中使用动态sql遇到了下述问题，向各位大佬求教……这个问题中的过程，在10G中运行没有问题，但是现在数据库升级了后，就产生了问题。
数据库版本12.1.0.2.0

我在存储过程中，使用了动态sql，目的是统计数据。动态sql大概如下,prm_con 是入参，

SELECT SEQ_table_1_ACE100.NEXTVAL INTO V_ACE100 FROM DUAL;
v_sql := 'insert into table1
            select t2.columns1,
                   t2.columns2,
                   :1,
                   ROW_NUMBER() OVER(ORDER BY columns1) AS XH
              from table2 t2,view1 v1
             where t2.columns3 = v1.columns3
               and ' || prm_con;

EXECUTE IMMEDIATE V_SQL USING v_ace100;

问题是这段sql，执行后， ROW_NUMBER()产生的数值没有写入到表中。而这段sql如果我自己直接执行，是可以插入的。

下面的截图是调试过程的结果

下面的截图是手动执行sql的结果

Answer 1

参考GPT和自己的思路：

根据你提供的信息，问题可能出在动态SQL中的参数使用以及执行权限上。

首先，你在动态SQL中使用了参数 prm_con，并在 EXECUTE IMMEDIATE 语句中使用了 USING 关键字传递参数值。
在 Oracle 12c 中，对于动态 SQL 中的参数使用会有一些影响。由于优化器无法评估动态 SQL 的参数类型和值，因此会使用默认的数据类型位于链接格式字符串的主光标。这可能会导致访问计划和结果不一致，导致插入数据失败。

解决此问题的方法是在动态 SQL 中使用绑定变量来代替参数，绑定变量的值可以在 EXECUTE IMMEDIATE 语句中使用 USING 关键字进行传递。使用绑定变量可以避免动态 SQL 优化问题。

其次，你需要确保代码运行的权限正确。在 Oracle 12c 中，执行动态 SQL 的权限限制要更严格，此外，有些动态 SQL 语句可能不再具有预期的权限。请确保该代码中使用的 SQL 语句和表的权限正确，以及运行代码的用户具有足够的权限来执行代码。

综上所述，你可以通过修改动态 SQL 中的参数使用方式和确保权限的正确性来解决这个问题。