Call External HTTPS APIs with Oracle Wallet

This guide is based on the original server-side setup article by my colleague and mentor, Sajjad Hanifa. You can read his full original post here: How to Call External HTTPS APIs with Oracle Wallet & Certificates. He handles the Oracle wallet and server configuration at SH Software Solutions. I work with this setup on the APEX side almost every day, so I wanted to put together a version that connects both worlds.

Security review, 14 October 2026: The examples below replace the earlier world-writable wallet directory, password-in-a-parameter-table pattern, and commands that hid setup errors. This revision is based on Oracle’s documentation; the revised commands and PL/SQL have not been executed as a new end-to-end test. Adapt and verify them in an authorized non-production environment with your DBA.

A successful HTTPS call from Oracle APEX depends on several separate checks: the database can reach the approved host, its calling schema has network access, TLS can validate the server’s certificate chain and hostname, and the remote API accepts the request. A browser reaching the URL does not prove the database server can reach it.

Scope and prerequisites

This walkthrough targets a self-managed Oracle Database 19c environment with a DBA-managed filesystem wallet and direct UTL_HTTP calls. It covers server-certificate trust, not mutual TLS or API authentication. Autonomous Database and managed APEX services have their own network and wallet workflows; do not assume you can use these filesystem commands there.

• Agree on an approved HTTPS hostname, port, and harmless health-check URL. All api.example.com values below are placeholders, not a working service.
• Identify the database OS account, container mount if applicable, calling database schema, database patch level, and location of orapki.
• Have the API owner or DBA provide the appropriate verified CA trust certificates. Do not download an unverified certificate and trust it merely to make an error disappear.
• Use a dedicated test wallet directory. Do not overwrite an existing wallet or change a production ACL to try the example.

Step 1: Create and protect the wallet

Have the DBA choose a directory owned by the OS account that runs the database. For a directory owned and used by that same account, a restrictive starting point is 700 on the directory and 600 on wallet files. The database account also needs traversal permission on the parent directories. If your deployment uses another owner or a restricted group, have the administrator configure that explicitly. Never use chmod -R 777 to solve a wallet access problem.

In Docker, the host-side volume path and the path visible inside the database container may differ. Oracle reads the container-side path. Check the actual container UID/GID and mount ownership rather than assuming that a host username or path is equivalent.

# Example only: run as the database OS owner after the DBA confirms this path.
# Stop if this directory already exists; inspect the existing wallet instead.
(
set -eu
WALLET_PATH="/opt/oracle/oradata/wallet/apex_api_demo"
umask 077
mkdir -m 700 "$WALLET_PATH"

# The subshell stops on command failure, including an existing directory.
# Omitting -pwd lets orapki prompt instead of putting a secret in shell history.
orapki wallet create -wallet "$WALLET_PATH" -auto_login

# Replace the certificate path with a verified CA certificate supplied by your DBA.
orapki wallet add -wallet "$WALLET_PATH" -trusted_cert \
  -cert /approved/certificates/api-root-ca.pem

orapki wallet display -wallet "$WALLET_PATH"
chmod 600 "$WALLET_PATH/ewallet.p12" "$WALLET_PATH/cwallet.sso"
ls -ld "$WALLET_PATH"
ls -l "$WALLET_PATH/ewallet.p12" "$WALLET_PATH/cwallet.sso"
)

Verify the displayed trusted certificates against the approved CA chain and import additional verified CA certificates only where your chain requires them. TLS trust is not simply a requirement to place the endpoint’s leaf certificate in a directory. Certificate expiry, missing intermediate certificates and a hostname mismatch can all cause failures.

The password-protected ewallet.p12 and auto-login cwallet.sso must both be protected. Auto-login removes the runtime password prompt; it does not make a wallet harmless or public. Wallets can contain credentials or private keys. A normal auto-login wallet is portable; -auto_login_local binds access to the creating host and OS user, which may conflict with container replacement, failover or shared-storage requirements. Choose with your DBA. See Oracle’s orapki wallet guide and auto-login security guidance.

Step 2: Grant only the required network access

A privileged administrator must review existing ACLs and the actual runtime principal before running a grant. APP_SCHEMA below is a placeholder for the schema that invokes UTL_HTTP, not the APEX username. A definer-rights wrapper can change which database principal matters.

-- Administrator-reviewed example for one host and port, not all outbound access.
BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host       => 'api.example.com',
    lower_port => 443,
    upper_port => 443,
    ace        => xs$ace_type(
      privilege_list => xs$name_list('http'),
      principal_name => 'APP_SCHEMA',
      principal_type => xs_acl.ptype_db
    )
  );
END;
/

This example grants the protocol-specific http privilege for the approved host and port 443. Oracle’s 19c examples use HTTP privileges with port-scoped ACEs. Have the DBA verify the effective ACLs and test access on the installed patch level. Do not broaden access to connect, wildcard hosts or additional ports merely to clear an error. Do not add resolve, wallet credential privileges or proxy access without a specific need. Trusting a server certificate is separate from permission to use a wallet’s stored passwords or client certificates. See Oracle’s ACL configuration guide and package reference.

Step 3: Keep configuration separate from secrets

