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 23ai 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. The target endpoint is provided by Oracle REST Data Services (ORDS). It is TLS-protected by a certificate signed by a well-known authority (in other words, not a self-signed certificate).
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 emily identified by &password
default tablespace users
temporary tablespace temp
quota 100M on users;
grant db_developer_role to emily;
grant execute on javascript to emily;
Allow emily to perform network I/O
In the next step you need to allow emily 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 => 'EMILY',
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 emily. Thanks to the DB_DEVELOPER_ROLE the account has the privileges to create JavaScript modules in the database, and you granted execute on javascript in a previous step as well, another prerequisite for In-Database JavaScript.
Let there be JavaScript
You can use the following JavaScript module to perform a fetch via https. The REST endpoint in the example does not require authentication – this was a conscious decision to keep it simple. Production applications are of course secured, but it’s not hard to add OAuth2 or other authentication methods to the JavaScript code.
create or replace mle module fetch_demo_module language javascript as
import "mle-js-fetch";
/**
* A small example function explaining how to call the JavaScript fetch() API in
* Multilingual Engine for Oracle Database 23ai. Provided a valid URL the
* function accesses an ORDS endpoint and returns the JSON result, if any.
*
* param {string} url - the REST endpoint to be accessed
* returns {object} the JSON result obtained from interrogating ORDS
export async function fetchExample(url) {
if (url === undefined || url.length < 0) {
throw Error("please provide a URL in the form https://ords-server:port/ords/...");
}
const response = await fetch(url);
if (! response.ok) {
throw new Error(`An error occurred: ${response.status}`);
}
const data = await response.json();
return data;
}
/
With the module in place the next step is to create a call-specification, allowing emily 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';
/
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');
Provided the REST endpoint TLS certificate was created by a trusted source, you will get a lot of JSON back. In earlier releases you would have received an 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 23ai you had to create a wallet, grab the webservice’s root certificate, store it in the wallet and make the wallet available to the database Thankfully this is a thing of the past, at least in most cases. Oracle Database 23ai can make use of the operating system’s certificate store making everyone’s life a lot easier. This is done implicitly for you, but it can be forced in cases where you get an unexpected certificate error you know you shouldn’t see:
begin
utl_http.set_wallet('system:');
end;
/
Under the covers Oracle Database 23ai uses the operating system’s certificate store to validate the target resource’s certificate chain. If the operating system trusts it, so does the database. Self-signed certificates are a different story, they still must be provided in a wallet, just as with Oracle Database 19c.
Summary
The introduction of JavaScript as part of Oracle Database 23ai on Linux x86-64 adds another option for server-side development and 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 23ai on Linux x86-64 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.