Getting Started with SODA for In-Database JavaScript

How to access SODA for In-Database JavaScript is described, as well as how to use it to create a database collection, insert a document into a collection, and retrieve a document from a collection.

Before you can get started working with SODA for MLE JavaScript, the account used for storing collections (in this case, emily) must be granted the SODA_APP roles, either directly or using the DB_DEVELOPER_ROLE:

grant soda_app to emily

Accessing SODA functionality requires the use of the MLE JavaScript SQL driver. Because the database session exists by the time the code is invoked, no additional connection handling is necessary. Example 8-1 demonstrates how to:

• Create a SODA collection,

• Insert a JSON document into it, and

• Iterate over all SODA Documents in the collection, printing their contents on screen

Each concept presented by Example 8-1 - creating collections, adding and modifying documents, and dropping collections - is addressed in more detail later in this chapter.

Example 8-1 SODA with MLE JavaScript General Workflow

This example demonstrates the general workflow using SODA collections with MLE JavaScript. Instead of using an MLE module, the example simplifies the process by implementing an inline call specification.

CREATE OR REPLACE PROCEDURE intro_soda(
    "dropCollection" BOOLEAN
) AUTHID CURRENT_USER
AS MLE LANGUAGE JAVASCRIPT
{{
    
  // use the soda object, available in the global scope instead of importing
  // the mle-js-oracledb driver, getting the default connection and extracting
  // the SodaDatabase from it
  const col = soda.createCollection("MyCollection");

  // create a JSON document (based on the HR.EMPLOYEES table for the employee with id 100)
  const doc = {
    "_id" : 100,
    "job_id" : "AD_PRES",
    "last_name" : "King",
    "first_name" : "Steven",
    "email" : "SKING",
    "manager_id" : null,
    "department_id" : 90
  };

  // insert the document into collection
  col.insertOne(doc);

  // find all documents in the collection and print them on screen
  // use a cursor to iterate over all documents in the collection
  const c = col.find()
            .getCursor();

  let resultDoc;
  
  while (resultDoc = c.getNext()){
    const content = resultDoc.getContent();
    console.log(`
      ------------------------------------------
      key:           ${resultDoc.key}
      content (select fields):
      - _id:         ${content._id}
      - job_id:      ${content.job_id}
      - name:        ${content.first_name} ${content.last_name}
      version:       ${resultDoc.version}
      media type:    ${resultDoc.mediaType}`
    );
  }

  // it is very important to close the SODADocumentCursor to free resources
  c.close();

  // optionally drop the collection
  if (dropCollection){
    // there is no auto-commit, the outstanding transaction must be
    // finished before the collection can be dropped
    session.commit();
    col.drop();
  }
}};
/

You can try the code by executing the procedure using your favorite IDE. Here is an example of the results of calling the intro_soda procedure:

BEGIN
  intro_soda(true);
END;
/

Result:

------------------------------------------
key:           03C202
content (select fields):
- _id:         100
- job_id:      AD_PRES
- name:        Steven King
version:       17EF0F3C102653DDE063DA464664399C
media type:    application/json

PL/SQL procedure successfully completed.