Using the JavaScript fetch API in Oracle Database 23c Free-Developer Release

July 12, 2023 | 4 minute read
Text Size 100%:

To quote from Mozilla's Developer Network "... the Fetch API provides a JavaScript interface for accessing and manipulating parts of the protocol, such as requests and responses. It also provides a global fetch() method that provides an easy, logical way to fetch resources asynchronously across the network".

Oracle's Multilingual Engine (MLE) powered by GraalVM offers an implementation of the fetch API in JavaScript for Oracle Database 23c Free-Developer Release on Linux x86-64. Thanks to the availability of a fetch() call, lots of interesting use cases can be implemented in a convenient way. Invoking remote REST-APIs might well turn out to be the most popular use case. In this article you can learn how to use the fetch API to invoke a REST call to retrieve information from a REST endpoint using HTTPS. This particular REST endpoint is provided by a local instance of Oracle's RESTful Data Service, ORDS.

Network Access

Oracle Database requires administrators to explicitly allow network I/O based on fine-grained access control entries. You can read more about that topic in the Database Security Guide. Before you can make any network calls to remote resources a privileged account needs to enable these.

Creating a database user

The following snippet can be used to create a local user in the (pluggable) database. This user will be allowed to invoke the remote network API.

create user demouser identified by &password
default tablespace users
temporary tablespace temp
quota 100M on users;

grant db_developer_role to demouser;
grant execute on javascript to demouser;

Allow the demo user to perform network I/O

In the next step you need to allow demouser to perform network I/O. As per the introduction, this is done by appending an Access Control Entry (ACE) to an Access Control List (ACL).

BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host => '&targetHost',
        ace  =>  xs$ace_type(
            privilege_list => xs$name_list('http'),
            principal_name => 'DEMOUSER',
            principal_type => xs_acl.ptype_db
        )
    );
END;
/

The snippet will prompt you for the ORDS instance's host name. There is no need to provide a protocol or a port, simply add the fully qualified hostname (fqdn) like ords.example.com.

Fetching information

Connect to the pluggable database as demouser. Thanks to the DB_DEVELOPER_ROLE the account has the privileges to create JavaScript modules in the database.

Let there be JavaScript

You can use the following JavaScript module to perform a fetch via https. The REST endpoint does not require authentication - this was a conscious decision to keep the example simple. If needed, OAuth2 or other authentication methods can be added in ORDS and catered for in the JavaScript code.

create or replace mle module fetch_demo_module language javascript as

import "mle-js-fetch";

export async function fetchExample(url) {

    if (url === undefined || url.length < 0) {
        throw Error("please provide a URL in the form http://ords-server:port/ords/...");
    }

    const response = await fetch(
        url,
        {
            credentials: "include"
        }
    );

    if (! response.ok) {
        throw Error(`An error occurred: ${response.status}`);
    }

    const data = response.json();

    return data;
}
/

With the module in place the next step is to create a call-specification, allowing demouser to invoke the JavaScript code anywhere SQL and PL/SQL are used:

create or replace function fetch_example(
    p_url varchar2
) 
return json
as mle module fetch_demo_module
signature 'fetchExample(string)';
/

As soon as control returns to the prompt you can give the new functionality a go!

Testing

The JavaScript module either returns the JSON data from the ORDS instance, or it throws an exception if something has gone wrong. You can use the following select statement to invoke TLS-protected REST endpoint:

select fetch_example('&restendpoint');

Anyone who previously used UTL_HTTP to connect to TLS-protected resources will immediately be familiar with the resulting error message, ORA-29024 Certification validation Failure:

SQL> select fetch_example('&restendpoint');

Error starting at line : 1 in command -
select fetch_example('https://...')
Error report -
ORA-04161: TypeError: ORA-29024: Certificate validation failure
ORA-04171: at callUtlHttp (mle-js-fetch:2723:25)

In the past, that is, before Oracle Database 23c Free-Developer Release you had to create a wallet, grab the webservice's certificate, store it in the wallet and make the wallet available to the database user. Thankfully this is a thing of the past! Oracle Database 23c Free-Developer Release can make use of the operating system's certificate store making everyone's life a lot easier. The first step is to tell Oracle to make use of the system's certificate store before invoking the fetch call again:

begin
    utl_http.set_wallet('system:');
end;
/

select fetch_example('https://ords-server:8443/ords/schema/table/');

And voila! You are presented with lots of JSON representing the output of your API call.

Summary

The introduction of JavaScript as another option for server-side development allows developers to benefit from using this hugely popular language. Using the fetch() call it is possible to perform lots of tasks from within the database - you are not restricted to GET requests, others can be invoked as well.

Oracle Database 23c Free-Developer Release greatly simplifies accessing TLS-protected network endpoints. Rather than having to import the resource's certificate into a wallet and storing it locally the operating system's certificate store can be used instead.

Martin Bach

Martin is a product manager at Oracle helping customers in Europe and around the world address their IT related problems. He is most interested in cloud technology, DevOps and how these can be used best with Oracle technology.


Previous Post

Announcing WebLogic / Helidon Integration

Randy Stafford | 2 min read

Next Post


How to Migrate to Ampere on OKE with Heterogeneous Kubernetes Clusters

Jyothi Sreekumar | 18 min read