Home » SQL & PL/SQL » SQL & PL/SQL » JSON_TABLE concurrent access issue (Oracle 12C)
JSON_TABLE concurrent access issue [message #659034] Wed, 04 January 2017 13:50 Go to next message
Messages: 1
Registered: January 2017
Junior Member

We are using CLOB with JSON constraint in a table which is retrieved into a JSON_TABLE in a stored procedure.
Query construct is:
Select jt.col1, jt.col2 from JSON_TABLE(...) as jt where jt.col3 = (some value);

The procedure takes a key and does some processing around it.
There is a list of keys which if traversed in sequentially causes no issues. So this procedure executed in single thread from Java based application works fine. If we spawn multiple threads to execute the procedure with different input keys then after a while it get the following exception in application log:
java.sql.sqlexception No more data to read from socket

Following messages are retrieved in database stacktrace:
ORA-01423: error encountered while checking for extra rows in exact fetch
ORA-19114: XPST0003 - error during parsing the XQuery expression
ORA-19202: Error occurred in XML processing

Comment out this select clause and hardcode the value retrieved from JSON_TABLE then this procedure works fine.
We have also replaced the logic to use JSON_VALUE instead and that also works concurrently.

Is there any known issue in use of JSON_TABLE in concurrent access? Are we missing any best practice in JSON_TABLE's use?


Re: JSON_TABLE concurrent access issue [message #659040 is a reply to message #659034] Wed, 04 January 2017 14:41 Go to previous message
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

>We have also replaced the logic to use JSON_VALUE instead and that also works concurrently.
I conclude the code has a BUG!
Previous Topic: SYSDATE - 1 'MM' for JAN showing 0 instead of 12
Goto Forum:

Current Time: Sat Jul 31 06:20:45 CDT 2021