Category: Oracle REST Data Services

Transform your SQL Results with Mustache and ORDS JavaScript Handlers

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…

CREATE MLE ENV LIBRARY_ENV IMPORTS ('mustache' module MUSTACHE);

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:

begin
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'demo_mle_javascript',
      p_pattern        => 'employees/');

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'demo_mle_javascript',
      p_pattern        => 'employees/',
      p_method         => 'GET',
      p_source_type    => 'mle/javascript',
      p_mle_env_name   => 'LIBRARY_ENV',
      p_source         => 
q'~ 
 (req, resp) => {
    const query = 'select * from employees order by employee_id';
         const res = session.execute(query);
    const mustache = await import('mustache');

    var template = '<employees>{{#rows}} <employee id="{{EMPLOYEE_ID}}" first_name="{{FIRST_NAME}}" last_name="{{LAST_NAME}}" salary="{{SALARY}}"/>{{/rows}}</employees>';

    var output = mustache.default.render(template, res);
    resp.content_type('application/xml');
    resp.status(200);
    resp.send(output);
}
~'
      );
end;

Let’s break that down and talk about the distinct parts…

ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'demo_mle_javascript',
      p_pattern        => 'employees/');

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:

  1. Define a query and execute it, storing the reference to the results in a variable called res
  2. Import the Mustache MLE module and refer to it as mustache. Note that import is asynchronous so we wait for it to complete.
  3. Defines a Mustache template for the XML structure. The curly bracket syntax is Mustache specific. Refer to Mustache documentation for more information.
  4. 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
  5. 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

  1. JavaScript Errors: Check for syntax errors or issues with the JavaScript handler.
  2. SQL Query Problems: Ensure your SQL query is correct and returns the expected results.
  3. 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.

Additional Resources

Try implementing this solution in your projects and feel free to share your experiences or ask questions in the comments below!

Multilingual Engine (MLE) for JavaScript Handlers

The eagle eyed amongst you would have noticed this in the ORDS 24.1.1 release notes

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…

