Both network and file I/O are a security-sensitive affair. Therefore you cannot simply write to a file, or open a network connection in Oracle Database without doing some setup work up front. Developers wishing to perform network I/O via UTL_HTTP for example must define fine-grained access control settings. Or rather, ask the administrator to perform this operation for them. For network I/O this includes the definition of an Access Control Entry (ACE) by calling DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE().

In case the web-resource to be consumed requires the use of Transport Layer Security (TLS) the addition of the web-resource’s certificate to an Oracle wallet was needed all the way up to Oracle Database 23ai. That is, unless you are on Autonomous Database where the friendly engineers provide lots of certificates in the wallet.

Oracle Database 23ai Free does away with the requirement to store the certificate in a wallet. Provided the web-service you are connecting to is “trusted” by the operating system, the connection process is greatly simplified. How can you tell? A low-tech solution is to point cURL to the URI you want to connect to. If it doesn’t complain about an unknown certificate, you are most likely fine. Oh and don’t specify the --insecure option, that’s cheating.

You need to be aware of all security and compliance related restrictions for your database. Network I/O is a very sensitive matter and Oracle disables it by default for good reason. Always consult the security, compliance or any other team responsible for that area before deciding on performing network I/O with the database. Have the solution vetted, verified, certified and rubber stamped by the experts in your organisation before even thinking of deploying it. It’s better to be safe than sorry.

Setup

With the warnings out of the way, let’s give the new way of doing things in Oracle Database 23ai a try. I’d like to use api.ipify.org to get my current IP address in JSON format. I haven’t found a simpler, more accessible web service to use, it’s not super complex but should be sufficient to demonstrate the concept.

This is what it looks like when you invoke the web service via curl on the command line:

$ curl 'https://api.ipify.org/?format=json'
    {"ip":"1.2.3.4"}

No complaint about the certificate from cURL, Oracle should allow me to access that URL as well, using the operating system’s certificate store. I had to allow the developer (demouser) to perform network I/O against this web service first. As per the introduction, this is done by appending an Access Control Entry (ACE) to an Access Control List (ACL). For example:

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

This anonymous PL/SQL block, to be executed by a privileged account, grants demouser the right to issue HTTP requests against api.ipify.org.

Example using UTL_HTTP

The following example demonstrates how to perform a simple lookup using UTL_HTTP.REQUEST to fetch my IP address:

SQL> select
      2    json_serialize(
      3      utl_http.request('https://api.ipify.org/?format=json')
      4      pretty
      5    ) as my_ip_address;
    
    MY_IP_ADDRESS    
    ________________ 
    {
      "ip":"1.2.3.4"
    }

I previously established that my operating system trusts api.ipify.org, so there wasn’t an issue reading from it. Note that you can set the wallet_path to system: (including the colon) in most functions/procedures in UTL_HTTP if you want, although it’s not needed.

Example using In-Database JavaScript

Oracle Database 23ai Free features Multilingual Engine (MLE), powered by GraalVM. This new feature allows developers to use JavaScript as the database’s third server-side programming language, alongside PL/SQL and Java.

The JavaScript fetch API provides a very popular way for reading information from remote sources. The above call to UTL_HTTP.REQUEST can be written as follows in JavaScript:

create or replace function fetch_me_if_you_can
    return JSON
    as mle language javascript
    q'~
        await import ("mle-js-fetch");
        const response = await fetch('https://api.ipify.org/?format=json');
        if (response.ok) {
            const data = await response.json();
            return data;
        } else {
            throw new Error(
              'unexpected network error trying to query the web server'
            );
        }
    ~';
    /

The above code snippet creates a new function in my schema that I can query next:

SQL> select
      2    json_serialize(
      3      fetch_me_if_you_can
      4      pretty
      5    ) as my_ip_address;
    
    MY_IP_ADDRESS    
    ________________ 
    {
      "ip":"1.2.3.4"
    }

It’s as simple as that.

Summary

The use of the operating system’s certificate store is a great productivity booster, at least in my opinion. Instead of having to create a wallet to access TLS-protected web resources, you can rely on the operating system’s certificate store instead.