Oracle 18c三层表生成JSON遇ORA-40478错误求助

这个问题我之前帮不少开发者踩过坑——Oracle 18c的JSON聚合函数有个容易忽略的细节：哪怕你在内层的JSON_OBJECT或JSON_ARRAYAGG上加了RETURNING CLOB，如果外层的聚合函数没明确指定返回CLOB，还是会触发4000字符的限制，报出ORA-40478错误。

问题根源 #

你当前的查询里，只有内层的JSON_OBJECT和嵌套的JSON_ARRAYAGG加了RETURNING CLOB，但最外层的JSON_ARRAYAGG还是用的默认返回类型VARCHAR2(4000)。当三层表的数据聚合后总长度超过4000字符时，Oracle就会报错，因为它试图把大的CLOB结果塞进有限的VARCHAR2里。

另外，中间层的JSON_ARRAYAGG（regions对应的聚合）如果返回的结果本身就超过4000字符，即使外层处理了，也可能在中间环节触发限制，所以需要逐层指定CLOB返回。

修复后的查询语句 #

给每一层的JSON_ARRAYAGG都加上RETURNING CLOB，同时确保外层的JSON_OBJECT也明确返回CLOB：

SELECT JSON_ARRAYAGG( 
    JSON_OBJECT( 
        'page' VALUE p.name, 
        'regions' VALUE( 
            SELECT JSON_ARRAYAGG( 
                JSON_OBJECT( 
                    'region' VALUE r.name, 
                    'items' VALUE( 
                        SELECT JSON_ARRAYAGG( 
                            JSON_OBJECT( 
                                'item_name' VALUE i.item_name, 
                                'item_value' VALUE i.item_value 
                            ) RETURNING CLOB 
                        ) FROM region_items_tbl i 
                        WHERE i.region_id = r.region_id AND i.enabled = 1
                        RETURNING CLOB -- 内层items的JSON_ARRAYAGG指定CLOB
                    ) 
                ) RETURNING CLOB -- 中间层regions的JSON_OBJECT指定CLOB
            ) FROM page_regions_tbl r 
            WHERE r.page_id = p.page_id AND r.enabled = 1
            RETURNING CLOB -- 中间层regions的JSON_ARRAYAGG指定CLOB
        ) 
    ) RETURNING CLOB -- 外层page的JSON_OBJECT指定CLOB
) RETURNING CLOB -- 最外层的JSON_ARRAYAGG必须指定CLOB
FROM pages_tbl p 
WHERE p.category_id = 10150 AND p.enabled = 1;

额外验证技巧 #

如果你想确认返回的结果确实是CLOB且长度符合预期，可以用DBMS_LOB.GETLENGTH()函数检查：

SELECT DBMS_LOB.GETLENGTH(
    JSON_ARRAYAGG( 
        JSON_OBJECT( 
            'page' VALUE p.name, 
            'regions' VALUE( 
                SELECT JSON_ARRAYAGG( 
                    JSON_OBJECT( 
                        'region' VALUE r.name, 
                        'items' VALUE( 
                            SELECT JSON_ARRAYAGG( 
                                JSON_OBJECT( 
                                    'item_name' VALUE i.item_name, 
                                    'item_value' VALUE i.item_value 
                                ) RETURNING CLOB 
                            ) FROM region_items_tbl i 
                            WHERE i.region_id = r.region_id AND i.enabled = 1
                            RETURNING CLOB
                        ) 
                    ) RETURNING CLOB
                ) FROM page_regions_tbl r 
                WHERE r.page_id = p.page_id AND r.enabled = 1
                RETURNING CLOB
            ) 
        ) RETURNING CLOB
    ) RETURNING CLOB
) AS json_length
FROM pages_tbl p 
WHERE p.category_id = 10150 AND p.enabled = 1;

这样就能看到生成的JSON总长度，确认超过4000字符也能正常返回了。

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