Oracle SODA : creating tables manually

Question

I want to use Oracle SODA, but the user that connects to the DB from the java application cannot modify tables, for security reasons.

I did grant the "SODA_APP" to the schema, so the SODA java lib works (it calls the SODA pl/sql functions), but obviously fails to create collection with "insufficient privileges"

I can create the collection in a test DB, and copy the table structure. But I must also copy the metadata.

I know this because the doc says :

Do not use SQL to drop the database table that underlies a collection. In addition to the documents that are stored in its table, a collection has metadata, which is also persisted in Oracle Database. Dropping the collection table does not also drop the associated metadata.

ref: https://docs.oracle.com/cd/E56351_01/doc.30/e58124/soda_for_java.htm#ADSDA111

Does anyone know where the metadata of the collection is located / how I can create it (or copy) manually?

Thanks.

Answer 1

So, the metadata is located in this table:

XDB.JSON$COLLECTION_METADATA

There is a view for the current user

SELECT * FROM XDB.JSON$USER_COLLECTION_METADATA

Which looks like the same view as the documented "user_soda_collections" view.

Answer 2

Yes, correct. USER_SODA_COLLECTIONS dictionary view reports collections created by the current user.

https://docs.oracle.com/en/database/oracle/simple-oracle-document-access/adsdi/overview-soda.html#GUID-AFA0FE26-415B-4BC0-8545-06C38855D54C

You could 'MAP' a collection to an existing one, if you want to avoid copying.

If you want to create a collection on a pre-existing one, you should be able to, using 'MAP' mode of create collection. SODA creates a collection, mapping it to a table in an existing schema. For this, you need to specify "schemaName" and "tableName" in the metadata while you create a collection.

{
  "schemaName": "<SCHEMA_NAME_OF_EXISTING_TABLE>",
  "tableName": "<EXISTING_TABLE_NAME>",
    ...your other fields in metadata...
}

Also, if you want this collection to be immutable, you can use "readOnly" field in the metadata (set it to TRUE).

Hope this helps