Mostly Making oracle easy, mostly:
Search for:
In ORDS

Oracle 23c Free Developer Release: MLE JS Modules & Snippets

6 Mins Read

Oracle Database 23c Free – Developer Release is the first release of the next-generation Oracle Database, allowing developers a head-start on building applications with innovative 23c features that simplify development of modern data-driven apps. The entire feature set of Oracle Database 23c is planned to be generally available within the next 12 months.

Today’s post is all about the MultiLingual Engine, powered by GraalVM, in the Oracle Database, and it’s ability to execute JavaScript.

This was first delivered as a feature in 21c, but only for running snippets of JavaScript. In 23c, we now allow you create reusable blocks of code, called Modules. To learn more about this feature, I highly recommend you read the 23c JavaScript Developer Guide (Docs.)

Creating a Database User, MLEJS

My user needs to be able to like, do stuff. Some of that stuff would include, logging into SQL Developer Web, and of course running JavaScript.

Here’s my user setup –

CREATE USER mlejs identified BY oracle;
grant CREATE session TO mlejs;
grant RESOURCE, db_developer_role TO mlejs;
grant unlimited tablespace TO mlejs;
 
grant EXECUTE ON javascript TO mlejs;
grant EXECUTE dynamic mle TO mlejs;
 
 
-- ENABLE REST
BEGIN
    ORDS_ADMIN.ENABLE_SCHEMA(
                   p_enabled => TRUE,
                    p_schema => 'mlejs',
          p_url_mapping_type => 'BASE_PATH',
       p_url_mapping_pattern => 'mlejs',
            p_auto_rest_auth => FALSE
    );
    COMMIT;
END;
/

Did you catch the new 23c role, DB_DEVELOPER_ROLE? It’s documented in the 23c Security Guide.

There’s a small bug in the Free Developer Release, those last two exec grants should already be available via the DB_DEVLEOPER_ROLE. You can fix this by running this script –

@?/rdbms/admin/catdevrol.sql

Database Tools Support for MLE JS

JavaScript ‘Snippets’

If you don’t have a formal program to be stored in the database to be reused later, but you do have some JavaScript goodies you want to run more ad hoc, then snippets are going to be your friend.

In SQL Developer Web, we have a MLE JS editor you can use to work with Modules OR Snippets. Let’s look at a snippet first –

(async () => {
    await import('mle-js-fetch');
    const fetchUrl = "http://localhost:8080/ords/mlejs/greetings/hello.world/";
    const answer = await fetch(fetchUrl).then(response => response.json());
    console.log(JSON.stringify(answer, undefined, 4));
})();

Thanks to Martin for this block of code. We’ll be talking about Martin again later in this post 🙂

That library we’re importing, ‘mle-js-fetch’ – is a miracle worker. It allows the database to EASILY call out to a network resource via HTTP/HTTPS and GET, PUT, POST, PUT…stuff. In this case I’m doing a simple GET.

Bookmark this mle-js-fetch resource, our partial implementation of the Fetch API in the Oracle Database.

I’m taking the response, and I’m formatting it, then printing it, with everyone’s friend, console.log.

Copy and paste that code into the Snippet editor, and hit ‘Execute.’ Well…first you’ll need a valid REST API, and you’ll need an ACL defined to allow your user to access said REST API.

We’re currently updating our parser, version 23.2 won’t print those syntax issue indicators.

I’m hitting a REST API, it just so happens to be on the same machine as my database…

So the database itself is calling out to the HTTP site, and it’s doing that by leveraging the JavaScript Fetch API. That’s only like 4 or 5 lines of code?

In the background, we’re constructing a PL/SQL block of code to invoke the DBMS_MLE package for you. The snippet editor allows you to forget about SQL and PL/SQL for awhile, and just stay in the JavaScript Universe.

If I were JavaScript proficient, I’d write some more JS to pull out a particular attribute from that JSON response, but I’m OK to leave it here, and let you folks use YOUR JavaScript skills to do what needs to be done.

Oops, I made a boo-boo, what went wrong?

Perhaps I gave you bad code or you fat-fingered something. How do you know what went wrong? Well in the error stack, you can see the actual database call we’re making.

I tried to import mle-js-fech, oops! But I can see the DBMS_MLE.CREATE_CONTEXT() call.

There may be another reason this doesn’t work for you, network security in the database is preventing you from doing the actual fetch.

Don’t forget your ACLs

The database user needs permission to make a network request. This permission is granted via the DBMS_NETWORK_ACL_ADMIN PL/SQL API.

BEGIN
 DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE
 (
            host => 'localhost',
      lower_port => 8080,
      upper_port => 8888,
             ace => xs$ace_type(privilege_list => xs$name_list('http'),
  principal_name => 'mlejs',
  principal_type => xs_acl.ptype_db)
 );
END;
/

My database user, MLEJS, gets to use HTTP, but only for localhost, and only on ports 8080 – to 8888.

If you’re going to hit a HTTPS site, you may need to create a wallet on the database server and add the necessary Trusted Certificate, but that’s a different post.

JavaScript Modules

This next bit is a direct lift from Martin’s post here on Oracle Blogs. We’re going to import the open source validateor.js library and emloy the ‘isEmail()’ function.

And we’re going to do that with SQL Developer Web.

Step 1: Download the JS source. Be sure to grab the ECMAScript version. I grabbed it from the same place as Martin, cdn.jsdelivr.net.

Save that to a local file.

Step 2: Login as MLEJS and go to the MLE JS screen.

Step 3. Create a new module.

In the editor panel, not the snippets, click the ‘Open File’ button and point to the one you created or downloaded in Step 1. Once the editor is populated, put in the name of the module you want to save it as, and hit the ‘save’ button.

Once it’s compiled, you can refresh the list to the left, to see the new module appear.

Now that the module is in the database, it can be called from say, a PL/SQL function.

Calling the JS Module via SQL invoking a PL/SQL Function

First we’ll create a function.

CREATE OR REPLACE FUNCTION MLEJS.isEmail(
  p_str VARCHAR2
) RETURN BOOLEAN
AS mle module validator
signature 'default.isEmail(string)';

If we plop that code into the SQL Developer Web PL/SQL editor, we can compile it, and voila.

Compiled, no errors.

But wait, we can help with that too – Call Specifications

We have a GUI you can use to create these PL/SQL objects that are used to basically invoke the MLE modules.

This is a work in progress, the parameter declaration will get better.

Anyways, once the PL/SQL program is available, I can use this function directly in a simple SELECT statement.

The SQL

SELECT isEmail('abc') VALID_EMAIL ;
 
SELECT isEmail('[email protected]') VALID_EMAIL;

It’s very convenient for this example that BOOLEAN is a native type in SQL now, and also we don’t have to use a ‘FROM DUAL’ to invoke the function.

We’re not using a 23c JDBC driver (yet), TRUE comes base as 1 and FALSE as 0.

Wait, is that it?

Oh heck no, there’s a lot more to show off.

Managing Environments and Call Specifications, creating Debug Specifications, debugging snippets, generating dependency diagrams, and much much more.

I apologize if this was too much to grok in a single post, but don’t worry we’ll be covering these topics much more in the near future!

Follow these MLE JS Experts

I couldn’t share what I’m sharing without their help. And more importantly, they’re attending our weekly developer calls to help make sure these interfaces fit the needs of our JavaScript developers in the Oracle Database! Thanks Martin and Lucas!

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

Write A Comment