Oracle参数化游标执行时PRODUCT_CODE过滤条件未生效问题

你遇到的是PL/SQL的标识符命名优先级冲突问题：
你定义的游标参数product_code和关联表ETDW.MFE_AR_ACTION_DETAILS的列名PRODUCT_CODE完全重名，Oracle在解析SQL语句中的标识符时，会优先匹配当前上下文的表列名，而非PL/SQL的参数/变量名。
因此你写的act.PRODUCT_CODE = product_code实际会被Oracle解析为act.PRODUCT_CODE = act.PRODUCT_CODE，这个条件恒成立，所以完全没有过滤效果，返回所有满足其他关联条件的行。
而你单独在PL/SQL块外执行查询时，没有重名的游标参数，用的是字面量或独立变量，所以可以正常返回预期的3条结果。

有两种常用的方案可以解决这个问题：

方案1：修改游标参数命名，避免和列名冲突 #

这是最推荐的方案，统一给参数加前缀（比如p_代表parameter），从根源上避免命名冲突：

CURSOR cur_action ( p_product_code VARCHAR2(100) , p_action_master_list VARCHAR2(100)) 
IS 
        SELECT
        act.ACTION_DETAIL_KEY,
        act.ACTION_MASTER_KEY,
        act.PRODUCT_CODE,
        act.REF_ACTION_DETAIL_KEY
        FROM   XMLTABLE(p_action_master_list) x
        JOIN   ETDW.MFE_AR_ACTION_DETAILS act ON TO_NUMBER(x.COLUMN_VALUE) = act.ACTION_MASTER_KEY
        WHERE  1=1
        AND    act.LAST_FLAG = 'Y'
        AND    act.PRODUCT_CODE = p_product_code;

调用游标时不需要做任何修改，还是传入原来的两个参数即可。

方案2：给参数添加限定符 #

如果不想修改参数名，也可以明确指定标识符的所属范围，给参数加上游标名作为前缀：

WHERE  1=1
AND    act.LAST_FLAG = 'Y'
AND    act.PRODUCT_CODE = cur_action.product_code;

内容的提问来源于stack exchange，提问作者Martin Fedy Fedorko