Oracle Cloud Fusion多选参数选中两项及以上时报错

问题场景 #

在Oracle Cloud Fusion中构建报表数据模型时，配置了带「全部」选项的多选参数（选择「全部」时参数返回NULL，避免选项过多影响可读性）：

• 选「全部」或单个参数选项时，查询正常运行
• 选中2个及以上选项时，直接触发ORA-00920: invalid relational operator错误，连直接在DUAL中输出参数的语句都会报错

原错误查询写法 #

最初尝试的CASE表达式配合IN的写法无法兼容多值参数：

SELECT DISTINCT
    G.CERTIFICATION_NAME
FROM 
    GRC_ACN_CERTIFICATION_VIEW G
WHERE G.CERTIFICATION_NAME IN 
(
    CASE
        WHEN :cert_name_p IS NULL THEN G.CERTIFICATION_NAME
        WHEN :cert_name_p IS NOT NULL THEN :cert_name_p
    END 
)

甚至测试参数的语句也失败：

SELECT
:cert_name_p
FROM DUAL

可行解决方案 #

通过拆分条件分支，同时处理「全部」（NULL）和多值参数的情况，以下查询可解决问题：

SELECT DISTINCT    
    G.CERTIFICATION_NAME
FROM     
    GRC_ACN_CERTIFICATION_VIEW G
WHERE 
    1=1 AND
    (
        G.CERTIFICATION_NAME IN (:cert_name_p)
        OR LEAST(:cert_name_p) IS NULL
    )

逻辑说明 #

• G.CERTIFICATION_NAME IN (:cert_name_p)：直接匹配多选参数传入的多个值，Oracle Cloud Fusion的参数会自动将多选值解析为IN子句可识别的集合
• LEAST(:cert_name_p) IS NULL：判断参数是否为「全部」（即NULL），因为当参数是NULL时LEAST返回NULL；若传入多个非NULL值，LEAST会返回其中最小值，不会为NULL，以此区分「全部」和多值选择的场景

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