Soda 1.1.7 throws Exceptions with 21c JSON datatype

[osawyerr]

It's not clear what combination of driver version to use for soda 1.1.7 and 21c and collection is using the new oracle "JSON" data type. Using ver ojdbc10 - 19.8.00 throws Exceptions.

Using

<dependency>
       <groupId>com.oracle.database.jdbc</groupId>
       <artifactId>ojdbc10</artifactId>
       <version>19.8.0.0</version>
</dependency>

Json param is:

{"email":"[email protected]","externalIds":[],"firstName":"John","registeredDateUtc":"2020-12-09T21:57:21Z","role":"USER","surname":"Doe"}

Code:

String json =  ... json above...;
try (var con = dataSource.getConnection()) {
            var db = oracleClient.getDatabase(con);
            var messageDoc = db.createDocumentFromString(json);
            var collection = db.openCollection(collectionName);
            collection.insert(messageDoc);
        } catch (Exception e) {
            logger.error("An exception occurred adding entity " + entity, e);
            throw new RuntimeException(e);
        }

Exceptions thrown:

1. When ID generation for collection is set to UUID:

java.lang.RuntimeException: oracle.soda.OracleException: Error occurred during conversion of the input document's JSON content to binary. Ensure the content of the input document is valid JSON.

2. When ID generation for collection is set to CLIENT:
   Also throws an Exception.

[morgiyan]

The default content column storage for SODA collection in 21c is the new JSON data type (as long as the "compatible" database parameter is set to 20 or above, which is the case on Autonomous). This is the new datatype specifically designed for storing JSON, and it's backed by a binary format representation of JSON, called OSON. The previous default was BLOB.

The 21c JDBC jar that supports JSON type is not yet released. I will post an update here once it's out. So in order for SODA to work with JSON type, you'd need SODA version 1.1.7 and that new 21c JDBC jar.

In the meantime, the workaround is to explicitly request the old default (BLOB) in collection metadata. That'll allow you to use 21c.

Here's the metadata that would the default collection:

{"contentColumn" : { "sqlType" : "BLOB"},
"versionColumn" : {"name" : "VERSION", "method" : "UUID"},
"lastModifiedColumn" : {"name" : "LAST_MODIFIED"},
"creationTimeColumn" : {"name" : "CREATED_ON"}}

Example in Java:

       OracleDocument meta = db.createDocumentFromString("{\"contentColumn\" : { \"sqlType\" : \"BLOB\"}, \"versionColumn\" : { \"name\" : \"VERSION\", \"method\" : \"UUID\"}, \"lastModifiedColumn\" : { \"name\" : \"LAST_MODIFIED\" }, \"creationTimeColumn\" : { \"name\" : \"CREATED_ON\"}}");

       OracleCollection col = db.admin().createCollection("myColNameHere", meta);

[morgiyan]

And if you wanted to use CLIENT assigned keys instead, you'd do:

{"keyColumn" : {"assignmentMethod" : "CLIENT"},
"contentColumn" : { "sqlType" : "BLOB"},
"versionColumn" : {"name" : "VERSION", "method" : "UUID"},
"lastModifiedColumn" : {"name" : "LAST_MODIFIED"},
"creationTimeColumn" : {"name" : "CREATED_ON"}}

[osawyerr]

Thanks for the reply. Trying it out but there seems to be another error related to wallets for Oracle Autonomous 21c in UK region. Don't know if its relates to the SODA driver or a wider problem with the Autonomous 21c.

"ORA-28374: typed master key not found in wallet"

[morgiyan]

We will take a look and get back to you. This seems completely unrelated to SODA.

[osawyerr]

Sure thanks. Yeah, this also seems to happen in SQL Developer Web as well which should "just work".

Screenshot below:

[morgiyan]

Thanks. We (SODA team) tried this out against Ashburn but can't reproduce. Must be something specific to the UK region you're trying. We are checking with the appropriate team, will get back to you.

Does anything work? For example, can you create a regular table (not AQ) and use it from SQLDevWeb? Or do you get this error for that as well?

[osawyerr]

Nope nothing seems to work. Even just creating a simple table and doing an insert fails with the same error on SQLDevWeb during the insert. (even as ADMIN).

CREATE TABLE FOO (
    PAYLOAD VARCHAR2(5000) 
);

insert into foo(payload) values ('bar');

gives the error

"ORA-28374: typed master key not found in wallet"

Tested on ATP 21c and ADW 21c in UK free tier and get the same error.

[morgiyan]

Our apologies about that! We reached out to the Autonomous DB team, and they asked for these details:

Region
Tenancy OCID
Database name

Would you be able to provide these?

[osawyerr]

Home region is: uk-london-1

I don't want to put the other details on GitHub if it can be avoided. Have they tried to reproduce? i.e. have they created an ATP 21c or ADW 21c with the UK as home region, created a table and tried an insert? Its happened with every 21c database I've created previously so might be easy for them to reproduce.

Alternatively, if you provide an email address I can send the details you requested details there?

[morgiyan]

We've tried in US, and it's working fine. I'll see if they can reproduce in UK.

Or you can just shoot me an email: maxim dot orgiyan at oracle dot com
Please replace "dot" with "." and replace "at" with "@". And remove spaces.

[osawyerr]

Sure. I've sent the details via email.

[CoolSoybean]

I am having similar issue when I run migrate for my Django project. Been googling for many days😭😭

[morgiyan]

@CoolSoybean our apologies for that! Somehow I didn't get a notification on your message so just seeing it now. Have you solved the issue? If not, I can help you with that. I'll need some details.

(1) Which service are you trying to connect to? Is it ATP (Autonomous Transaction Processing) 21c or something else? (e.g. on prem, some other cloud, etc). Please provide the database version as well (21c, 19c, etc).

(2) Which SODA driver are you using? Is it SODA Java? I ask because you mentioned Django, which makes me wonder if you're using SODA Java or the SODA support in cx_oracle (the Oracle python driver).

If SODA Java, which version are you using? And which version of JDBC?

If SODA cx_oracle, which version of cx_oracle are you using? And which version of instant client?

Please provide these details and the error you're getting and I can help you resolve the issue.