Oracle触发器解析:new.payload的JSON值遇ORA-00904报错咨询

看起来你在触发器里用JSON_TABLE解析:new.payload时遇到了恼人的递归SQL错误，而单独执行JSON_VALUE是正常的。这个问题通常和触发器的执行上下文限制有关，下面给你两个可行的解决方案：

方案一：改用JSON_VALUE逐个提取字段 #

既然你已经验证JSON_VALUE在SQL Developer中可以正常工作，那在触发器里直接用它来逐个提取每个字段是最稳妥的方式，避开JSON_TABLE带来的上下文问题：

DECLARE
  v_timestamp VARCHAR2(80);
  v_value NUMBER;
  v_unit VARCHAR2(80);
BEGIN
  -- 逐个提取JSON字段
  v_timestamp := JSON_VALUE(:new.payload, '$.timestamp');
  v_value := JSON_VALUE(:new.payload, '$.value');
  v_unit := JSON_VALUE(:new.payload, '$.unit');
  
  -- 这里添加你的存储逻辑，比如插入到其他表或者更新当前表字段
  -- INSERT INTO your_table (timestamp_col, value_col, unit_col) VALUES (v_timestamp, v_value, v_unit);
END;

这种方式简单直接，而且在触发器的递归执行上下文里兼容性更好，不会触发ORA-00604这类错误。

方案二：调整JSON_TABLE的使用方式（如果一定要用） #

如果坚持要用JSON_TABLE，可以先把:new.payload赋值给一个局部变量，再用这个变量来解析，这样能规避触发器上下文的一些限制：

DECLARE
  v_payload CLOB := :new.payload;
  v_timestamp VARCHAR2(80);
  v_value NUMBER;
  v_unit VARCHAR2(80);
BEGIN
  SELECT timestamp, value, unit
  INTO v_timestamp, v_value, v_unit
  FROM json_table(v_payload, '$' 
    columns ( 
      timestamp VARCHAR2(80) path '$.timestamp', 
      value NUMBER path '$.value', 
      unit VARCHAR2(80) path '$.unit' 
    ) 
  ) jt;
  
  -- 后续存储逻辑
END;

错误原因说明 #

你遇到的ORA-00604: 递归SQL级别1出现错误加上ORA-00904: "DECL_OBJ#": 标识符无效，本质是因为触发器执行属于递归SQL场景（Oracle内部会递归处理触发器的执行），而JSON_TABLE在这种递归上下文中的解析逻辑可能触发了Oracle内部的对象引用问题，这可能是特定版本的兼容性bug或者上下文限制。改用标量的JSON_VALUE函数就不会有这个问题，因为它的执行逻辑更简单，不需要创建临时表对象。

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