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!

Beda Hammerschmidt · Accepted Answer · 2017-02-16 21:54:31Z

2

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

answered Feb 16, 2017 at 21:54

Beda Hammerschmidt

4463 silver badges6 bronze badges

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

1 Comment

Evoltafreak Over a year ago

Sorry for the late reply. Our systems have been updated to the Release 2 (Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production). Now it works fine. I didn't try the patch that you mentioned. But probably it is the right solution. Thanks for your help.

Ivan Bryzzhin · Accepted Answer · 2017-02-13 13:57:33Z

1

I did run sql:

select f.val from (
    select '{"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"}}}}}'
        as json from dual) v
   ,JSON_TABLE(json,'$' COLUMNS(val VARCHAR(4000) PATH '$.glossary.GlossDiv.GlossList.GlossEntry.GlossTerm')) as f

And it is correct. Then I did make app, as well as you do. And try 12.0.1.1 and 12.0.1.2 drivers. Both working well. App is correct as well as sql.

The only idea that I have about problem is that your json table has different json-schemas or nulls in different rows. Try change you sql to filter by rowid, where row has string as you show above and test app again. Probably when you check sql in IDE (let say Pl/SQl developer) it select only first rows, that why it works, but when you run it in app it try select all rows at once and select this other (or nulls) schemas rows.

answered Feb 13, 2017 at 13:57

Ivan Bryzzhin

2,16521 silver badges29 bronze badges

3 Comments

Evoltafreak Over a year ago

Hey Ivan. Thank you for your quick answer. I tried your sql in my Java Code as well. So I access the JSON directly without my JSON database table. The strange thing is that it sometimes works and it sometimes doesn't work. I tried it a couple of times and the exception sometimes appears like three times, one time or even no exception gets called. I also tried to filter it with ROWNUM = 1, so it just selects one row. The same behavior... It seems to be really randomly. Maybe a bug?

Ivan Bryzzhin Over a year ago

Yes, interesting. I do not gave a value to this, but I also have exceptions in execution: java.sql.SQLRecoverableException: No more data to read from socket. It can happen three, two, one or no one time. About this error i did find answers that is driver problem. Probably may be this is multithreding driver bug.

Evoltafreak Over a year ago

It seems like the JSON_TABLE construct, which maps the JSON structure in a relational way causes the issues. I will do a workaround for this and will use the JSON_VALUE and JSON_QUERY functions to select the specific values. Those functions don't create any problems when I am using them in simultaneously way. But thanks anyway for your contribution.

Collectives™ on Stack Overflow

Querying JSON simultaneously on Oracle DB with JSON_TABLE and Java

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related