PLSQL PIVOT聚合字符串触发ORA-00932数据类型不一致错误解决

问题场景 #

需要通过PL/SQL的PIVOT功能实现每个问题对应一列、答案列于下方的效果，以字符串作为聚合值，且每个单元格仅存在一个值。

初始代码与报错 #

初始执行的SQL代码：

SELECT  EFOLDERID,
        SUN,
        LEGALNAME,
        QLIST_NAME
FROM    V_CCRA_EXPORT_ANSWERS
PIVOT
(
    MAX(DERIVED_ANSWER) -- 注：因每个单元格仅一个值，使用MAX/MIN/AVG等聚合函数均可
    FOR QUESTION_NUMBER
    IN  (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
);

触发报错：

ORA-00932: inconsistent datatypes: expected - got NCLOB
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

注：DERIVED_ANSWER在视图V_CCRA_EXPORT_ANSWERS中已被转换为VARCHAR(1000)类型。

排查与原因定位 #

1. 得知QUESTION_NUMBER为VARCHAR(20)类型后，修改SQL将IN子句中的值改为字符串形式：

SELECT  *
FROM    V_CCRA_EXPORT_ANSWERS
PIVOT
(
    MAX(DERIVED_ANSWER) -- 注：因每个单元格仅一个值，使用MAX/MIN/AVG等聚合函数均可
    FOR (QUESTION_NUMBER)
    IN  ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12')
);

但仍触发相同错误。

2. 最终确认错误根源：PIVOT的输入查询中不能包含NCLOB类型字段。即便未在SELECT中显式引用该字段，若基础视图包含NCLOB类型字段，使用SELECT *时会将其带入PIVOT处理，导致类型不兼容错误。

解决方法 #

修改PIVOT的基础查询，仅保留需要的字段（明确排除所有NCLOB类型字段），示例代码如下：

SELECT  *
FROM    (
    -- 仅选择需要的字段，排除视图中的NCLOB字段
    SELECT  EFOLDERID,
            SUN,
            LEGALNAME,
            QLIST_NAME,
            QUESTION_NUMBER,
            DERIVED_ANSWER
    FROM    V_CCRA_EXPORT_ANSWERS
)
PIVOT
(
    MAX(DERIVED_ANSWER)
    FOR QUESTION_NUMBER
    IN  ('1' AS Q1, '2' AS Q2, '3' AS Q3, '4' AS Q4, '5' AS Q5, '6' AS Q6, 
         '7' AS Q7, '8' AS Q8, '9' AS Q9, '10' AS Q10, '11' AS Q11, '12' AS Q12)
);

注：若视图中存在NCLOB字段，必须在子查询中明确筛选所需字段，避免使用SELECT *。

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