BEGIN
  ORDS.DEFINE_MODULE(
      p_module_name    => 'demo_mle_javascript',
      p_base_path      => '/demojs/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => 'Demonstrate MLE Javascript Handler functionality in ORDS 24.1.1.
Javascript-as-a-Service.');

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'demo_mle_javascript',
      p_pattern        => 'employees/:id',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'demo_mle_javascript',
      p_pattern        => 'employees/:id',
      p_method         => 'GET',
      p_source_type    => 'mle/javascript',
      p_items_per_page => 0,
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         => 
' 
 (req, resp) => {  
    const query = ''select employee_id, first_name, salary from employees where employee_id = :1'';
    const res = session.execute(query, [req.uri_parameters.id]);

    if (res.rows.length > 0) {
        var employee_item = {};
        employee_item[''employee_id''] = res.rows[0].EMPLOYEE_ID;
        employee_item[''first_name''] = res.rows[0].FIRST_NAME;
        employee_item[''salary''] = res.rows[0].SALARY;
        resp.content_type(''application/json'');
        resp.json(employee_item);
    } else {
        resp.status(404);
    }
}
');
        
COMMIT;

END;

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:

{
  "employee_id": 102,
  "first_name": "Lex",
  "salary": 17000
}

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.

if (res.rows.length > 0) {
   var employee_item = {};
   employee_item['employee_id'] = res.rows[0].EMPLOYEE_ID;
   employee_item['first_name'] = res.rows[0].FIRST_NAME;
   employee_item['salary'] = res.rows[0].SALARY;
   resp.content_type('application/json');
   resp.json(employee_item);
} else {
   resp.status(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.

More detailed documentation and examples to come.

Java 11 – Diagnose JDBC proxy connection

A few years ago this Diagnose JDBC proxy connection article was really useful to point people to if they had an issue with JDBC/UCP connections in ORDS. Since then ORDS has moved to Java 11 so an update on the steps is long overdue…

…and it is now available at https://github.com/pobalopalous/ords-ucp-example. There you will find a class that does the same JDBC proxy test but getting it running is much simpler.

Simply clone the repository or download it as a zip file and follow the steps in the README.

This test could be used with the standard Oracle JDBC/UCP distributed jars but the original intention is use the jars that are distributed with ORDS. Your version of ORDS might be different so the README shows steps to find the exact jar names to use.

What you should see when the UCPProxyTest is successful

RESTful CSV file

An article from earlier this year by Ulrike Schwinn titled Oracle Object Storage Access for all Oracle Databases with DBMS_CLOUD got me thinking about doing more with CSV data because it can be treated as a table through DBMS_CLOUD.CREATE_EXTERNAL_TABLE. In essence, provide secure RESTful access to the data, and even visualise that data through charts.

Very quickly, we’ll go from a uploading a Salary.csv dataset to rendering a chart of Job Titles for the highest education level and permitting RESTful queries on data…

Example chart generated on a sharable URL
Query the CSV data through ORDS RESTful interface

Salary Data

Let’s start with a CSV dataset. For this exercise we’ll use a public domain Salary by Job Title and Country from kaggle.com. The download is a ZIP archive with two files. In this case it is just the Salary.csv which will be used.

This dataset provides a comprehensive collection of salary information from various industries and regions across the globe.
Oracle Object Storage Buckets will be used for storing the Salary.csv

I’m not going into all the details of downloading the archive file, unzipping it, creating a bucket, and uploading the Salary.csv but needless to say, those are important steps to get you this stage…

Dataset is uploaded to my ‘example-bucket’

Note that the file has a URL for accessing and downloading it but that URL is not publicly accessible.

The file can be accessed at this URL, but not publicly

External Table

In the database, the DBMS_CLOUD package can create a table which has it’s data loaded from the CSV file. To get access to object storage from the database we require a credential object through DBMS_CLOUD.CREATE_CREDENTIAL. Details on achieving that are already covered in Ulrike’s Oracle Object Storage Access for all Oracle Databases with DBMS_CLOUD article and my previous article Data move mojo with ORDS 21.3.0. In fact, I’m going to reuse that DATAPUMP_CRED_NAME credential. That’s just out of convenience for me. You be you and do your own thing.

To create the SALARY table we must specify a column list with data types and sizes. That requires figuring out, based on the data and also the description on kaggle.com, what data types makes sense. Some columns are obviously numeric, but it is not always clear how big or small those columns should be.

begin 
    DBMS_CLOUD.CREATE_EXTERNAL_TABLE(table_name=>'SALARY', 
                   credential_name => 'DATAPUMP_CRED_NAME',
                   file_uri_list => 'https://frckdrxj9oco.objectstorage.eu-frankfurt-1.oci.customer-oci.com/n/frckdrxj9oco/b/example-bucket/o/Salary.csv',
                   format => json_object('type' VALUE 'CSV', 'skipheaders' VALUE '1'),
                   column_list =>'Age NUMBER(4,1),
                                  Gender varchar2(6),
                                  Education_Level NUMBER(1),
                                  Job_Title varchar2(100),
                                  Experience NUMBER(4,1),
                                  Salary NUMBER(9,2),
                                  Country varchar2(200),
                                  Race varchar2(100),
                                  Senior NUMBER(1)'
                            );
end;
/

Note that the file_uri_list parameter came directly from the object details panel in Object Storage.

Unless there’s a syntax error, creating the table will always work. You’ll only find out about problems when you run a select query. That’s when the DB attempts to read the file.

If you have done this right…select * from salary returns data from the Salary.csv file.

Chart

ORDS Database Actions has a charts and dashboard feature that we’re going to use now. Charts enable you to create charts from the database. The chart is constructed using the input SQL command. Dashboards enable you to group charts together to create reports. Let’s define a bar chart showing the number of job titles for the highest education level. So that the chart is not too busy I’m limiting the chart to only show job titles that have more than 10 occurrences.

Defining a bar chart for job titles

For simplicity my chart definition says that it is unprotected, so it is publicly available and anyone can see it with the URL. That’s ok with this scenario because the data is in the public domain anyway but please consider the protection on your charts and dashboards.

That URL can be shared and it will render in the browser like this…

Note that the data can be viewed as a chart, or as a table.

Although the chart and its data is available to anyone with the URL, the data is just the data for the chart.

No direct access to the original Salary.csv data, just aggregation from a specific query

For many folks the only way they can create a chart based on a CSV file and share it is through using a spreadsheet and sharing that, which means sharing the underlying data. In this case the data is not shared, just the chart. So far so cool. Now let’s look at sharing that data and allowing clients to query it.

AutoREST

This will be a short section. It is as simple as:

EXEC ORDS.ENABLE_OBJECT(p_object => 'SALARY');

That’s it. The SALARY table is now accessible through a REST service at /ords/<my schema>/salary/. Moreover, the Filter Object query parameter provides a powerful search mechanism through the Salary.csv file. For example, let’s find people in the data set with more than 30 years experience: q={"experience":{"$gt":30}}

Records in the CSV can be search with relational operators!

You can’t do that so easily in a text editor 😁

See Filter Objects in the ORDS Developer Guide for more information. Of course this RESTful URL can be protected to require a authorisation for accessing it too while the chart is left unprotected.

Note that with standard REST Enabled tables you can perform DELETE, GET, POST and PUT methods but with external tables, the only functional method is GET. All others will get a HTTP 555 response with this message: ORA-30657: operation not supported on external organized table.

Conclusion

If you have followed the above steps you have opened up new ways of accessing, sharing and interrogating data in a CSV file.

To reiterate, when using an external table the data comes from files in the Cloud or from files in a directory. The data does not get loaded or copied into a table in the database. Drop in a new Salary.csv file with different data and it is immediately picked up on the next query.

The combination of Object Storage, Autonomous Database and Oracle REST Data Services provide some interesting options for doing more with CSV files. It has its limits though. To be realistic, large volumes of data and complex queries would require actually loading copies of the data into the database. For that one should consider Autonomous Database Data Studio to load the data. You can still use ORDS as above to share the data as charts or expose a RESTful interface to the data.

This article was inspired by Ulrike’s January blog post. More excellent articles on data management can be found at https://blogs.oracle.com/authors/ulrike-schwinn. Well worth a look.

RESTful Car-Racing Example

Updated for 23ai. The structure for Duality Views changed from 23free to 23ai release. In summary, the identifiers for rows are now _id rather than driverId, raceId, teamId.

Oracle Database 23c Free introduced the concept of JSON-Relational Duality Views which allows you to overlay a JSON document representation over a relational data model. In this article we’ll take Car-Racing Duality View Tutorial setup script and build on that to explore Oracle’s RESTful interface to Duality Views – Oracle REST Data Services (ORDS)

What Are JSON-Relational Duality Views?

JSON-Relational Duality Views provide a seamless way to interact with both JSON documents and relational data within Oracle Database. These views expose relational data as JSON documents, allowing developers to perform query and DML (Data Manipulation Language) operations using conventional SQL or directly with JSON.

Key Features and Benefits:

Unified Access:

  • Developers can choose their preferred approach: SQL for relational data or JSON for document-centric applications.
  • Changes made via one method automatically reflect in the other, maintaining consistency.

Updatable Views:

  • Duality views allow insertion, update, deletion, and querying of documents.
  • Inserting a row into the root table creates a new document, and changes propagate automatically.

Document-Identifier Field:

  • Each document has an identifier (e.g., _id) that corresponds to the primary-key columns of the underlying tables.
  • Ensures efficient updates and consistency.

Integration with APIs:

  • Use SQL/JSON functions, Oracle Database API for MongoDB, Simple Oracle Document Access (SODA), or Oracle REST Data Services (ORDS) to manipulate data.
  • Analytics, reporting, and machine learning can operate on the same data using SQL, PL/SQL, JavaScript, or C.

Car-Racing Example: The Track Ahead

Data related to Formula 1 car races are used in Oracle documentation to present the feature. In our car-racing example, we’ll explore three kinds of documents:

1. Team Document

Contains information about racing teams, including team names and the drivers associated with each team. The underlying tables are TEAM and DRIVER. Example team duality view structure:

{
  "_id":307,
  "name":"Williams",
  "points":15,
  "driver":[
    {
      "driverId":115,
      "name":"Alexander Albon",
      "points":15
    },
    {
      "driverId":116,
      "name":"Logan Sargeant",
      "points":0
    }
  ]
}

2. Driver Document

Represents individual drivers, including their names, and team affiliations. The underlying tables are DRIVER, TEAM and DRIVER_RACE_MAP. Example driver duality view structure:

{
  "_id":115,
  "name":"Alexander Albon",
  "points":15,
  "teamId":307,
  "team":"Williams",
  "race":[
    {
      "driverRaceMapId":22,
      "raceId":202,
      "name":"Saudi Arabian Grand Prix",
      "finalPosition":3
    }
  ]
}

3. Race Document

Captures race details, podium standings, and results for each driver. The underlying tables are RACE, DRIVER, and DRIVER_RACE_MAP. Example driver duality view structure:

{
  "_id":202,
  "name":"Saudi Arabian Grand Prix",
  "laps":50,
  "date":"2022-03-27T00:00:00",
  "podium":{},
  "result":[
    {
      "driverRaceMapId":24,
      "position":1,
      "driverId":101,"
      name":"Max Verstappen"
    }
  ]
}

These example Duality Views and their corresponding tables can be created using DualityViewTutorial.sql which sets to scene for Using JSON-Relational Duality Views.

Setup for Oracle REST Data Services (ORDS)

The Using JSON-Relational Duality Views chapter outlines some examples of using ORDS and references the Support for JSON-Relational Duality View chapter in the ORDS documentation but does not go into details on how to achieve that.

Here’s an additional script for REST Enabling the tables and views so that an access token is required to access them. The following has been tested using the Oracle Developer DB Virtual Machine with Oracle 23free Database. See previous articles on the topic: Oracle Developer DB Virtual Machine – a closer look and Get the latest – Enhance your DB Developer VM. The database account used is HR which has already been REST Enabled. From here on, every URL will start with http://localhost:8080/ords/hr/ because that is the easiest to reference with the Developer DB Virtual Machine.

BEGIN
    ords.enable_object(p_object => 'TEAM', p_auto_rest_auth => TRUE);
    ords.enable_object(p_object => 'DRIVER', p_auto_rest_auth => TRUE);
    ords.enable_object(p_object => 'RACE', p_auto_rest_auth => TRUE);
    ords.enable_object(p_object => 'DRIVER_RACE_MAP', p_auto_rest_auth => TRUE);
    ords.enable_object(p_object => 'RACE_DV', p_object_type => 'VIEW', p_auto_rest_auth => TRUE);

    ords.enable_object(p_object => 'DRIVER_DV', p_object_type => 'VIEW', p_auto_rest_auth => TRUE);

    ords.enable_object(p_object => 'TEAM_DV', p_object_type => 'VIEW', p_auto_rest_auth => TRUE);

    oauth.delete_client(p_name => 'Car Racing Client');
    oauth.create_client(p_name => 'Car Racing Client', 
                    p_grant_type => 'client_credentials', 
                    p_description => 'Used for interacting with Car Racing Duality View Examples', 
                    p_owner => sys_context( 'userenv', 'current_schema' ),
                    p_support_email => 'janus@example.com', 
                    p_privilege_names => null);
  OAUTH.grant_client_role(
    p_client_name => 'Car Racing Client',
    p_role_name   => 'oracle.dbtools.role.autorest.HR.TEAM'
  );

  OAUTH.grant_client_role(
    p_client_name => 'Car Racing Client',
    p_role_name   => 'oracle.dbtools.role.autorest.HR.DRIVER'
  );
  OAUTH.grant_client_role(
    p_client_name => 'Car Racing Client',
    p_role_name   => 'oracle.dbtools.role.autorest.HR.RACE'
  );
  OAUTH.grant_client_role(
    p_client_name => 'Car Racing Client',
    p_role_name   => 'oracle.dbtools.role.autorest.HR.DRIVER_RACE_MAP'
  );
  OAUTH.grant_client_role(
    p_client_name => 'Car Racing Client',
    p_role_name   => 'oracle.dbtools.role.autorest.HR.RACE_DV'
  );
  OAUTH.grant_client_role(
    p_client_name => 'Car Racing Client',
    p_role_name   => 'oracle.dbtools.role.autorest.HR.DRIVER_DV'
  );
  OAUTH.grant_client_role(
    p_client_name => 'Car Racing Client',
    p_role_name   => 'oracle.dbtools.role.autorest.HR.TEAM_DV'
  );

    COMMIT;
END;
/

That pl/sql block makes the tables and views available through ORDS AutoREST but to access them requires authorisation and Car Racing Client OAuth client is created with the necessary roles to do that. It is a client credentials OAuth client so to see the username and password generated for it just run this query:

SELECT client_id, client_secret 
FROM user_ords_clients 
WHERE name = 'Car Racing Client';


CLIENT_ID                   CLIENT_SECRET                   
--------------------------- --------------------------------
x9mADG7hKJNlj4LNITBJ3Q..    alRNwbRmezM2E0YDPTe6eA..

That CLIENT_ID and CLIENT_SECRET are the username and password to use to get an access token so that the tables and views can be accessed through ORDS. More on getting that access token later. For now, let’s look at the tables and views. Send a request to http://localhost:8080/ords/hr/open-api-catalog/ and list of services available will include:

   {
      "name": "RACE",
      "links": [
        {
          "rel": "canonical",
          "href": "http://localhost:8080/ords/hr/open-api-catalog/race/",
          "mediaType": "application/openapi+json"
        }
      ]
    },
    {
      "name": "TEAM",
      "links": [
        {
          "rel": "canonical",
          "href": "http://localhost:8080/ords/hr/open-api-catalog/team/",
          "mediaType": "application/openapi+json"
        }
      ]
    },
    {
      "name": "DRIVER",
      "links": [
        {
          "rel": "canonical",
          "href": "http://localhost:8080/ords/hr/open-api-catalog/driver/",
          "mediaType": "application/openapi+json"
        }
      ]
    },
    {
      "name": "RACE_DV",
      "links": [
        {
          "rel": "canonical",
          "href": "http://localhost:8080/ords/hr/open-api-catalog/race_dv/",
          "mediaType": "application/openapi+json"
        }
      ]
    },
    {
      "name": "TEAM_DV",
      "links": [
        {
          "rel": "canonical",
          "href": "http://localhost:8080/ords/hr/open-api-catalog/team_dv/",
          "mediaType": "application/openapi+json"
        }
      ]
    },
    {
      "name": "DRIVER_DV",
      "links": [
        {
          "rel": "canonical",
          "href": "http://localhost:8080/ords/hr/open-api-catalog/driver_dv/",
          "mediaType": "application/openapi+json"
        }
      ]
    },
    {
      "name": "DRIVER_RACE_MAP",
      "links": [
        {
          "rel": "canonical",
          "href": "http://localhost:8080/ords/hr/open-api-catalog/driver_race_map/",
          "mediaType": "application/openapi+json"
        }
      ]
    }

Follow those canonical links for an OpenAPI V3 description of the services available. For example, http://localhost:8080/ords/hr/open-api-catalog/driver_dv/ will show something like this…

Portion of the OpenAPI V3 document for driver_dv Duality View

That OpenAPI V3 document can be imported into your development or integration tools so you can get started with building clients to use the services straight away. As a convenience, ORDS Database Actions provides a graphical interface for the OpenAPI document.

In the AutoREST section of ORDS Database Actions REST Workshop the REST Enabled objects are listed.
Select OpenAPI View for a visual representation of the OpenAPI V3 document.

Try It Out

In that OpenAPI View there’s even the option to invoke that service directly from your browser.

Expand the operation and details of parameters, expected responses, etc. are shown included this “Try it out” button

Click on “Try it out” and an Execute button appears at the bottom of the Parameters section.

While in “Try it out” mode we can specific parameters and execute a request.

The parameters are optional so just press that Execute button to “Retrieve records from DRIVER_DV”. Note that this is the same as running the following from the command line:

curl -X 'GET' \
  'http://localhost:8080/ords/hr/driver_dv/' \
  -H 'accept: application/json'
The DRIVER_DV endpoint is protected so a request without a valid access token will be rejected.

The DRIVER_DV, in fact none of the Car-Racing example tables and duality views, can be accessed through ORDS without authorisation.

Client Access Token

Let’s get that access token. The PL/SQL block for REST Enabling the tables and views also created a client with the necessary roles for access them: Car Racing Client. We retrieved the Client ID and Client Secret ( aka username and password ) earlier. Those credentials are used to obtain an access token and that access token is sent with every request to ORDS. The database user can revoke that access token and even remove the OAuth client so it is more secure than always relying on usernames & passwords. ORDS supports a variety of OAuth types as well as JWT Profiles for OpenID Connect. Now back to getting that access token…

Command Line

Using the Client ID and Client Secret as username and password the access token can be retrieved using cURL:

curl \
--user x9mADG7hKJNlj4LNITBJ3Q..:alRNwbRmezM2E0YDPTe6eA.. \
--data 'grant_type=client_credentials' \
http://localhost:8080/ords/hr/oauth/token

{
 "access_token":"Fs5Lrsu1g6XAP1mGXMnUig",
 "token_type":"bearer",
 "expires_in":3600
}

Note that every time a new OAuth access token ( aka Bearer token ) is issued the previous one issued for that OAuth client is invalidated. You only need to request a new one when the current one is about, or has already, expired.

Using Database Actions

While in ORDS Database Actions REST Workshop and logged in as the HR user you can retrieve that information too by selecting Security -> OAuth Clients.

Head to OAuth Clients view for managing OAuth Clients
OAuth Clients can be managed from here or through ORDS OAuth package subprograms
Obtain a new token – note this value is different from the one previously retrieved and has invalidated it.

Try It Out Again!

Now that we have a valid bearer token Vu1AJ02QdF_13_kjjsSXRw let’s use that in the OpenAPI View to try executing requests again. Navigate back to the OpenAPI View for DRIVER_DV.

Notice that the padlocks are unlocked indicating that now bearer / access token is saved

The Authorize button will launch a dialog box to set the bearer / access token to be used when making requests. Other authorisation flows are supported but since we have a bearer token already we’ll just set that…

A number of authorisation types are available but we’ll use the BearerAuth.

Enter the BearerAuth value and press Authorize to have it saved in the browser session. Now all HTTP requests executed in the OpenAPI View will have this token until you go back this “Available authorizations” dialog and press Logout. Let’s now use that token. Press the “Close” button and now you’ll see that the padlocks now show as locked. Repeating the steps for executing a GET request to Retrieve records from DRIVER_DV we now see data returned…

Success ! The Bearer token is accepted by ORDS and data is returned.

Note that the corresponding cURL command is also displayed so this could also has been executed from the command line as:

curl -X 'GET' \
  'http://localhost:8080/ords/hr/driver_dv/' \
  -H 'accept: application/json' \
  -H 'Authorization: Bearer Vu1AJ02QdF_13_kjjsSXRw'

There are a lot more operations listed so experiment on what you can do through the OpenAPI View or from cURL. Note that Duality View JSON QBE parameter for querying uses the same syntax as SODA so it is not exactly the same as the Filter Object syntax used for AutoREST of relational tables and views.

How about trying to create a new driver record?

Try out the POST operation to insert a new Driver!
“Crash Test” is now the latest driver for team Alpine!

Build out the team

Using those Duality View services now let’s start creating new teams and drivers using batchload which is covered in the ORDS documentation. This allows you to perform multiple inserts of data that spans multiple tables. In this case the TEAM and DRIVER tables behind the TEAM_DV Duality View. The add-team.json file contains data for 7 more teams with 2 drivers each. Download it and use the file to populate those tables in one single request…

curl -i -X POST --data-binary @add-team.json \
  -H "Content-Type: application/json" \
  -H 'Authorization: Bearer Vu1AJ02QdF_13_kjjsSXRw' \
  'http://localhost:8080/ords/hr/team_dv/batchload

HTTP/1.1 200 OK
Content-Type: text/plain
Transfer-Encoding: chunked

#INFO Number of rows processed: 7
#INFO Number of rows in error: 0
#INFO Last row processed in final committed batch: 7
SUCCESS: Processed without errors
Loading data to TEAM_DV creates Team and Driver records.

Relational Table

So far the focus has been on Duality Views but as one can see from the above image, the underlying table(s) for a Duality View get updated. ORDS can also provide access to these relational tables and the above PL/SQL block provided we have already configured that.

Let’s populate a few race results using ORDS:

curl --request POST \
  --url http://localhost:8080/ords/hr/driver_race_map/ \
  --header 'Content-Type: application/json' \
  --header 'Authorization: Bearer Vu1AJ02QdF_13_kjjsSXRw' \
  --data '{"race_id"   : 202, "driver_id" : 119, "position" : 4}'

curl --request POST \
  --url http://localhost:8080/ords/hr/driver_race_map/ \
  --header 'Content-Type: application/json' \
  --header 'Authorization: Bearer Vu1AJ02QdF_13_kjjsSXRw' \
  --data '{"race_id"   : 202, "driver_id" : 115, "position" : 3}'

curl --request POST \
  --url http://localhost:8080/ords/hr/driver_race_map/ \
  --header 'Content-Type: application/json' \
  --header 'Authorization: Bearer Vu1AJ02QdF_13_kjjsSXRw' \
  --data '{"race_id"   : 202, "driver_id" : 103, "position" : 2}'

curl --request POST \
  --url http://localhost:8080/ords/hr/driver_race_map/ \
  --header 'Content-Type: application/json' \
  --header 'Authorization: Bearer Vu1AJ02QdF_13_kjjsSXRw' \
  --data '{"race_id"   : 202, "driver_id" : 101, "position" : 1}'

Thanks to the driver_race_map_trigger created when we ran the DualityViewTutorial.sql script we can now see the impact on the DRIVER table with Max Verstappen going from zero points to 25.

The insert to DRIVER_RACE_MAP resulted in a points increase on DRIVER

GraphQL

In this Car-Racing example the underlying tables for the Duality Views have been REST Enabled which also means they can be queried using GraphQL if ORDS is running on Oracle GraalVM Enterprise Edition with JS component installed. The advantage of GraphQL is that it allows you to define a query across the relational tables without having to define a Duality View but it would be a read only query. You do not get the create, update and delete capabilities of JSON-Relational Duality Views.

In this section we will take a look at a GraphQL query which can return the race date which is not currently included in the Driver Duality View. As a reminder, the Driver DV row looks like this:

{
  "_id":101,
  "name":"Max Verstappen",
  "points":25,
  "teamId":301,
  "team":"Red Bull",
  "race":[
    {
      "driverRaceMapId":8,
      "raceId":202,
      "name":"Saudi Arabian Grand Prix",
      "finalPosition":1
    }
  ]
}

It shows the data from DRIVER table, the team name from TEAM table, the RACE name and DRIVER_RACE_MAP finalPosition. However, it does not show the date of the race. Albeit in a slightly different structure, this GraphQL query will return the same data, plus the date of the race:

query Drivers {
  driver 
  { 
    driver_id 
    name 
    points 
    team_id
    team_team_id {
        name
    }
    driver_race_map_driver_id {
        driver_race_map_id
        race_id
        race_race_id {
            name
            race_date
        }
        position
    }
  }
}

It is slightly more nested and uses names reflecting the foreign key constraints between the tables but it does include race_date. Running the query through cURL is simply a POST to the GraphQL endpoint.

curl --location 'http://localhost:8080/ords/hr/_/graphql' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer vSs7J_ILrcynMaPWyVcQLA' \
--data '{"query":"query Drivers {\n  driver \n  { \n    driver_id \n    name \n    points \n    team_id\n    team_team_id {\n        name\n    }\n    driver_race_map_driver_id {\n        driver_race_map_id\n        race_id\n        race_race_id {\n            name\n            race_date\n        }\n        position\n    }\n  }\n}","variables":{}}'
Running GraphQL query through postman

Note that running the query through postman still requires setting the access bearer token. The added advantage of postman’s support for GraphQL is that it will use the same endpoint ( /ords/hr/_/graphql ) to automatically fetch the GraphQL schema so developing the query is guided. Here is the GraphQL query response for that query. It is quite long but it is informative to compare it with the corresponding DRIVER_DV Duality View structure.

{
    "data": {
        "driver": [
            {
                "driver_id": 107,
                "name": "Lando Norris",
                "points": 0,
                "team_id": 303,
                "team_team_id": [
                    {
                        "name": "McLaren"
                    }
                ],
                "driver_race_map_driver_id": []
            },
            {
                "driver_id": 108,
                "name": "Oscar Piastri",
                "points": 0,
                "team_id": 303,
                "team_team_id": [
                    {
                        "name": "McLaren"
                    }
                ],
                "driver_race_map_driver_id": []
            },
            {
                "driver_id": 109,
                "name": "Fernando Alonso",
                "points": 0,
                "team_id": 304,
                "team_team_id": [
                    {
                        "name": "Aston Martin"
                    }
                ],
                "driver_race_map_driver_id": []
            },
            {
                "driver_id": 110,
                "name": "Lance Stroll",
                "points": 0,
                "team_id": 304,
                "team_team_id": [
                    {
                        "name": "Aston Martin"
                    }
                ],
                "driver_race_map_driver_id": []
            },
            {
                "driver_id": 112,
                "name": "Daniel Ricciardo",
                "points": 0,
                "team_id": 305,
                "team_team_id": [
                    {
                        "name": "RB"
                    }
                ],
                "driver_race_map_driver_id": []
            },
            {
                "driver_id": 111,
                "name": "Yuki Tsunoda",
                "points": 0,
                "team_id": 305,
                "team_team_id": [
                    {
                        "name": "RB"
                    }
                ],
                "driver_race_map_driver_id": []
            },
            {
                "driver_id": 113,
                "name": "Nico Hulkenberg",
                "points": 0,
                "team_id": 306,
                "team_team_id": [
                    {
                        "name": "Haas"
                    }
                ],
                "driver_race_map_driver_id": []
            },
            {
                "driver_id": 114,
                "name": "Kevin Magnussen",
                "points": 0,
                "team_id": 306,
                "team_team_id": [
                    {
                        "name": "Haas"
                    }
                ],
                "driver_race_map_driver_id": []
            },
            {
                "driver_id": 115,
                "name": "Alexander Albon",
                "points": 15,
                "team_id": 307,
                "team_team_id": [
                    {
                        "name": "Williams"
                    }
                ],
                "driver_race_map_driver_id": [
                    {
                        "driver_race_map_id": 6,
                        "race_id": 202,
                        "race_race_id": [
                            {
                                "name": "Saudi Arabian Grand Prix",
                                "race_date": "2022-03-27T00:00:00Z"
                            }
                        ],
                        "position": 3
                    }
                ]
            },
            {
                "driver_id": 116,
                "name": "Logan Sargeant",
                "points": 0,
                "team_id": 307,
                "team_team_id": [
                    {
                        "name": "Williams"
                    }
                ],
                "driver_race_map_driver_id": []
            },
            {
                "driver_id": 118,
                "name": "Zhou Guanyu",
                "points": 0,
                "team_id": 308,
                "team_team_id": [
                    {
                        "name": "Kick Sauber"
                    }
                ],
                "driver_race_map_driver_id": []
            },
            {
                "driver_id": 117,
                "name": "Valtteri Bottas",
                "points": 0,
                "team_id": 308,
                "team_team_id": [
                    {
                        "name": "Kick Sauber"
                    }
                ],
                "driver_race_map_driver_id": []
            },
            {
                "driver_id": 119,
                "name": "Pierre Gasly",
                "points": 12,
                "team_id": 309,
                "team_team_id": [
                    {
                        "name": "Alpine"
                    }
                ],
                "driver_race_map_driver_id": [
                    {
                        "driver_race_map_id": 5,
                        "race_id": 202,
                        "race_race_id": [
                            {
                                "name": "Saudi Arabian Grand Prix",
                                "race_date": "2022-03-27T00:00:00Z"
                            }
                        ],
                        "position": 4
                    }
                ]
            },
            {
                "driver_id": 120,
                "name": "Esteban Ocon",
                "points": 0,
                "team_id": 309,
                "team_team_id": [
                    {
                        "name": "Alpine"
                    }
                ],
                "driver_race_map_driver_id": []
            },
            {
                "driver_id": 101,
                "name": "Max Verstappen",
                "points": 25,
                "team_id": 301,
                "team_team_id": [
                    {
                        "name": "Red Bull"
                    }
                ],
                "driver_race_map_driver_id": [
                    {
                        "driver_race_map_id": 8,
                        "race_id": 202,
                        "race_race_id": [
                            {
                                "name": "Saudi Arabian Grand Prix",
                                "race_date": "2022-03-27T00:00:00Z"
                            }
                        ],
                        "position": 1
                    }
                ]
            },
            {
                "driver_id": 102,
                "name": "Sergio Perez",
                "points": 0,
                "team_id": 301,
                "team_team_id": [
                    {
                        "name": "Red Bull"
                    }
                ],
                "driver_race_map_driver_id": []
            },
            {
                "driver_id": 105,
                "name": "George Russell",
                "points": 12,
                "team_id": 302,
                "team_team_id": [
                    {
                        "name": "Ferrari"
                    }
                ],
                "driver_race_map_driver_id": []
            },
            {
                "driver_id": 104,
                "name": "Carlos Sainz Jr",
                "points": 18,
                "team_id": 302,
                "team_team_id": [
                    {
                        "name": "Ferrari"
                    }
                ],
                "driver_race_map_driver_id": []
            },
            {
                "driver_id": 103,
                "name": "Charles Leclerc",
                "points": 43,
                "team_id": 2,
                "team_team_id": [
                    {
                        "name": "Mercedes"
                    }
                ],
                "driver_race_map_driver_id": [
                    {
                        "driver_race_map_id": 7,
                        "race_id": 202,
                        "race_race_id": [
                            {
                                "name": "Saudi Arabian Grand Prix",
                                "race_date": "2022-03-27T00:00:00Z"
                            }
                        ],
                        "position": 2
                    }
                ]
            },
            {
                "driver_id": 106,
                "name": "Lewis Hamilton",
                "points": 15,
                "team_id": 2,
                "team_team_id": [
                    {
                        "name": "Mercedes"
                    }
                ],
                "driver_race_map_driver_id": []
            }
        ]
    }
}

GraphQL requires ORDS to be running on GraalVM with the JS component installed but unlike Duality Views, you don’t require Oracle RDBMS 23 to use it. The Car-Racing relational tables could still be used in a 19c database for example. However, JSON-Relational Duality Views in Oracle RDBMS release 23 can do so much more.

Crossing the Finish Line

In this blog post we have taken the Car Racing example Duality Views and their corresponding tables, created using DualityViewTutorial.sql, and REST Enabled them for secure RESTful access through ORDS. Using batchload we loaded multiple team and driver records in one single request. We have interacted with the underlying tables and even performed dynamic cross table join queries using GraphQL. By introducing GraphQL into the conversation it makes the Duality View advantages much clearer.

JSON-Relational Duality Views provide a powerful bridge between the structured world of relational databases and the flexible world of JSON documents. Whether you’re building APIs, applications, or analytical tools, these views offer a unified approach to data manipulation on the fast track. 🏁🚀