Creating Call Specifications Involving the SODA API

Earlier in this chapter, in the section Getting Started with SODA for In-Database JavaScript, an example showing how to invoke the MLE SODA API using an inline call specification is included. The following short example demonstrates how to use SODA in MLE modules.

Example 8-22 Use SODA for In-Database JavaScript

See Example 8-8 for details about how to create employeesCollection, used in this example.

CREATE OR REPLACE MLE MODULE end_to_end_demo
LANGUAGE JAVASCRIPT AS

/**
 * Example for a private function used to open and return a SodaCollection
 *
 * @param {string} collectionName the name of the collection to open
 * @returns {SodaCollection} the collection handle
 * @throws Error if the collection cannot be opened
 */
function openAndCheckCollection(collectionName){
  
  const col = soda.openCollection(collectionName);
  if(col === null){
    throw new Error(`invalid collection name: ${collectionName}`);
  }

  return col;
}

/**
 * Top-level (public) function demonstrating how to use a QBE to
 * filter documents in a collection.
 *
 * @param {number} departmentId the numeric department ID
 * @returns {number} the number of employees found in departmentId
 */
export function simpleSodaDemo(departmentId){
  
  if(departmentId === undefined || isNaN(departmentId)){
    throw new Error('please provide a valid numeric department ID');
  }

  const col = openAndCheckCollection('employeesCollection');

  const numDocs = col.find()
                     .filter({"departmentId": departmentId})
                     .count();

  return numDocs;
}
/

After the module has been created you need to create the call specification. The module features a single public function, so a standalone function should suffice:

CREATE OR REPLACE FUNCTION simple_soda_demo(
  "departmentId" NUMBER
) RETURN NUMBER
AUTHID current_user
AS MLE MODULE end_to_end_demo
SIGNATURE 'simpleSodaDemo';
/

Now everything is in place to call the function:

select simple_soda_demo(30);

Result:

SIMPLE_SODA_DEMO(30)
--------------------
                   6