MacLochlainns Weblog

Oracle Database 23c has some really great features. One of those features is the ability to write JavaScript functions inside the database. Unfortunately, I noticed a couple omissions in Oracle’s JavaScript Developer’s Guide. Specifically, I encountered a privilege error while testing the example in the 5.1 Call Specifications for Functions example.

After having typed it all in to a simple script file, I encountered the following error message when trying to create an MLE MODULE:

CREATE OR REPLACE MLE MODULE jsmodule
*
ERROR AT line 1:
ORA-01031: insufficient PRIVILEGES
Help: https://docs.oracle.com/error-help/db/ora-06575

That was easy enough to fix. As the system user you need to grant the following two additional privileges to the user (based on my earlier sandbox pluggable user configuration user setup), which in my case is the c##student pluggable user:

GRANT CREATE MLE TO c##student;
GRANT EXECUTE ON JAVASCRIPT TO c##student;

You need to enable the SQL*PLus SERVEROUTPUT environment parameter for Oracle’s code example to work when you run the greet procedure from SQL*Plus command-line interface (CLI).

Below is the modified example file (unfortunately, the GeSHi formatting promotes log and return in the JavaScript functions to uppercase because they’re assumed as keywords in Oracle SQL):

1. Conditionally drop the MLE MODULE.

   DROP MLE MODULE IF EXISTS jsmodule;

   DROP MLE MODULE IF EXISTS jsmodule;

2. Create a MLE Call Specification.

   1 2 3 4 5 6 7 8 9 10 11 12

   CREATE OR REPLACE MLE MODULE jsmodule LANGUAGE JAVASCRIPT AS   export function greet(str) { console.log(`Hello ${str}!`) }   export function concat(str1, str2) { return str1 + " " + str2 + "!"; } /

   CREATE OR REPLACE MLE MODULE jsmodule LANGUAGE JAVASCRIPT AS export function greet(str) { console.log(`Hello ${str}!`) } export function concat(str1, str2) { return str1 + " " + str2 + "!"; } /

3. The greet function doesn’t return a value and uses the Nodejs console.log() function to write a string, which means you must wrap the JavaScript function as a procedure because it returns a void type.

   1 2 3 4 5

   CREATE OR REPLACE PROCEDURE greet(str in VARCHAR2) AS MLE MODULE jsmodule SIGNATURE 'greet(string)'; /

   CREATE OR REPLACE PROCEDURE greet(str in VARCHAR2) AS MLE MODULE jsmodule SIGNATURE 'greet(string)'; /

4. Wrap the concatenate function as a function because it returns a value inside the JavaScript.

   1 2 3 4 5 6 7

   CREATE OR REPLACE FUNCTION concatenate ( str1 VARCHAR2 , str2 VARCHAR2 ) RETURN VARCHAR2 AS MLE MODULE jsmodule SIGNATURE 'concat(string, string)'; /

   CREATE OR REPLACE FUNCTION concatenate ( str1 VARCHAR2 , str2 VARCHAR2 ) RETURN VARCHAR2 AS MLE MODULE jsmodule SIGNATURE 'concat(string, string)'; /

5. Enable to the SERVEROUTPUT environment variable to display messages printed from inside stored procedures or other PL/SQL blocks.

   SET SERVEROUTPUT ON SIZE UNLIMITED

   SET SERVEROUTPUT ON SIZE UNLIMITED

6. Call the greet(literal|variable) function’s procedure wrapper.

   CALL greet('Peter');

   CALL greet('Peter');

   It returns

   Hello Peter!
   

7. Query the result from the concatenate() function’s function wrapper. */

   SELECT concatenate('Hello','World');

   SELECT concatenate('Hello','World');

   It returns

   Hello World!
   

As always, I hope this helps somebody working through the same issue.