Insert Document into Oracle SODA Collection Created with CREATE_MODE_MAP

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;

Solution

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.