oracle trigger and json_value

Hi I want to store some values from :new.payload via an trigger.

this works in sql-developer but not in a trigger…

select json_value('{"timestamp":"2019-05-09T14:00:00Z","value":0,"unit":"W/m²"}', '$.unit') from dual;

​x
 
select json_value('{"timestamp":"2019-05-09T14:00:00Z","value":0,"unit":"W/m²"}', '$.unit') from dual;​

‘{“timestamp”:”2019-05-09T14:00:00Z”,”value”:0,”unit”:”W/m²”}’ is my :new.payload

i tried also:

SELECT unit INTO v_unit
    FROM
        json_table(:new.payload, '$'
            columns (
                    timestamp VARCHAR2(80) path '$.timestamp',
                    value NUMBER path '$.value',
                    unit VARCHAR2(80) path '$.unit'
                    )
) jt;

 
SELECT unit INTO v_unit    FROM        json_table(:new.payload, '$'            columns (                    timestamp VARCHAR2(80) path '$.timestamp',                    value NUMBER path '$.value',                    unit VARCHAR2(80) path '$.unit'                    )) jt;​

i get the error ORA-00904: “DECL_OBJ#” ;/

(Error: ORA-00604: Recursive Error SQL-Level 1 ORA-00904: “DECL_OBJ #”: invalid ID)

Answer

This is a wild guess, but try this:

1.Open SQL Developer Set the PLScope identifiers parameter (Tools > Preferences > Database > PL/SQL Compiler > PLScope identifiers) from All to None.

Close and open the SQL Developer

Advertisement

Answer