oracle trigger and json_value

Question

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;

'{"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;

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

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

Please edit the question and show the entire error including call stack. Thanks — OldProgrammer
– OldProgrammer, Commented May 9, 2019 at 15:42

OldProgrammer · Accepted Answer · 2019-05-09 16:46:36Z

6

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

edited May 9, 2019 at 16:46

answered May 9, 2019 at 15:46

OldProgrammer

12.2k4 gold badges27 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ted Mosby Over a year ago

that's it? Really? okay ... why does that work now?

OldProgrammer Over a year ago

I looked for that error on the Oracle support site and found notes about it. It is an internal oracle error. You would need an oracle support account to get the details.

OldProgrammer Over a year ago

You can read the details here - drive.google.com/file/d/1Vy3GRqUezdhRb-FENY8eurGvx6UIUZUb/…

Collectives™ on Stack Overflow

oracle trigger and json_value

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related