Example 7-1 Getting Started with the MLE JavaScript SQL Driver

The following code demonstrates how to import the MLE JavaScript SQL driver into the current module's namespace. This example is based on one provided in the node-oracledb documentation, A SQL SELECT statement in Node.js.

CREATE OR REPLACE MLE MODULE js_sql_mod LANGUAGE JAVASCRIPT AS

import oracledb from "mle-js-oracledb";

/**
 * Perform a lookup operation on the HR.DEPARTMENTS table to find all
 * departments managed by a given manager ID and print the result on
 * the console
 * @param {number} managerID the manager ID
*/

function queryExample(managerID) {

  if (managerID === undefined) {
    throw new Error (
        "Parameter managerID has not been provided to queryExample()"
    );
  }
  let connection;

  try {
    connection = oracledb.defaultConnection();

    const result = connection.execute(`
        SELECT manager_id, department_id, department_name
        FROM hr.departments
        WHERE manager_id = :id`,
        [
            managerID
        ], 
        {
            outFormat: oracledb.OUT_FORMAT_OBJECT
        }
    );
    if (result.rows.length > 0) {
        for (let row of result.rows) {
            console.log(`The query found a row:
                manager_id:      ${row.MANAGER_ID}
                department_id:   ${row.DEPARTMENT_ID}
                department_name: ${row.DEPARTMENT_NAME}`);
        }
    } else {
        console.log(`no data found for manager ID ${managerID}`);
    }

  } catch (err) {
    console.error(`an error occurred while processing the query: ${err.message}`);
  } 
}

export { queryExample };
/

The only function present in the module, queryExample(), selects a single row from the HR departments table using a bind variable by calling connection.execute(). The value of the bind variable is passed as a parameter to the function. Another parameter passed to connection.execute() indicates that each row returned by the query should be provided as a JavaScript object.

If data has been found for a given managerID, it is printed on the screen. By default, the call to console.log() is redirected to DBMS_OUTPUT. Should there be no rows returned a message indicating this fact is printed on the console.

The call specification in the following snippet allows the code to be invoked in the database.

CREATE OR REPLACE PROCEDURE p_js_sql_query_ex(
    p_manager_id number)
AS MLE MODULE js_sql_mod
SIGNATURE 'queryExample(number)';
/

Provided the defaults are still in place, invoking p_js_sql_query_ex displays the following:

SQL> set serveroutput on
SQL> EXEC p_js_sql_query_ex(103)
The query found a row:
manager_id:      103
department_id:   60
department_name: IT

Example 7-2 Use Global Variables to Simplify SQL Execution

Example 7-1 can be greatly simplified for use with MLE. Variables injected into the global scope can be referenced, eliminating the need to import the mle-js-oracledb module. Additionally, because only a single function is defined in the module, an inline call specification saves even more typing.

CREATE OR REPLACE PROCEDURE js_sql_mod_simplified(
    "managerID" number
) AS MLE LANGUAGE JAVASCRIPT
{{
if (managerID === undefined || managerID === null){
    throw new Error (
        "Parameter managerID has not been provided to js_sql_mod_simplified()"
    );
}

const result = session.execute(`
    SELECT
        manager_id,
        department_id,
        department_name
    FROM
        hr.departments
    WHERE
        manager_id = :id`,
    [ managerID ]
);

if(result.rows.length > 0){
    for(let row of result.rows){
        console.log(
            `The query found a row:
             manager_id: ${row.MANAGER_ID}
             department_id: ${row.DEPARTMENT_ID}
             department_name: ${row.DEPARTMENT_NAME}`
        );
    }
} else {
    console.log(`no data found for manager ID ${managerID}`);
}
}};
/

SQL> set serveroutput on
SQL> exec js_sql_mod_simplified(100);

The query found a row:
manager_id:      100
department_id:   90
department_name: Executive