An endpoint URL and wallet path can be stored in a controlled configuration table or package. A generic parameter table should not become a plaintext store for the wallet password. For the protected auto-login wallet in this example, pass NULL as the runtime wallet password. Keep the wallet’s management password in your organization’s approved secret store.

If the API requires an API key, OAuth token, Basic authentication or a client certificate, configure that separately using the supported credential mechanism for your integration. APEX Web Credentials are relevant to supported APEX web-service integrations; they do not automatically authenticate an arbitrary UTL_HTTP call. Never place secrets in page items, source control, public examples or diagnostic output.

Step 4: Check the HTTP result without hiding failures

Run this adapted example in a fresh, controlled SQL session as the intended calling schema. Existing session settings can cause 4xx/5xx responses to raise before the custom status check; either path must remain a failure. It uses a private request context, checks the HTTP status, closes the response and destroys the context. It prints no response body, credentials or authorization headers. Replace both placeholders first. This is a status-only diagnostic, not a complete REST client or a test of the API’s business result.

SET SERVEROUTPUT ON

DECLARE
  l_context       UTL_HTTP.REQUEST_CONTEXT_KEY;
  l_request       UTL_HTTP.REQ;
  l_response      UTL_HTTP.RESP;
  l_response_open BOOLEAN := FALSE;
  l_request_open  BOOLEAN := FALSE;
  l_status        PLS_INTEGER;
BEGIN
  l_context := UTL_HTTP.CREATE_REQUEST_CONTEXT(
    wallet_path     => 'file:/opt/oracle/oradata/wallet/apex_api_demo',
    wallet_password => NULL,
    enable_cookies  => FALSE
  );
  l_request := UTL_HTTP.BEGIN_REQUEST(
    url             => 'https://api.example.com/health',
    method          => 'GET',
    request_context => l_context
  );
  l_request_open := TRUE;
  UTL_HTTP.SET_FOLLOW_REDIRECT(l_request, 0);
  -- GET_RESPONSE ends the request even if it raises.
  l_request_open := FALSE;
  l_response := UTL_HTTP.GET_RESPONSE(l_request);
  l_response_open := TRUE;
  l_status := l_response.status_code;

  UTL_HTTP.END_RESPONSE(l_response);
  l_response_open := FALSE;
  UTL_HTTP.DESTROY_REQUEST_CONTEXT(l_context);
  l_context := NULL;

  IF l_status < 200 OR l_status >= 300 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Unexpected HTTP status: ' || l_status);
  END IF;
  DBMS_OUTPUT.PUT_LINE('HTTP status: ' || l_status);
EXCEPTION
  WHEN OTHERS THEN
    -- Cleanup failures must not replace the original request failure.
    IF l_response_open THEN
      BEGIN
        UTL_HTTP.END_RESPONSE(l_response);
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('Response cleanup failed; inspect server diagnostics.');
      END;
    END IF;
    IF l_request_open THEN
      BEGIN
        UTL_HTTP.END_REQUEST(l_request);
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('Request cleanup failed; inspect server diagnostics.');
      END;
    END IF;
    IF l_context IS NOT NULL THEN
      BEGIN
        UTL_HTTP.DESTROY_REQUEST_CONTEXT(l_context);
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('Context cleanup failed; inspect server diagnostics.');
      END;
    END IF;
    RAISE;
END;
/

The outer RAISE preserves failure for the caller. The nested exception handlers are limited to cleanup, and report that cleanup failed. Redirects are deliberately disabled: a 3xx result needs review before the database follows another destination. A 2xx response establishes a successful HTTP status only; validate the expected payload separately before treating a business operation as complete. Oracle documents request contexts, HTTP status and cleanup in the UTL_HTTP reference and wallet access guide.

Troubleshooting and APEX integration checks

• Network access denied: Check the calling schema, exact destination, port and existing ACL precedence. Do not grant every schema access to every host.
• Wallet cannot be opened: Check the path as seen by the database process, mount ownership, directory traversal and wallet-file read permissions. Do not make the directory world writable.
• Certificate validation failure: Check the approved CA chain, certificate validity, requested hostname and database TLS support. Do not disable verification.
• 401 or 403: Review API authentication and authorization. Successfully loading a wallet does not provide an API credential.
• 3xx or an unexpected successful payload: Verify that you reached the intended endpoint rather than a login page or redirect. Do not log sensitive response bodies.
• SQL succeeds but APEX fails: Verify the runtime principal and package rights. A successful privileged DBA test does not prove the application schema has access.

Before moving a call into an APEX process or scheduled job, add appropriate timeout and response-size handling, input validation, sanitized logging and API-specific status/payload checks. Restrict outbound destinations on the server rather than accepting an arbitrary URL from a page item. Avoid automatic retries for operations that could create duplicate transactions. The SQL diagnostic above is not a production-ready wrapper.

The useful sequence is: confirm the environment, protect the wallet, verify certificate trust, grant narrowly scoped access, test as the actual caller, then integrate with application-specific error handling. Keep the server setup and the APEX caller’s responsibilities visible so that a connection problem can be diagnosed without weakening security.

Original server-side article by Sajjad Hanifa: apexnote.de.

Hassan Raza
Oracle ACE Apprentice, Pakistan (author signature at original publication)