Managing Oracle database accounts is an essential part of database security and operational hygiene. One key task is identifying accounts that have not been used for extended periods, such as over 30 days. While queries against DBA_USERS or ALL_USERS provide basic metadata about database accounts, they do not reliably indicate actual usage, particularly for REST-enabled schemas. This is because Oracle REST Data Services (ORDS) and other middleware may connect to the database via a “connect through” mechanism, creating sessions on behalf of the schema without a traditional direct login.
Unified Auditing consolidates Oracle’s auditing mechanisms into a single, consistent framework. It allows administrators to define audit policies that track database activity across sessions, users, and even system-level events.
A Unified Audit Policy is a set of rules that specifies which actions are audited. When enabled, it records events in the UNIFIED_AUDIT_TRAIL view, including logins, logouts, DML operations, and privilege usage. This provides a reliable, timestamped record of actual database activity.
Define The Policy
Oracle 26ai ships with a predefined Unified Audit Policy called ORA_LOGIN_LOGOUT. This policy is designed to track user logins and logouts. However, by default it only tracks failures unless your System Administrator has altered the policy definition. So lets verify your audit policy state.
To confirm that Unified Audit is enabled in your database run this query. It should return TRUE.
SELECT VALUE
FROM V$OPTION
WHERE PARAMETER = 'Unified Auditing';
Now check the status of the predefined ORA_LOGIN_LOGOUT policy…
SELECT * FROM audit_unified_enabled_policies where POLICY_NAME = 'ORA_LOGIN_LOGOUT';
The results could look like this indicating that only failed attempts are recorded.
If the predefined ORA_LOGIN_LOGOUT policy is not recording on SUCCESS then define a new policy to achieve that and activate it…
Now that you are recording the LOGON activity you can review the audit trail view. To find database accounts that have not logged in for over 30 days, you can query the UNIFIED_AUDIT_TRAIL using the following SQL:
SELECT
UAT.DBUSERNAME,
MAX(UAT.EVENT_TIMESTAMP_UTC) AS LAST_LOGIN_UTC
FROM
UNIFIED_AUDIT_TRAIL UAT,
ALL_USERS AU
WHERE
UAT.ACTION_NAME = 'LOGON'
AND UAT.DBUSERNAME = AU.USERNAME
GROUP BY
UAT.DBUSERNAME
HAVING
MAX(UAT.EVENT_TIMESTAMP_UTC) < SYSTIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '30' DAY
ORDER BY
LAST_LOGIN_UTC DESC;
Explanation of the query:
DBUSERNAME identifies the database account.
EVENT_TIMESTAMP_UTC captures the precise UTC timestamp of the last login.
MAX(EVENT_TIMESTAMP_UTC) gives the most recent login per user.
The join on ALL_USERS ensures you only see database users that still exist.
HAVING MAX(EVENT_TIMESTAMP_UTC) < SYSTIMESTAMP AT TIME ZONE 'UTC' - INTERVAL '30' DAY filters accounts that have been inactive for over 30 days.
ORDER BY LAST_LOGIN_UTC DESC lists the accounts from least to most recently active.
This approach is far superior to just using DBA_USERS.LAST_LOGIN or ALL_USERS because it captures all session activity, including sessions initiated via REST services like ORDS. Accounts that appear “inactive” in DBA_USERS may, in fact, be used regularly through REST-enabled access.
Why DBA_USERS or ALL_USERS Isn’t Enough
Traditional queries like:
SELECT username, last_login
FROM dba_users;
or
SELECT username
FROM all_users;
have limitations:
No REST context – They only reflect direct database logins, missing sessions created via middleware using “connect through” mechanisms.
Potentially outdated – DBA_USERS.LAST_LOGIN is only updated on direct logins.
Unified Auditing solves all these limitations by taking advantage ofUNIFIED_AUDIT_TRAIL, recording every login event centrally, giving administrators a reliable view of database account activity.
Conclusion
By taking advantage ofUNIFIED_AUDIT_TRAIL, administrators can:
Accurately determine which accounts are truly inactive.
Include REST-enabled schemas in their audits.
Improve security by identifying accounts that may no longer require access.
If it is enabled for SUCCESS then the ORA_LOGIN_LOGOUT Unified Audit Policy is the recommended approach for identifying inactive database accounts in Oracle 26ai. Otherwise, you will have to define a new policy as detailed in this article.
Enabling and managing the audit policies is a critical best practice for modern Oracle databases, particularly those supporting REST services through ORDS. It is important to also have procedures in place to archive old audit trail records so they do not fill disk space.
What audit policies for ORDS do you have in place for monitoring activity?
In a previous post, we explored how to use the Mustache template engine with ORDS mle/javascript Handlers to transform SQL query responses into application/json responses. If you missed that, check it out – Transform your SQL Results with Mustache and ORDS JavaScript Handlers.
The idea was inspired by a tweet from @FriedholdMatz and I put together an initial implementation at the time which I have since refined.
Easily done with @OracleREST ! I haven't written this up yet…TEMPLATES table contains a list of templates and mle/javascript handler does the work 👍 pic.twitter.com/mRQvTcbQJE
This follow-up dives deeper into the concept of using Mustache as a service, enabling users to submit their own template definitions and payloads for dynamic transformations. This service can be incredibly useful when you need to allow external users or applications to define how they want data structured without modifying your code every time.
In fact, this article is going to cover a wide range of subjects related to developing services with ORDS. These include:
Use of implicit parameters both in query, PL/SQL and mle/javascript handlers.
Protecting all services defined in a module using a single privilege definition.
Invoking the secured services from Postman using OAuth 2.0 Client Credentials to automatically obtain a new bearer token.
Let’s walk through the implementation steps. As a REST Enabled user connect to your 23ai database where you have already defined your MLE library for Mustache as covered in the previous article. Just like in that article, our REST Enabled user in this article is the HR schema and all statements are executed in the database as that user.
NOTE! The following steps are based on Oracle 23ai MLE database objects which you should have created from Transform your SQL Results with Mustache and ORDS JavaScript Handlers. Having the MLE Module for Mustache and an MLE library making it available to mle/javascript handlers is a prerequisite.
Step 1: Setting up the Database Table for Mustache Templates
First, we need a database table to store the template definitions. This table will allow clients to create and manage their templates, identified by a unique ID.
CREATE TABLE mustache_templates (
template_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
template_name VARCHAR2(100) not null,
template_text CLOB not null,
template_owner VARCHAR2(200) not null,
template_content_type VARCHAR2(100) not null,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
In this table, template_name is used for a meaningful description of the Mustache template, while template_text will store the Mustache template itself. You will also notice a template_owner column which we will use to keep the rows distinct for each template owner. Put simply, a user, identified by their OAuth client identifier, manages their own templates. Both the primary key template_id and the created_at columns are automatically assigned values on insert.
Step 2: Defining ORDS services for Mustache Template creation, retrieval and use
Now, we need an API endpoint to submit, retrieve and use templates. Access to each row is going to be restricted based on the OAuth client identifier so we will require a slightly more complex set of logic than what REST Enabling the mustache_templates table will provide. That will all be taken care of in one module with base path = /templates/ and a few of REST Service templates defined. These are:
/templates/. – This will have a GET handler to retrieve all rows the user has access to and a POST handler to add new Mustache templates.
/templates/:id – This will have a GET handler to retrieve a specific Mustache template by template_id and a PUT handler to update that specific Mustache template.
/templates/id/generate – This will have a single POST handler to apply the Mustache template to the request body and return the generated content.
The GET handlers will be simple query handlers. The PUT and handler will involve a PL/SQL block. One of the POST handlers will be defined using PL/SQL and the other will use JavaScript. In all cases they will use the ORDS provided implicit parameter for referencing the current user which will be the Client ID of the OAuth client used at runtime.
Define the ORDS REST module
This PL/SQL block will create the module which all the service handlers will belong to.
ORDS uses templated REST Service definitions and we will create three now:
BEGIN
ORDS.DEFINE_TEMPLATE(
p_module_name => 'templates',
p_pattern => '.',
p_comments => 'Retrieve all Mustache templates the runtime user has access to and create new ones');
ORDS.DEFINE_TEMPLATE(
p_module_name => 'templates',
p_pattern => ':id',
p_comments => 'Retrieve or update a specific Mustache template');
ORDS.DEFINE_TEMPLATE(
p_module_name => 'templates',
p_pattern => ':id/generate',
p_comments => 'Transform the request payload using the specified Mustache template');
COMMIT;
END;
With these in place the next step is to define the relevant handlers for the GET, PUT and POST methods.
GET: /templates/
This will retrieve all Mustache template records that the runtime user has access to. This query uses the implicit parameter :current_user and a special column alias $.id which is used to generate a self reference link in the response.
BEGIN
ORDS.DEFINE_HANDLER(
p_module_name => 'templates',
p_pattern => '.',
p_method => 'GET',
p_source_type => 'json/collection',
p_source =>
'select TEMPLATE_ID as "$.id",
TEMPLATE_ID, TEMPLATE_NAME, TEMPLATE_CONTENT_TYPE, TEMPLATE_TEXT, CREATED_AT from MUSTACHE_TEMPLATES where TEMPLATE_OWNER = :current_user');
COMMIT;
END;
The above handler will return a response similar to this when invoked by an authorised user:
This handler is a PL/SQL handler which will insert a new Mustache template record. This handler has some content of note.
The implicit parameter :current_user is used to limit queries to rows that the runtime user has access to.
Once the runtime user has 10 Mustache templates they can not create any more.
The implicit parameters :status_code and :forward_location are used to generate a reference to the just inserted Mustache template. ORDS will generate a response body containing the json representation of that new record.
You will notice that parameters are not defined for :template_name, :template_content_type and :template_text. When not explicitly defined they are assumed to be fields in the request body.
The p_mimes_allowed argument makes it clear that application/json payloads are expected by this handler.
BEGIN
ORDS.DEFINE_HANDLER(
p_module_name => 'templates',
p_pattern => '.',
p_method => 'POST',
p_source_type => 'plsql/block',
p_mimes_allowed => 'application/json',
p_source =>
'DECLARE
existing_templates_count NUMBER;
new_template_id NUMBER;
BEGIN
select count(*) into existing_templates_count from mustache_templates where TEMPLATE_OWNER = :current_user;
if existing_templates_count < 10 then
insert into MUSTACHE_TEMPLATES (TEMPLATE_NAME, TEMPLATE_CONTENT_TYPE, TEMPLATE_TEXT, TEMPLATE_OWNER) VALUES(:template_name, :template_content_type, :template_text, :current_user)
RETURNING template_id INTO new_template_id;
:status_code := 201;
:forward_location := new_template_id;
else
-- Too many records
:status_code := 400;
end if;
END;');
COMMIT;
END;
This handler will take a request payload such as the following…
This will retrieve a Mustache template by its unique identifier. Similar to the GET /templates/ handler it is a simple query but uses the :current_user implicit parameter and the $.id alias for generating a self link in the response. Note that the value for :id in the query comes from the URL path pattern of the template.
BEGIN
ORDS.DEFINE_HANDLER(
p_module_name => 'templates',
p_pattern => ':id',
p_method => 'GET',
p_source_type => 'json/item',
p_source =>
'select
TEMPLATE_ID as "$.id",
TEMPLATE_ID,
TEMPLATE_NAME,
TEMPLATE_CONTENT_TYPE,
TEMPLATE_TEXT,
CREATED_AT
from MUSTACHE_TEMPLATES
where TEMPLATE_OWNER = :current_user and TEMPLATE_ID = :id');
COMMIT;
END;
This returns the specific Mustach template requested so one can see what it produces based on the content in the template_text. Hopefully the template_name is descriptive too 😀
PUT: /templates/{id}
This handler will update an existing Mustache template if the runtime user has access to the row. Similar to the POST /templates/ handler it uses implicit parameters for the SQL query but also to instruct ORDS on how to generate the response.
And now the mle/javascript handler that will apply the Mustache template to the request body to return a generated document. The request payload will be the data to be used into the Mustache template which is specified by the templates identifier. This endpoint will:
Retrieve the template corresponding to template_id.
Use the JavaScript Mustache engine to transform the payload.
Similar to the mle/javascript handler in the previous article it relies on an MLE environment to reference the Mustache template. Other items to note about this mle/javascript handler:
Checks the content_type implicit parameter and returns an appropriate status code if it is not application/json.
Provides fetchInfo to database session so that it knows how to represent CLOB values from the TEMPLATE_TEXT column.
Refers to the :id parameter using the uri_parameters of the request.
Invoking this service specifying the Mustache template id in the URL will transform the request payload into a generated document using the specified template. It will even set the response content type as specified by the Mustache template.
The eagle eyed reader will have noticed that this mle/javascript handler source is checking that the content type of the request is application/json and strictly speaking the Mustache Templating Engine can work with more than JSON as the source context. As an additional homework exercise you could explore working with different request payload structures.
Step 3: Protecting the Services
For this service we’re going to restrict access to only authenticated users. Note that every handler refers to the implicit parameter :current_user. Every row will belong to a specific template_owner user and that will correspond to an OAuth client that we can issue for each prospective user. To achieve that a Role must be defined and a Privilege explicitly protecting the module created earlier is required. Run this…
DECLARE
L_PRIV_ROLES owa.vc_arr;
L_PRIV_PATTERNS owa.vc_arr;
L_PRIV_MODULES owa.vc_arr;
BEGIN
ORDS.CREATE_ROLE(
P_ROLE_NAME => 'blog.peterobrien.MustacheTemplateUser'
);
L_PRIV_MODULES( 1 ) := 'templates';
L_PRIV_ROLES( 1 ) := 'blog.peterobrien.MustacheTemplateUser';
ORDS.DEFINE_PRIVILEGE(
P_PRIVILEGE_NAME => 'blog.peterobrien.MustachTemplate',
P_ROLES => L_PRIV_ROLES,
P_PATTERNS => L_PRIV_PATTERNS,
P_MODULES => L_PRIV_MODULES,
P_LABEL => 'Mustache Template Privilege',
P_DESCRIPTION => 'Protects access to the Mustache Template module',
P_COMMENTS=> 'Mustache Template module provides the managed and use of Mustache Template as a Service.'
);
COMMIT;
END;
Send a request to one of the handlers now and one should get a HTTP 401 response.
With the module now directly protected let’s create an OAuth client that can access it. Note that this is using OAUTH package but ORDS is moving to a new OAUTH_SECRETS package which I will refer to here when the documentation for it is published…
BEGIN
ORDS_METADATA.OAUTH.CREATE_CLIENT(
P_NAME => 'TemplateClient1',
P_GRANT_TYPE => 'client_credentials',
P_OWNER => 'HR',
P_DESCRIPTION => 'A client for the Mustache Template as a Service module',
P_SUPPORT_EMAIL => 'test@example.com',
P_PRIVILEGE_NAMES => 'blog.peterobrien.MustachTemplate'
);
ORDS_METADATA.OAUTH.GRANT_CLIENT_ROLE(
P_CLIENT_NAME => 'TemplateClient1',
P_ROLE_NAME => 'blog.peterobrien.MustacheTemplateUser'
);
COMMIT;
END;
Execute a select statement on USER_ORDS_CLIENTS to get the CLIENT_ID and CLIENT_SECRET for this TemplateClient1.
select
client_id, client_secret
from user_ords_clients
where name = 'TemplateClient1';
With that client_id and client_secret you can get an access token which can then be used to invoke the Mustache template services we have created. The :current_user value in our handlers will be the client_id value from the request.
The access token will expire after an hour and another one will have to be requested. Rather than going through all that manually we can use our REST client to manage that, even automatically requesting a new access token when the current one expires. In this article we’ll use Postman but there are alternatives. In fact if you are coding a client application to use the Mustache Template as a Service endpoints then a similar framework for handling the OAuth token lifecycle will be required. For Postman, we’ll define our authorisation flow and requests in a collection called Mustache.
In “My Workspace” I have a collection called Mustache
The collection is useful for keeping important metadata in one place such as how authentication and authorisation will be achieved…
In the Authorization definition of my Mustach collection I specify that I will use OAuth 2.0 as the Auth Type
Keep on scrolling down to enter specifics.
Let’s look at some of those specifics…
The token name doesn’t really matter. You can use any name you want.
The grant type should match your ORDS OAuth client definition: Client Credentials
The access token URL will be the full URL for your REST Enabled schema’s access endpoint. Generally that is <server>/ords/<schema alias>/oauth/token For example: https://ords.example.com/ords/hr/oauth/token
The client ID and client secret comes from the query on user_ords_clients earlier.
The client authentication should be Send as Basic Auth header. Postman will send the Client ID and Client Secret as basic authentication to the Access Token URL endpoint to get an access token.
Keep on scrolling down and press that Get New Access Token button to verify it all works.
With that in place then every request defined in the Mustache collection can just inherit that definition from the collection and the OAuth client token lifecycle is all automagically taken care of.
Of course, I’m running all this on my Oracle Free Tier 23ai Autonomous Database in Frankfurt
Note that if you import that Mustache Collection into Postman the hostname referred to will be ords-23ai.adb.eu-frankfurt-1.oraclecloudapps.com which doesn’t exist. You will have to change those entries to point to your own system.
Don’t want to set this up yourself but still want to try it out? Leave a comment below and I’ll get back to you with your own Client ID and Client Secret for you to use on a trial basis for a few days. All I have to do is create a new OAuth client with the same privilege and role and then forward you the Client ID and Client Secret. After a few days I can just delete the OAuth client.
Conclusion
By extending ORDS with Mustache templates as a service, you now have a flexible system that allows users to dynamically define and utilize templates. This can be a practical, reusable tool for building customisable reporting, notifications, or any scenario where the structure of data needs to be adaptable.
Feel free to experiment with more complex templates and explore how Mustache’s logic-less approach can simplify many data transformation tasks in your applications.
Stay tuned for future articles where we dive deeper into templating and ORDS functionalities!
If you have any questions or feedback, drop a comment below.
When dealing with SQL data, format and structure is crucial. Raw data isn’t always the most readable or useful format, especially when you need to expose it through an API. This is where Oracle REST Data Services (ORDS) comes in handy. It provides a secure and convenient JSON representation of the SQL query results in a JSON format by default. In this article, we’ll walk through using ORDS JavaScript handlers with the Mustache template engine to format an XML response from SQL query results. What we cover here is based on the previous article Multilingual Engine (MLE) for JavaScript Handlers. That article introduced an ORDS Module called demo_mle_javascript and in this article we will add to it.
The high level flow
Introducing the key components
Oracle REST Data Services (ORDS)
Oracle REST Data Services (ORDS) is a powerful tool that allows you to expose database functionalities as RESTful services. It simplifies the process of creating APIs for your Oracle database, making it easier to integrate with other systems and applications.
Mustache Template Engine
Mustache is a logic-less template engine that helps you format data into any desired format. Its simplicity and flexibility make it a great choice for rendering text in a wide variety of formats: HTML, JSON, XML, etc. Mustache uses templates to define the structure of the output, which can then be populated with data.
Oracle 23ai Database and the Multilangual Engine
With Oracle Database Multilingual Engine (MLE), developers have the option to run JavaScript code through dynamic execution or with persistent MLE modules stored directly in the database.
Bringing all together
Just as with the previous article which showcased Multilingual Engine (MLE) for JavaScript Handlers in ORDS we will use the 23ai database available at Oracle Autonomous Database. There we have a /ords/hr/demojs/employee/:id GET handler which has a JavaScript implementation for running a SELECT query to get an employee record for a employee_id provided in the URI.
In this article we’ll implement in JavaScript a GET handler to return all employee records but in an XML format. It will be very similar to the previous handler but reuse a JavaScript library so there’s actually less code. The additional step is that the JavaScript Mustache template engine will be imported into an MLE Module and then referred to by our JavaScript source in our ORDS JavaScript handler to transform the JSON representation of a query result set into an XML document returned by the service.
Defining the Mustache MLE Module
Mustache JavaScript
To use Mustache in ORDS JavaScript handler, you need to define it as an MLE module so that it can be imported by the dynamic MLE JavaScript in the handler source. The simplest way to get this done is include it from a content delivery network like jsDelivr. Here’s the URL for Mustache version 4.2.0: https://cdn.jsdelivr.net/npm/mustache@4.2.0/mustache.mjs.
The availability of JavaScript components like this supports self-contained and reusable code, key to developing successful software projects. We will use this library unmodified. However, due to a difference in architecture, module imports behave slightly differently in the Oracle Database when compared to other development environments. For example, JavaScript source code used with Node.js is stored in a specific directory structure on disk. MLE modules are stored together with the database, rather than in a file system, so must be referenced in a different manner. Let’s get that Mustache MLE module defined
Creating the MLE Module in Autonomous Database
The simplest way to define a module, from an external source, in an 23ai Autonomous Database is to use ORDS Database Actions. Note that one must be using a 23ai database, otherwise the menu option in Database Actions does not appear. So, first step, login to Database Actions ( AKA SQL Developer Web ) https://my-autonomous-database.oraclecloudapps.com/ords/sql-developer and choose the Development->JavaScript menu option…
The Development->JavaScript section provides a handy UI for managing MLE Modules
If this is your first time defining an MLE Module the JavaScript area in Database Actions will look like this
In the MLE Editor there is a globe icon with an arrow pointing downwards. This is the Open from URL button. Press that, enter the URL https://cdn.jsdelivr.net/npm/mustache@4.2.0/mustache.mjs and press the Open button to load the text.
You should then see the source in the MLE Module editor. Enter MUSTACHE as the name ( leave the Environment info blank ) and press the Save icon to create the MLE Module.
Save the MLE Module source and call it MUSTACHE
The last part of that source, at line 764, the JavaScript exports a mustache object and that is what we will refer to in our own JavaScript: export default mustache;
Now, to use that MLE Module it must be included in an MLE Environment. Amongst other things, MLE environments are used for name resolution of JavaScript module imports. For this example we will define an MLE Environment called LIBRARY_ENV where we specify a namespace for modules we want to reuse.
Let’s switch to an SQL Worksheet environment for the rest of this article. Run this to create that environment…
With that in place, we’re now ready to create our JavaScript handler.
Developing the ORDS REST Service
Now, define a JavaScript handler that will query all records from HR.EMPLOYEES table and format the SQL results into an XML response using Mustache. The service will be part of the existing demo_mle_javascript module and available at employees/ so we will define a template and a handler:
Not much to explain about the above. We’re defining a pattern for a URI to add to an existing ORDS REST Service module and will have one or more handlers defined.
The parameters to the DEFINE_HANDLER procedure are more interesting so let’s take a look at them:
p_module_name => 'demo_mle_javascript', This states which module the handler belongs to.
p_pattern => 'employees/', This states which template in that module the handler belongs to.
p_method => 'GET', Handlers are define for a specific HTTP method. This handler will be for GET requests.
p_source_type => 'mle/javascript', Handler source can be of various types. Some simple such as a basic select query and some more complex such as pl/sql block. This handler's source is dynamic MLE JavaScript.
p_mle_env_name => 'LIBRARY_ENV', When dynamic MLE JavaScript imports a module it must state the MLE Environment where that module's namespace is defined. This is important for this handler's source and MUST be specified.
The p_source parameter content is more complex and deserves it’s own explanation. At a high level the steps are:
Define a query and execute it, storing the reference to the results in a variable called res
Import the Mustache MLE module and refer to it as mustache. Note that import is asynchronous so we wait for it to complete.
Defines a Mustache template for the XML structure. The curly bracket syntax is Mustache specific. Refer to Mustache documentation for more information.
Formats the data using Mustache and save that as a variable called output. Note that we reference the default object because that is what was in the export definition in the imported mustache.mjs
Sends the formatted XML as the HTTP response.
Remember that, as per ORDS REST JavaScript Handler developer guide documentation that the defined JavaScript code must be inside an anonymous function that receives the following two parameters: ORDS request object and ORDS response object. In the p_source parameter for this handler those two parameters are called req and resp respectively.
(req, resp) => { // define the query const query = 'select * from employees order by employee_id'; // execute it const res = session.execute(query); // import the mustache JavaScript library const mustache = await import('mustache');
// define the template var template = '<employees>{{#rows}} <employee id="{{EMPLOYEE_ID}}" first_name="{{FIRST_NAME}}" last_name="{{LAST_NAME}}" salary="{{SALARY}}"/>{{/rows}}</employees>';
// format the data from the result set var output = mustache.default.render(template, res);
// send the formatter XML as the HTTP response resp.content_type('application/xml'); resp.status(200); resp.send(output); }
Testing and Debugging
Testing the Endpoint
To test your new RESTful service, use tools like Postman or curl. Here’s an example using curl:
curl -X GET https://cust-23aidb.adb.eu-frankfurt-1.oraclecloudapps.com/ords/hr/demojs/employees/
Obviously your server hostname will be different. Also, you don’t have to be using the HR schema or have your ORDS REST Service module base path as demojs. So your URL could be way different but if you’ve gotten this far, you’ve already figured that out.
You should see an XML response formatted according to your Mustache template.
Debugging Common Issues
JavaScript Errors: Check for syntax errors or issues with the JavaScript handler.
SQL Query Problems: Ensure your SQL query is correct and returns the expected results.
Template Errors: Verify the Mustache template syntax and placeholders.
Conclusion
In this article, we’ve shown how to use Mustache and ORDS JavaScript handlers to transform SQL query results into beautifully formatted XML. By following these steps, you can enhance the presentation of your data and make it more accessible and readable.
ORDS now supports user-defined REST Resource Handlers powered by the Oracle Database Multilingual Engine (MLE) for JavaScript—the ORDS.DEFINE_HANDLER procedure now includes a new ‘mle/javascript’ p_source_type for MLE JavaScript modules stored directly in the Oracle database.
The Multilingual Engine (MLE) was a significant innovation introduced in Oracle Database 21 for providing In-Database JavaScript and now is a key feature of Oracle Database 23ai. In Data Magic: Oracle HR REST Service with Faker.js Sample Data I provided an example ORDS REST Services PL/SQL block handler which interacted with an MLE JavaScript module and an MLE Call Specification to generate sample data for the HR Employees table.
The new handler type mle/javascript in ORDS 24.1.1 now makes it possible to code business logic in JavaScript and make that available over HTTP(S). With Oracle Database 23ai and ORDS 24.1.1 available on cloud.oracle.com for free it is even easier to explore the capabilities of JavaScript-as-a-Service with ORDS.
Get a Database 23ai autonomous database at cloud.oracle.com
The goal for this article to walk you through defining a GET handler which uses JavaScript to run a select query based on a parameter passed and then return some JSON data. We’ll use the HR sample schema and the EMPLOYEES table in particular.
Permissions
Not every database user will have the database privileges to execute dynamic JavaScript in the database so we must first get those permissions in place.
GRANT EXECUTE ON JAVASCRIPT TO HR;
GRANT EXECUTE DYNAMIC MLE TO HR;
The above will have to be executed by a sufficiently privileged user. With the Autonomous Database at cloud.oracle.com that could be the ADMIN user.
Define the service
Assuming the HR schema is already REST Enabled simply connect to the database as that user and run the following…
We’ll take a look at that handler definition in detail a little later but for now, let’s try it out. Send a GET request to /ords/hr/demojs/employees/102 and you will see the results of the query executed:
Specify an employee identifier that does not exist and you should get a HTTP 404 response.
Using the mle/javascript handler on Autonomous Database to indicate a record was not found
Handler in detail
The first thing to point out is the new handler source type: mle/javascript.
p_source_type => 'mle/javascript',
Note that this source type is not only applicable to the DEFINE_HANDLER procedure but also the DEFINE_SERVICE procedure too.
The source is a dynamic definition of JavaScript function which is passed a HTTP Request object and a HTTP Response object. In the source we can specify what variable names will be used for those two objects. It is fairly common to refer to them as req and resp but any names will do. In our case, we’ll stick to the convention.
(req, resp) => { ... JavaScript goes here ! ... }
In-Database JavaScript references
The database session for the request can be referred through the variable session and functions can be invoked, such as running a query. In this snippet we define a query which takes a bind variable and provide a value from the HTTP Request URI when executing that query.
const query = 'select employee_id, first_name, salary from employees where employee_id = :1'; const res = session.execute(query, [req.uri_parameters.id]);
Recall that the id was defined as a parameter in the template definition. The res reference now contains the result set metadata and rows reference for the executed query.
Evaluate the query results and set the HTTP Response
Check the result set to see if there are any rows. If there are, construct a JSON object to return in the HTTP Response. Otherwise, just set the HTTP status code to 404.
Note that for ease of readability I have removed the escaping single quotes from any string references.
Further reading
There are a lot of concepts that have been quickly skimmed over. ORDS initially introduced a limited JavaScript-as-a-Service product offering with javascript module plugins which required GraalVM. With MLE the JavaScript execution can be performed in the database so a GraalVM runtime environment is not required but more significantly, any REST Enabled database user can define their JavaScript business logic and make it available as a REST service.
Two important MLE related documents to discover more about what you can do with MLE are:
Through REST Enabling packages, procedures and functions, or supporting custom pl/sql handlers, ORDS makes it easy to access business logic associated with your data. Now you have another string to your bow: mle/javascript.
Peter's Random Topic Library 