Reaching Out From the Database
Modern applications rarely live in isolation. Your Oracle database may need to call external REST APIs to validate addresses, process payments, send SMS messages, or integrate with cloud services. While UTL_HTTP has been available for decades, the APEX_WEB_SERVICE package provides a much simpler interface for making HTTP requests from PL/SQL.
A Simple GET Request
DECLARE
l_response CLOB;
BEGIN
l_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
p_url => 'https://api.exchangerate-api.com/v4/latest/USD',
p_http_method => 'GET'
);
APEX_JSON.PARSE(l_response);
DBMS_OUTPUT.PUT_LINE('EUR rate: ' ||
APEX_JSON.GET_VARCHAR2(p_path => 'rates.EUR'));
END;
/POST Requests With JSON Bodies
DECLARE
l_request_body CLOB;
l_response CLOB;
BEGIN
APEX_JSON.INITIALIZE_CLOB_OUTPUT;
APEX_JSON.OPEN_OBJECT;
APEX_JSON.WRITE('customer_id', 12345);
APEX_JSON.WRITE('amount', 99.99);
APEX_JSON.WRITE('currency', 'USD');
APEX_JSON.CLOSE_OBJECT;
l_request_body := APEX_JSON.GET_CLOB_OUTPUT;
APEX_JSON.FREE_OUTPUT;
l_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
p_url => 'https://api.payment-processor.com/v1/charges',
p_http_method => 'POST',
p_body => l_request_body,
p_parm_name => APEX_UTIL.STRING_TO_TABLE('Content-Type'),
p_parm_value => APEX_UTIL.STRING_TO_TABLE('application/json')
);
IF APEX_WEB_SERVICE.G_STATUS_CODE = 200 THEN
APEX_JSON.PARSE(l_response);
DBMS_OUTPUT.PUT_LINE('Charge ID: ' ||
APEX_JSON.GET_VARCHAR2(p_path => 'charge_id'));
ELSE
DBMS_OUTPUT.PUT_LINE('Error: HTTP ' || APEX_WEB_SERVICE.G_STATUS_CODE);
END IF;
END;
/Authentication
APEX_WEB_SERVICE supports common authentication methods. For API key authentication, pass the key as a header. For Basic authentication, use the p_username and p_password parameters. For OAuth2 client credentials, use APEX_WEB_SERVICE.OAUTH_AUTHENTICATE to obtain a token first, then pass it as a Bearer token header in subsequent requests.
Network Configuration
Oracle’s database security model requires that outbound network access be explicitly granted. The DBA must create an Access Control List (ACL) that permits the database user to connect to external hosts using DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE. Without this, all HTTP requests will fail with a network access denied error.
Error Handling
Always check APEX_WEB_SERVICE.G_STATUS_CODE after a request. Network timeouts, DNS failures, and HTTP errors are all possible. Wrap your calls in exception handlers and log both the status code and response body for troubleshooting. For production code, implement retry logic with exponential backoff for transient failures.