Forum Stats

  • 3,782,440 Users
  • 2,254,645 Discussions
  • 7,880,078 Comments

Discussions

JSON_TABLE used for IN clause

shiva887
shiva887 Member Posts: 45 Blue Ribbon
edited Dec 16, 2019 7:02AM in SQL & PL/SQL

DB Version: Oracle Database 12c Enterprise Edition Release 12.2 - 64bit Production

Can someonce let me know if the JSON_TABLE can be used as below. I get a weird internal error.

UPDATE         UNF_WEB_TEAM_MEMBER XSET         (X.DFG_ID) = (        SELECT         DISTINCT RA.DFG                                    FROM         MS_REP_ADDR RA                                    WHERE        RA.ZIP IN (SELECT jt.zip FROM json_table(x.DATA.AddressList, '$[*]' COLUMNS (zip VARCHAR2(5) PATH '$.Zip')) jt)        );

My table unf_web_team_member has a data column which has JSON saved in it. The AddressList element in JSON has multiple addresses of which i am trying to match zip codes. Below is the error i get when executing the query.error.png

Tagged:

Answers

  • GregV
    GregV Member Posts: 3,075 Gold Crown
    edited Dec 16, 2019 7:02AM

    Hi,

    It looks like Bug 24376875:

    Bug 24376875 : JSON_TABLE QUERY AS NON-SYS USER ORA-600 [QMXPTADDDEPENDENCY1]

    Strangely enough there seems to be a patch only for 12.1 versions.