How to Call HTTPS Endpoints with UTL_HTTP.SET_WALLET

HTTPS servers prove who they are with certificates, and the database must trust the authorities that signed them. UTL_HTTP.SET_WALLET tells it where to find those trusted certificates: an Oracle wallet, or the certificate store of the operating system, which needs no wallet at all.

Code for This Guide

The main examples are in the examples/pkg-files-network folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

utl_http.set_wallet('system:');                      -- the operating system's certificates
utl_http.set_wallet('file:/path/to/wallet', password);

The setting lasts for the session. The host also needs an access control entry with the http privilege.

Call an HTTPS Site

Example:

declare
  v_req  utl_http.req;
  v_resp utl_http.resp;
  v_text varchar2(32767);
begin
  utl_http.set_wallet('system:');              -- the operating system's certificate store
  v_req := utl_http.begin_request('https://example.com/');
  v_resp := utl_http.get_response(v_req);
  utl_http.read_text(v_resp, v_text);
  utl_http.end_response(v_resp);
  dbms_output.put_line(v_resp.status_code || ' ' || v_resp.reason_phrase || ', title: '
                       || regexp_substr(v_text, '<title>(.*)</title>', 1, 1, null, 1));
end;
/

Output:

200 OK, title: Example Domain

PL/SQL procedure successfully completed.

With 'system:', the database trusts the same authorities as the server's other programs, and the request to example.com succeeds with 200 OK.

Without SET_WALLET

Example:

-- no SET_WALLET in this session
select regexp_substr(utl_http.request('https://example.com/'),
                     '<title>(.*)</title>', 1, 1, null, 1) as title;

Output:

TITLE
_________________
Example Domain

On this test database, the call worked in a new session without SET_WALLET too, so Oracle found the certificates on its own. Don't rely on that in code that runs on other databases: set the wallet explicitly so the behavior is the same everywhere. When the certificates cannot be found, the call fails with ORA-29024, certificate validation failure.

Things to Know

• Use a file wallet created with orapki when the server's certificate is signed by a private authority.
• Client certificates for mutual TLS are stored in the wallet too.
• Access to a wallet's passwords and certificates can be controlled per user with APPEND_WALLET_ACE.

Related Guides

• How to Read HTTP Responses with UTL_HTTP.BEGIN_REQUEST
• How to Send a POST Body with UTL_HTTP.WRITE_TEXT

Conclusion

UTL_HTTP.SET_WALLET points the session to the certificates HTTPS needs. 'system:' uses the operating system's store with nothing to maintain, and a file wallet covers private authorities and client certificates.