OracleRDBMSMetadataBuilder support for keyColumn path field - to support Mongo collection creation

[mshannongit]

Mongo compatible collection creation on 19c using ords PUT against /ords/admin/soda/latest/< collectionname > may look similar to ...

{"contentColumn":{"name":"DATA"},"keyColumn":{"name":"ID","sqlType":"VARCHAR2","maxLength":255,"assignmentMethod":"EMBEDDED_OID","path":"_id"},"versionColumn":{"name":"VERSION","method":"UUID"},"lastModifiedColumn":{"name":"LAST_MODIFIED"},"creationTimeColumn":{"name":"CREATED_ON"}}

OracleRDBMSMetadataBuilder doesn't appear to support that path parameter override.

[mshannongit]

Additionally , keyColumnAssignmentMethod("EMBEDDED_OID") needs support added.

final OracleDocument metadata = cl.createMetadataBuilder().contentColumnName("DATA").keyColumnName("ID").keyColumnType("VARCHAR2").keyColumnMaxLength(255).keyColumnAssignmentMethod("EMBEDDED_OID").versionColumnName("VERSION").versionColumnMethod("UUID").lastModifiedColumnName("LAST_MODIFIED").creationTimeColumnName("CREATED_ON").schemaName(targetUser).tableName("COMPANY").build();

Currently the following exception is seen ...

OracleException occurred : Invalid argument value "EMBEDDED_OID".

[mshannongit]

createCollection() - OracleException occurred : Invalid argument value "EMBEDDED_OID".
oracle.soda.OracleException: Invalid argument value "EMBEDDED_OID".
at oracle.soda.rdbms.impl.SODAUtils.makeException(SODAUtils.java:53)
at oracle.soda.rdbms.impl.CollectionDescriptor$Builder.keyColumnAssignmentMethod(CollectionDescriptor.java:1553)
at oracle.soda.rdbms.impl.CollectionDescriptor.jsonToBuilder(CollectionDescriptor.java:918)
at oracle.soda.rdbms.impl.CollectionDescriptor.jsonToBuilder(CollectionDescriptor.java:1070)
at oracle.soda.rdbms.impl.OracleDatabaseImpl.callCreatePLSQL(OracleDatabaseImpl.java:1669)
at oracle.soda.rdbms.impl.OracleDatabaseImpl.createCollection(OracleDatabaseImpl.java:703)
at oracle.soda.rdbms.impl.OracleDatabaseImpl.createCollection(OracleDatabaseImpl.java:638)
at oracle.soda.rdbms.impl.OracleDatabaseImpl.createCollection(OracleDatabaseImpl.java:933)
at oracle.soda.rdbms.impl.OracleDatabaseImpl.access$200(OracleDatabaseImpl.java:70)
at oracle.soda.rdbms.impl.OracleDatabaseImpl$OracleDatabaseAdministrationImpl.createCollection(OracleDatabaseImpl.java:2304)

[mshannongit]

Looks like even PL/SQL doesn't work .. and the only hope is just using ORDS http request directly ...

createCollection() - SQLException occurred : ORA-40842: unsupported value EMBEDDED_OID in the metadata for the field assignmentMethod
ORA-06512: at "SYS.DBMS_SODA", line 4
ORA-06512: at line 4

java.sql.SQLException: ORA-40842: unsupported value EMBEDDED_OID in the metadata for the field assignmentMethod
ORA-06512: at "SYS.DBMS_SODA", line 4
ORA-06512: at line 4

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:628)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:562)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1207)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:727)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:291)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:498)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:151)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1192)
at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1747)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1432)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3743)
at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:4012)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3987)
at oracle.jdbc.driver.OracleCallableStatement.executeUpdate(OracleCallableStatement.java:4302)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:996)
at XXX.createCollection(BackupSchemaTool.java:225)

Caused by: Error : 40842, Position : 0, Sql = DECLARE
coll SODA_COLLECTION_T;
BEGIN
coll := DBMS_SODA.CREATE_COLLECTION(:1 , :2 );
END;, OriginalSql = DECLARE
coll SODA_COLLECTION_T;
BEGIN
coll := DBMS_SODA.CREATE_COLLECTION(?, ?);
END;, Error Msg = ORA-40842: unsupported value EMBEDDED_OID in the metadata for the field assignmentMethod
ORA-06512: at "SYS.DBMS_SODA", line 4
ORA-06512: at line 4

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:632)
... 16 more





        // create a mongo compatible collection

        String collectionName = "COMPANY";

        String metadataString = "{\"contentColumn\":{\"name\":\"DATA\"},\"keyColumn\":{\"name\":\"ID\",\"sqlType\":\"VARCHAR2\",\"maxLength\":255,\"assignmentMethod\":\"EMBEDDED_OID\",\"path\":\"_id\"},\"versionColumn\":{\"name\":\"VERSION\",\"method\":\"UUID\"},\"lastModifiedColumn\":{\"name\":\"LAST_MODIFIED\"},\"creationTimeColumn\":{\"name\":\"CREATED_ON\"}}";


        // workaround by calling plsql soda API directly to create_collection
        // triple quotes (introduced in JDK13) provides multi line text block
        final CallableStatement cstmt = conn.prepareCall("""
                DECLARE
                   coll SODA_COLLECTION_T;
                BEGIN
                   coll := DBMS_SODA.CREATE_COLLECTION(?, ?);
                END;""");
        cstmt.setString(1, collectionName);
        cstmt.setString(2, metadataString);
        cstmt.executeUpdate();

[morgiyan]

Right, currently we only support EMBEDDED_OID thru Mongo API (as documented here).

But we're working on adding it in the SODA drivers. For SODA Java, it's currently planned for the next release. May I ask if there's some urgency on this for you, so that we prioritize accordingly? Please feel free to email us directly with details if that's easier. My email is: maxim dot orgiyan at oracle dot com (remove the spaces, change "at" to "@", and change "dot" to ".").

Thank you!

[mshannongit]

Using a SODA REST client, I can create the mongo compatible collection.

That collection though cannot be read using

        OracleRDBMSClient cl = new OracleRDBMSClient();
        OracleDatabase db = cl.getDatabase(conn);
        OracleCollection col = db.openCollection(collectionName);

It will give the error ...

oracle.soda.OracleException: Invalid argument value "EMBEDDED_OID".
at oracle.soda.rdbms.impl.SODAUtils.makeException(SODAUtils.java:53)
at oracle.soda.rdbms.impl.CollectionDescriptor$Builder.keyColumnAssignmentMethod(CollectionDescriptor.java:1553)
at oracle.soda.rdbms.impl.CollectionDescriptor.jsonToBuilder(CollectionDescriptor.java:918)
at oracle.soda.rdbms.impl.CollectionDescriptor.jsonToBuilder(CollectionDescriptor.java:1070)
at oracle.soda.rdbms.impl.OracleDatabaseImpl.loadCollection(OracleDatabaseImpl.java:1790)
at oracle.soda.rdbms.impl.OracleDatabaseImpl.openCollection(OracleDatabaseImpl.java:771)
at oracle.soda.rdbms.impl.OracleDatabaseImpl.openCollection(OracleDatabaseImpl.java:730)