Thanks for the question, ShivaKumar.

Asked: April 03, 2019 - 1:43 pm UTC

Last updated: March 06, 2020 - 3:22 am UTC

Version: Oracle 12.1

Viewed 10K+ times! This question is

You Asked

Hi TOM,

I am trying to use json_value function infor loop as below.
then getting error as 'PL/SQL: ORA-40442: JSON path expression syntax error'.
Could you please help me with solution. Thanks

set serveroutput on;
DECLARE
p_dn_list CLOB := '[{"long_name" : "countryName", "oid" : "2.5.1.1", "value" : "ID"}
,{"long_name" : "organizationName", "oid" : "2.5.1.2", "value" : "VB"}]';
l_num NUMBER;
l_value varchar2(50);
BEGIN
WITH json AS
(SELECT p_dn_list AS dn_attributes
FROM dual
)
SELECT COUNT(*)INTO l_num
FROM json_table( (SELECT dn_attributes FROM json) , '$[*]'
COLUMNS ( value PATH '$.value' )
);
DBMS_OUTPUT.put_line ('Total entries in array is: ' || l_num);

FOR i IN 0 .. l_num-1
LOOP
SELECT json_value(p_dn_list, '$[i].long_name') into l_value FROM dual ;
DBMS_OUTPUT.put_line ('OSD id is: ' || i);
END LOOP ;
END;
/

Error report:
ORA-06550: line 19, column 53:
PL/SQL: ORA-40442: JSON path expression syntax error
ORA-06550: line 19, column 6:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

and Connor said...

Why not just get the array straight with SQL? eg

SQL> with t as
  2  ( select '[{"long_name" : "countryName", "oid" : "2.5.1.1", "value" : "ID"},{"long_name" : "organizationName", "oid" : "2.5.1.2", "value" : "VB"}]' data from dual )
  3  select jt.*
  4  from   t,
  5         json_table(data, '$[*]'
  6           columns (ln    varchar2(20 char) path '$.long_name',
  7                    oid   varchar2(20 char) path '$.oid',
  8                    val   varchar2(20char)  path '$.value')) jt;

LN                             OID                            VAL
------------------------------ ------------------------------ ------------------------------
countryName                    2.5.1.1                        ID
organizationName               2.5.1.2                        VB

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

To answer your specific question:

Stew Ashton, April 09, 2019 - 10:04 am UTC

Your "path expression" is '$[i].long_name'.

In that expression, the letter 'i' is part of the literal; it has nothing to do with the loop index i.

The path expression must be a literal, so there is no way of getting the value of the loop index into the path expression.

Conclusion: you cannot use json_value in a FOR loop, at least not in that way.

We may ask: why does Oracle only allow literals in path expressions? I think it is because Oracle uses the path expression to choose the execution plan, so the path expression must be known at parse time, not at run time.

Best regards,
Stew

April 10, 2019 - 1:56 am UTC

True - my first attempt at this was:

expr := '$['|| i ||'].long_name'

until I remembered the rule about literals.

ShivaKumar Jaka, April 15, 2019 - 7:07 am UTC

Thanks for your reply on this Tom!

Yes Agreed, we may not use json_value in FOR loop as 'i' is part of literal.
As you suggested I tried to use as below but not worked.

SELECT LOWER(json_value(p_dn_list, '$['|| i ||'].value'))
, LOWER(json_value(p_dn_list, '$['|| i ||'].long_name'))
, LOWER(json_value(p_dn_list, '$['|| i ||'].oid'))
INTO l_rdn_value ,l_longname ,l_oid
FROM dual;

Getting error as below
Error(25,46): PL/SQL: ORA-00907: missing right parenthesis.

April 16, 2019 - 11:42 pm UTC

It must be a literal (not an expression)

Dave, March 05, 2020 - 1:10 pm UTC

Thanks for all the information this forum provides I use it all the time.
This is my first time asking a question so hopefully I am doing it correctly and not supposed to ask a new question.

Anyways to the question.
After a period of time our CTXSYS.CONTEXT_V2 stops working, I dont know if its corrupt or out of date. The data is not updated so i dont see how its out of date. The index is still valid when getting the error. Whats odd though is if I gather stats on the table the index starts working again.

Below is the set up. This is Oracle 12.2 on Linux redhat 7 using ASM storage

CREATE TABLE MC.JW_SEARCH
(
NSN VARCHAR2(13 BYTE) CONSTRAINT JW_SEARCH_NN1 NOT NULL,
NSN_JSON_DOC CLOB CONSTRAINT JW_SEARCH_NN2 NOT NULL
)
LOB (NSN_JSON_DOC) STORE AS BASICFILE (
ENABLE STORAGE IN ROW
CHUNK 8192
RETENTION

ALTER TABLE MC.JW_SEARCH ADD (
CONSTRAINT CK_NSN_SEARCH_ENSURE_JSON
CHECK (NSN_JSON_DOC IS JSON WITH UNIQUE KEYS STRICT)
ENABLE VALIDATE)
/

CREATE INDEX MC.NSN_SEARCH_CTX ON MC.JW_SEARCH
(NSN_DOC)
INDEXTYPE IS CTXSYS.CONTEXT_V2
PARAMETERS('SIMPLIFIED_JSON ')
/

SQL> select *
2 from mc.JW__SEARCH s
3 WHERE JSON_TEXTCONTAINS(nsn_json_doc, '$', 'DEROSE');
from mc.JW_SEARCH s
*
ERROR at line 2:
ORA-40467: JSON_TEXTCONTAINS() cannot be evaluated without a JSON-enabled
context index
ORA-30576: ConText Option dictionary loading error
ORA-30576: ConText Option dictionary loading error
ORA-30576: ConText Option dictionary loading error
ORA-30576: ConText Option dictionary loading error
ORA-30576: ConText Option dictionary loading error

Thanks fro any advice/guidance

March 06, 2020 - 3:22 am UTC

I had a hunt around internally for anything regarding "ORA-30576: ConText Option dictionary loading error".

All entries pointed to an index corruption.

I think you'll need to take this one up with Support.

But I would have thought the more appropriate index would be a specific json search one, ie

CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (po_document) FOR JSON;

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.

Breadcrumb

Question and Answer