Querying JSON simultaneously on Oracle DB with JSON_TABLE and Java

Question

I actually want to query a JSON-String which is stored in an Oracle Database, using the construct JSON_TABLE. This works pretty well.

SQL Query

SELECT f.val 
from JSON, 
     JSON_TABLE(json,'$' COLUMNS(val VARCHAR(4000) PATH '$.glossary.GlossDiv.GlossList.GlossEntry.GlossTerm')) as f

JSON-String in DB

(It is by the way the example JSON from json.org/example.html)

{"glossary":{"title":"example glossary","GlossDiv":{"title":"S","GlossList":{"GlossEntry":{"ID":"SGML","SortAs":"SGML","GlossTerm":"Standard Generalized Markup Language","Acronym":"SGML","Abbrev":"ISO 8879:1986","GlossDef":{"para":"A meta-markup language, used to create markup languages such as DocBook.","GlossSeeAlso":["GML","XML"]},"GlossSee":"markup"}}}}}

Now I want execute the query in a normal Java Application. I use it simultaneously in five Threads. That is how I can reproduce the problem. In my original use case I press a button really quickly on a website which executes this query.

JsonRunnable.java

public class JsonRunnable implements Runnable {

    public void run() {

    try {

        List<String> list = new ArrayList<String>();

        java.util.Properties connProperties = new java.util.Properties();
        connProperties.put("user", "");
        connProperties.put("password", "");

        Class.forName("oracle.jdbc.driver.OracleDriver");
        String database =
                "jdbc:oracle:thin:@myserver.com:1521/DB";
        Connection conn = DriverManager.getConnection(database, connProperties);

        String sql = "SELECT f.val from JSON, JSON_TABLE(json,'$' COLUMNS(val VARCHAR(4000) PATH '$.glossary.GlossDiv.GlossList.GlossEntry.GlossTerm')) as f";
        PreparedStatement s1 = conn.prepareStatement(sql);

        s1.execute(sql);
        ResultSet rs = s1.getResultSet();

        while (rs.next()) {
            list.add(rs.getString(1));
        }

        s1.close();
        conn.close();

        System.out.println(list.get(0));

    } catch (Exception ex) {
        System.out.println(ex);
    }
}

}

Main.java

public class Main {
public static void main(String[] args) {
    for(int i = 0;i < 5;i++){
        new Thread(new JsonRunnable()).start();
    }
}
}

Now, I get this error, which tells me that something failed during parsing and processing the XML (The error message is in german but you can see the ORA Error Message):

java.sql.SQLException: ORA-19114: XPST0003 - Fehler beim Parsen des XQuery Ausdrucks: 
ORA-19202: Fehler bei XML-Verarbeitung 
jzntSCV1:invState2 aufgetreten

Oracle Driver: OJDBC 7 12.1.0.1

Java: 1.8

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

Can someone help me in this case? I am actually really lost how to solve this problem. Thank you guys a lot!

Answer 1

Have you installed the latest patch set (Patch 24968615: DATABASE PROACTIVE BUNDLE PATCH 12.1.0.2.170117). This should fix the issue.