Insert Document into Oracle SODA Collection Created with CREATE_MODE_MAP

Question

We have a node.js app with Oracle DB as backend that uses SODA API. Per our internal DB policy there is a OWNER schema which owns tables while USER schema is used to access objects owned by OWNER schema to perform CRUD operations on them. Can the same model be used with SODA Collections as well. Node.js app uses SODA based oracle driver to perform operations on these collection.

I have been able to create the actual collection in OWNER schema and then create a mapping collection in USER schema. But insert document operations to the mapped collection seems to fail. Probably I am missing a grant or something here.

From OWNER Schema do the following:

DECLARE
  METADATA varchar2(4000);
  l_collection SODA_COLLECTION_T;
BEGIN
  METADATA := '{
  "schemaName":"OWNER",
  "tableName":"TESTCOLLECTION1",
  "keyColumn":{"name":"ID","sqlType":"VARCHAR2","maxLength":255,"assignmentMethod":"UUID"},
  "contentColumn":{"name":"JSON_DOCUMENT","sqlType":"BLOB","compress":"NONE","cache":true,"encrypt":"NONE","validation":"STANDARD"},
  "lastModifiedColumn":{"name":"LAST_MODIFIED"},
  "versionColumn":{"name":"VERSION","method":"UUID"},
  "creationTimeColumn":{"name":"CREATED_ON"},
  "readOnly":false
  }';
  l_collection := DBMS_SODA.create_collection('TESTCOLLECTION1', METADATA);

  IF l_collection IS NOT NULL THEN
    DBMS_OUTPUT.put_line('Collection ID : ' || l_collection.get_name());
  ELSE
    DBMS_OUTPUT.put_line('Collection does not exist.');  
  END IF;
END;
/

Grants grant select, insert, update, delete on OWNER.TESTCOLLECTION1 to USER;

From USER Schema do the following:

declare
METADATA varchar2(4000);
COL SODA_COLLECTION_T;
begin
METADATA := '{
  "schemaName":"OWNER",
  "tableName":"TESTCOLLECTION1",
  "keyColumn":{"name":"ID","sqlType":"VARCHAR2","maxLength":255,"assignmentMethod":"UUID"},
  "contentColumn":{"name":"JSON_DOCUMENT","sqlType":"BLOB","compress":"NONE","cache":true,"encrypt":"NONE","validation":"STANDARD"},
  "lastModifiedColumn":{"name":"LAST_MODIFIED"},
  "versionColumn":{"name":"VERSION","method":"UUID"},
  "creationTimeColumn":{"name":"CREATED_ON"},
  "readOnly":false
}';
COL := dbms_soda.create_collection('TESTCOLLECTION1', METADATA, DBMS_SODA.CREATE_MODE_MAP);

  IF COL IS NOT NULL THEN
    DBMS_OUTPUT.put_line('Collection ID : ' || COL.get_name());
  ELSE
    DBMS_OUTPUT.put_line('Collection does not exist.');  
  END IF;
end;

Insert data into the mapped collection from USER schema

DECLARE
  l_collection  SODA_COLLECTION_T;
  l_document    SODA_DOCUMENT_T;
  l_status      NUMBER;
BEGIN
  l_collection := DBMS_SODA.open_collection('TESTCOLLECTION1');

  l_document := SODA_DOCUMENT_T(
                  b_content => UTL_RAW.cast_to_raw('{"employee_number":7521,"employee_name":"WARD"}')
                );

  l_status := l_collection.insert_one(l_document);

  DBMS_OUTPUT.put_line('status    : ' || l_status);
  COMMIT;
END;

Trying the below code from OWNER SCHEMA works and shows the document however trying the same from USER Schema does not produce the output.

DECLARE
    collection    SODA_COLLECTION_T;
    document      SODA_DOCUMENT_T;
    cur           SODA_CURSOR_T;
    status        BOOLEAN;
BEGIN
    -- Open the collection to be queried
    collection := DBMS_SODA.open_collection('TESTCOLLECTION1');

    -- Open the cursor to fetch the documents.
    cur := collection.find().get_cursor();

    -- Loop through the cursor
    WHILE cur.has_next
    LOOP
      document := cur.next;
      IF document IS NOT NULL THEN
          DBMS_OUTPUT.put_line('Document components:');
          DBMS_OUTPUT.put_line('Key: ' || document.get_key);
          DBMS_OUTPUT.put_line('Content: '
            || json_query(document.get_blob, '$' PRETTY));
          DBMS_OUTPUT.put_line('Creation timestamp: '
            || document.get_created_on);
          DBMS_OUTPUT.put_line('Last modified timestamp: '
            || document.get_last_modified);
          DBMS_OUTPUT.put_line('Version: ' || document.get_version);
      END IF;
    END LOOP;

    -- IMPORTANT: You must close the cursor, to release resources.
    status := cur.close;
END;

Answer 1

I am from the SODA team. Yes, sounds like some grant is missing.

What exact error do you get?

Maybe try to grant read/write priveleges on collection table (which resides in owner schema) to the user schema.

For example (adjust the privileges as appropriate for what you want to allow):

grant select, insert, update, delete on ownerSchemaNameHere.collectionTableNameHere to userSchemaNameHere;

Under the hood, SODA generates regular insert/select/udpate/delete SQL, for all its operations, against the target table backing the collection. So it needs the usual SQL grants to be able to read/write to the target table.

Answer 2

I don't know SODA. However, there are some basic principles that probably apply here:

1. If SODA permits you to prefix the object owner to the name, then it's as simple as connecting as the connect user and operating on the owner objects, fully qualified (update ownerschema.table set .... )
2. If SODA does not permit that or you do not wish to fully qualify object names, you can create synonyms under the connect user schema that point to the object owner schema (create synonym connectschema.table for ownerschema.table).

Either options #1 or #2 will require that the connect schema be granted insert,update,delete (just request ALL) on the owning schema objects.

3. If SODA does it's own dictionary interrogate/describe activities and is not sophisticated enough to handle a synonym, which does happen with some tools, then you can create views under the connect schema that select straight from the object owning schema (create view connectschema.table as select * from owningschema.table). It should be able to interrogate the structure of those views without any idea they are not normal tables, and you can do CRUD operations on them.

With option #3, the connect schema will require insert,update,delete grants "WITH GRANT OPTION" on the owning schema objects.