Oracle DB: How to Find Public IP for REST Calls

The Problem: Unseen Outbound IPs

Many applications today leverage Oracle Database's built-in capabilities to make outbound REST calls. This is commonly achieved using the UTL_HTTP package or APEX's MAKE_REST_REQUEST function. While convenient for integrating with external services, this often leads to a critical security and connectivity challenge: identifying the public IP address from which these database-initiated calls originate.

This becomes a showstopper when external services, like a third-party API provider, implement IP-based access control. They whitelist specific IP addresses for security, meaning your database's internal IP won't suffice. You need to provide the *public* IP address that traverses your firewall and reaches their servers. Many developers find themselves stuck when asked for this IP, as the database operates within a private network, and its public-facing IP isn't immediately obvious.

Solution 1: Using ipify via UTL_HTTP

A straightforward method to determine this public IP involves making an HTTP request to a service that simply returns the caller's IP address. ipify.org is a popular and reliable choice for this purpose. We can use Oracle's UTL_HTTP package to interact with ipify.

Here's the PL/SQL code to achieve this:

SET SERVEROUTPUT ON;

DECLARE
  l_response CLOB;
BEGIN
  -- Initialize the HTTP request
  UTL_HTTP.SET_TRANSFER_TIMEOUT(20); -- Set a reasonable timeout (in seconds)
  l_response := UTL_HTTP.request('https://api.ipify.org');

  -- Output the response, which is the public IP address
  DBMS_OUTPUT.PUT_LINE('Public IP Address: ' || l_response);

EXCEPTION
  WHEN UTL_HTTP.request_failed THEN
    DBMS_OUTPUT.PUT_LINE('HTTP Request Failed: ' || UTL_HTTP.get_detailed_sqlerrm);
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
END;
/

Explanation:

• SET SERVEROUTPUT ON;: Enables the display of output from DBMS_OUTPUT.PUT_LINE.
• UTL_HTTP.SET_TRANSFER_TIMEOUT(20);: Sets a timeout for the HTTP request to prevent indefinite hangs.
• UTL_HTTP.request('https://api.ipify.org');: This is the core function. It sends an HTTP GET request to the specified URL and returns the response body as a CLOB. For ipify.org, the response body *is* the public IP address.
• DBMS_OUTPUT.PUT_LINE(...): Displays the retrieved IP address.
• Exception Handling: Basic error handling is included to catch common HTTP request failures and other potential issues.

Prerequisites:

• The Oracle database user executing this code must have the EXECUTE privilege on the UTL_HTTP package.
• The database server must have network connectivity to api.ipify.org on port 443 (HTTPS). This often requires configuration in the database's firewall or network ACLs.
• Oracle Wallet configuration might be necessary for SSL/TLS connections if your Oracle version requires it for HTTPS.

Solution 2: Using APEX MAKE_REST_REQUEST

If you are primarily using Oracle APEX and its declarative or programmatic REST call features, you can achieve the same result using the APEX_WEB_SERVICE.MAKE_REST_REQUEST function. This function is a higher-level wrapper around UTL_HTTP, often simplifying the process.

Here's how you would do it within an APEX context (e.g., in a PL/SQL region, dynamic action, or stored procedure called by APEX):

DECLARE
  l_response CLOB;
  v_public_ip VARCHAR2(100);
BEGIN
  -- Make the REST call to ipify
  l_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
    p_url    => 'https://api.ipify.org',
    p_http_method => 'GET'
  );

  -- The response is directly in l_response
  v_public_ip := l_response;
  DBMS_OUTPUT.PUT_LINE('Public IP Address (APEX): ' || v_public_ip);

  -- You can now use v_public_ip for your firewall rules or logging

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error calling ipify via APEX: ' || SQLERRM);
END;
/

Explanation:

• APEX_WEB_SERVICE.MAKE_REST_REQUEST: This function simplifies making HTTP requests. You specify the URL and the HTTP method.
• The response body is directly captured in the l_response CLOB variable.

Prerequisites for APEX:

• APEX must be installed and configured.
• The database user associated with the APEX application (or the user running the PL/SQL code) needs appropriate privileges to execute APEX_WEB_SERVICE.
• Network connectivity from the database server to api.ipify.org must be established, similar to the UTL_HTTP requirement.
• If your APEX environment relies on specific network configurations or proxies for outbound HTTP(S) calls, those must be correctly set up.

Beyond ipify: Network Configuration and Verification

While using a service like ipify is effective for querying the IP, it's crucial to understand the underlying network configuration. The IP address returned is the one your database server uses to exit your network. This might be a dedicated NAT gateway IP, a load balancer IP, or the IP of a proxy server.

Firewall Rules: For external services that require IP whitelisting, you will need to coordinate with your network administrators. They will identify the correct public IP address used by the database server and add it to the allowlist of the target service.

Troubleshooting Connectivity: If the calls fail after configuring the firewall, double-check:

• DNS Resolution: Can the database server resolve the hostname of the external API?
• Port Access: Is the necessary port (usually 80 for HTTP or 443 for HTTPS) open on the firewall for outbound traffic from the database server's public IP?
• Proxy Settings: If your organization uses an HTTP/S proxy for outbound internet access, UTL_HTTP and APEX_WEB_SERVICE might need to be configured to use it. This typically involves setting parameters like UTL_HTTP.set_proxy.

Security Considerations: Always ensure that the services you are calling are legitimate and that your database is not being used to launch unsolicited or malicious requests. Limiting outbound call capabilities to only necessary hosts and ports is a fundamental security practice.

By understanding and implementing these methods, you can reliably determine and manage the public IP addresses used by your Oracle database for outbound REST communications, ensuring seamless integration with external services.