Before jumping into the example, it’s worth highlighting an important aspect: security. Therefore you cannot simply write to a file or open a network connection in Oracle AI Database without doing some setup work Developers wishing to perform network I/O via UTL_HTTP for example must define fine-grained access control settings. More realistically, they need to ask an administrator to do this. 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.

UPDATE 260410: this feature is also available for Oracle Database 19c. Refer to my earlier post for details.

Oracle Database 23ai Free and later, including AI Database 26ai do 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. As you read earlier, 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. To demonstrate this feature, I’ll call a simple public API (api.ipify.org) that returns my IP address in JSON format. I haven’t found a simpler web service to use. It’s not complex, but sufficient to demonstrate the concept of communicating with the outside world.

UPDATE 240108: in case you are using a proxy server to access web-APIs (which is quite likely), you can have a look at another post describing how to use UTL_HTTP and the fetch() API behind a proxy.

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"}

Since curl trusts the certificate via the OS store, Oracle should be able to do the same when using the OS certificate store. 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 mentioned earlier, this is done by appending an Access Control Entry (ACE) to an Access Control List (ACL). If you want to create the ACE you need SYSDBA privileges. Here is an 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, grants demouser the right to issue HTTP (including HTTPS) requests against api.ipify.org.

UTL_HTTP Example

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.

JavaScript Example

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.

UPDATE 240924: the code samples have been updated to use inline JavaScript, first available with Oracle Database Free 23.5.

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
{{
    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: ${response.status}`
        );
    }
}};
/

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"
}

Summary

Oracle Database 23ai first introduced the ability to use the operating system’s certificate store instead of an Oracle wallet for HTTPS connections. This simplifies configuration by eliminating the need to create and maintain wallets for trusted certificates. After configuring the appropriate network access controls (ACLs), database code can securely call external HTTPS services using certificates already trusted by the OS.

This approach reduces administrative overhead and is particularly useful for development and lightweight integrations, while still requiring careful attention to security and privilege management.