Oracle: MLE Modules

Creating an MLE module

Create a MLE module with a JavaScript function (js_proc) and export the function:

create or replace mle module tq84_mle_module
  language javascript
as

  function js_proc() {
     console.log('js_proc was called');
  }

  export {
     js_proc
  }

/

Add a PL/SQL interface to the exported JavaScript function:

create or replace procedure plsql_proc
   authid definer
as
   mle module tq84_mle_module
   signature 'js_proc()';
/

Call the PL/SQL procedure. When executed in SQL*Plus or Oracle SQL Developer, the serveroutput SQL*Plus option must be on to see the output (as apparently, under the hood, console.log is routed to dbms_output).

set serveroutput on

begin
   plsql_proc;
end;
/

If the attempt to execute plsql_proc throws the error message ORA-01031: insufficient privileges, the executing user might lack the execute privelege on javascript. This privilege can be by someone with the necessary rights like so:

grant execute on javascript to rene;

Querying the data dictionary for information peratinging the newly created MLE module…

select *
from
   user_mle_modules
where
   module_name = 'TQ84_MLE_MODULE';

… and the JavaScript source code …

select *
from
   user_source
where
   type = 'MLE MODULE' and
   name = 'TQ84_MLE_MODULE';

… and the information about the PL/SQL interface:

select *
from
   user_mle_procedures
where
   object_name = 'PLSQL_PROC';

Executing SQL with mle-js-oracledb

It's possible to execute SQL statements from JavaScript by importhing the mle-js-oracledb module:

create or replace mle module tq84_sql_example language javascript as

import oracledb from "mle-js-oracledb";

function print_objs_of_type_js(obj_type) {

   if (obj_type === undefined) {
      throw "obj_type is undefined";
   }

   try {
     let connection = oracledb.defaultConnection();

     const result = connection.execute(`
        select
           object_name,
           created
        from
           user_objects
        where
           lower(object_type) = lower(:t)`,
        [ obj_type ],
        { outFormat: oracledb.OUT_FORMAT_OBJECT }
     );

     for (let row of result.rows) {
         console.log(`${row.OBJECT_NAME} (${row.CREATED})`);
     }

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

export { print_objs_of_type_js };
/

Note, the previous code block might show the Enter Binds dialog when executed with ctrl+enter in Oracle SQL Developer because the bind variable :t does not occur in a PL/SQL string and SQL developer wants to privide a value for the variable.

Therefore, in this case, the code should be executed with F5.

The PL/SQL wrapper for the JavaSCript code:

create or replace procedure print_objs_of_type(obj_type varchar2)
   authid definer
as
   mle module tq84_sql_example
   signature 'print_objs_of_type_js(string)';
/

Calling the wrapper:

begin
    print_objs_of_type('procedure');
end;
/

Required privileges

In order to execute plsql_proc, the following privilege must be granted, otherwise, Oracle throws ORA-01031: insufficient privileges:

grant execute on javavascript to … ;

Oracle: MLE Modules

Creating an MLE module

Executing SQL with mle-js-oracledb

Required privileges

See also