PL/SQL触发器解析物化视图BLOB列JSON_OBJECT_T报错ORA-40834

ORA-40834错误的核心原因是：物化视图ON COMMIT刷新时，行级触发器中访问的:NEW.THE_DATA是临时LOB对象，而JSON_OBJECT_T.parse函数不支持对临时LOB执行解析操作。

当你提交源表的更新后，数据库的执行流程是：

1. 触发物化视图的快速刷新逻辑，临时生成JSON格式的BLOB数据；
2. 触发物化视图的BEFORE行级触发器，此时:NEW.THE_DATA指向的是刷新过程中生成的临时LOB（尚未被持久化到物化视图的物理存储中）；
3. JSON_OBJECT_T.parse要求操作的是数据库中持久化的LOB（具备可定位的存储地址），因此抛出ORA-40834错误。

而直接执行select json_object(* ABSENT ON NULL returning blob)后解析的场景中，虽然LOB也是临时的，但解析操作是在SQL上下文完成的，不受临时LOB的限制，因此可以正常运行。

以下是两种可行的解决方式：

方式一：改用AFTER触发器并读取持久化的BLOB #

将触发器改为AFTER行级触发器，此时物化视图的BLOB数据已经被持久化，直接从物化视图中查询已存储的BLOB即可：

CREATE OR REPLACE TRIGGER MV_TBL_SOURCE_T 
AFTER DELETE OR INSERT OR UPDATE ON MV_TBL_SOURCE 
FOR EACH ROW 
DECLARE 
   payload json_object_t;
   v_blob blob;
BEGIN 
    -- 查询物化视图中已持久化的BLOB数据
    SELECT the_data INTO v_blob FROM mv_tbl_source WHERE field1 = :NEW.field1;
    payload := json_object_t.parse(v_blob);
    -- 在此添加你的业务逻辑
END; 
/

方式二：将临时BLOB转换为CLOB后解析 #

在BEFORE触发器中，先将临时BLOB转换为CLOB（注意指定UTF-8字符集，因为JSON_OBJECT生成的BLOB默认采用UTF-8编码），再执行解析：

CREATE OR REPLACE TRIGGER MV_TBL_SOURCE_T 
BEFORE DELETE OR INSERT OR UPDATE ON MV_TBL_SOURCE 
FOR EACH ROW 
DECLARE 
   payload json_object_t;
   v_clob clob;
BEGIN 
    -- 将UTF-8编码的BLOB转换为CLOB
    v_clob := UTL_LOB.convert_to_clob(
        dest_lob => EMPTY_CLOB(),
        src_blob => :NEW.THE_DATA,
        amount => DBMS_LOB.LOBMAXSIZE,
        dest_offset => 1,
        src_offset => 1,
        blob_csid => NLS_CHARSET_ID('AL32UTF8'),
        lang_context => 0,
        warning => 0
    );
    payload := json_object_t.parse(v_clob);
    -- 在此添加你的业务逻辑
    DBMS_LOB.freeTemporary(v_clob); -- 释放临时CLOB资源
END; 
/

可选优化：将物化视图的JSON存储改为CLOB类型 #

如果业务允许，可以直接修改物化视图，将JSON数据存储为CLOB类型，这样触发器中可以直接解析，避免临时LOB的问题：

-- 先删除原物化视图及触发器（如果存在）
DROP TRIGGER MV_TBL_SOURCE_T;
DROP MATERIALIZED VIEW mv_tbl_source;

-- 创建存储CLOB的物化视图
create materialized view mv_tbl_source 
REFRESH FAST
ON COMMIT
as select field1,json_object(* ABSENT ON NULL returning clob) as the_data from tbl_source;

-- 重新创建触发器
CREATE OR REPLACE TRIGGER MV_TBL_SOURCE_T 
BEFORE DELETE OR INSERT OR UPDATE ON MV_TBL_SOURCE 
FOR EACH ROW 
DECLARE 
   payload json_object_t;
BEGIN 
    payload := json_object_t.parse(:NEW.THE_DATA);
    -- 在此添加你的业务逻辑
END; 
/

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