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.
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.
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.
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 (
Connect to the pluggable database as
demouser. Thanks to the
With the module in place the next step is to create a call-specification, allowing
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!
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.
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 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.