Creating MLE JavaScript Modules in Database Actions

Oracle recently released ORDS (Oracle RESTFul Data Services) 23.2.0, available for download at the usual place. This is great news for anyone wishing to develop JavaScript modules in Oracle Database 23c Free-Developer Release. Database Actions provides a great developer experience, including syntax highlighting, an object browser for modules, environments, and call specifications and many more features. There is also built-in support for post-execution debugging. You can read more about working with Multilingual Engine (MLE) JavaScript modules in this post, the next one in the queue is reserved for post-execution debugging.

First contact with Database Actions 23.2

This post assumes you installed ORDS 23.2 in FREEPDB1, the initial Pluggable Database available with Oracle Database 23c Free-Developer Release. In my case I simply ords-enabled my own schema, "MARTIN". If you would like to follow along you need to grant the following privileges to the user:

• grant EXECUTE on javascript to user
• grant EXECUTE DYNAMIC MLE to user
• grant DB_DEVELOPER_ROLE to user

Additionally you might need space quotas on your assigned tablespaces. After your schema is enabled (see ORDS.enable_schema() in the documentation set for details) you can point your browser to Database Actions using the following URL:

https://<ords-host>:<ords-port>/ords/<your-schema-name-or-alias>/_sdw/

You will be prompted for your database username and password. Once authenticated you are presented with the launchpad:

You can see the MLE JS tile in the development section. Clicking on it takes you to the JavaScript editor.

Create a new MLE JavaScript module

Using the editor you can start creating your first JavaScript module. The example shown below shows the editor’s syntax highlighting capabilities.

A quick mouse-click on the diskette icon stores the module in the database. The navigator panel on the left shows the new module:

Making str2obj() available to PL/SQL and SQL

Before a JavaScript function exported from a module can be used in SQL and PL/SQL a call specification must be created first. Database Actions can help with that task as well. Right-click on the module name, then select “Create” followed by “Call Specification”:

In the ensuing dialog click on the exported function’s name, switch the toggle to “function” and set the output type to JSON. Database Actions identifies the parameter and makes a suggestion of its data type.

If you aren’t happy with the suggested data type select one that’s more appropriate from the “Type” drop down. Click on “Create” to persist the call specification in the database.

Executing the code

After switching to the SQL Worksheet it is straight-forward to invoke the function you just created in the previous step. The JavaScript call specification attached to the PL/SQL function allows you to call JavaScript code wherever SQL and PL/SQL can be used. The example shown in the print screen below demonstrates this fact. The function you just created is invoked, but before its output can be shown it's passed to json_serialize for pretty printing the resulting JSON. You can see the formatted result by clicking on the pencil in the results pane, it doesn't show in the output below.

Summary

If you prefer a graphical user interface for writing your code online, then Database Actions might just be the thing for you. The functionality has been available with 23.1.n as well but the new release enhances the experience substantially. Feel free to give the combination of ORDS 23.2.0 and Oracle Database 23c Free-Developer Release a try. Any feedback is good feedback, if you like you can share yours in the dedicated forum. Happy coding!