
Oracle Database is more than a data store. With PL/SQL, it can execute complex business logic and even communicate with external systems over HTTP. Many organizations need their database to call web services directly, for example to validate addresses, fetch exchange rates, push updates to a CRM, or send notifications when records change. Oracle provides built-in packages that make this possible. This guide explains how to configure the database and call both REST and SOAP web services from PL/SQL safely and efficiently.
Database-Driven Application Development With AAMAX.CO
Connecting databases with modern web services requires a careful balance of performance, security, and maintainability. AAMAX.CO is a full service digital marketing company providing web development, digital marketing, and SEO services worldwide. Their developers design data-driven applications that integrate back-end databases with external APIs and user-friendly interfaces. Through their web application development services, businesses can modernize legacy systems and build connected platforms that grow with their needs.
Built-In Options for HTTP Calls
Oracle offers several tools for calling web services from PL/SQL:
- UTL_HTTP: A low-level package available in all Oracle editions that sends HTTP and HTTPS requests and reads responses.
- APEX_WEB_SERVICE: A higher-level package included with Oracle APEX that simplifies REST and SOAP calls, headers, and authentication.
- DBMS_CLOUD.SEND_REQUEST: Available in Oracle Autonomous Database for calling cloud services with stored credentials.
Step 1: Configure a Network Access Control List
For security, Oracle blocks outbound network access by default. A DBA must grant the schema permission to connect to the target host using DBMS_NETWORK_ACL_ADMIN. In Oracle 12c and later, the APPEND_HOST_ACE procedure is used:
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'api.example.com',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => 'APP_SCHEMA',
principal_type => xs_acl.ptype_db));
END;Grant access only to the specific hosts your application needs.
Step 2: Set Up an Oracle Wallet for HTTPS
To call HTTPS endpoints, Oracle needs the certificate authority certificates of the target service stored in an Oracle Wallet. Use the orapki utility to create a wallet and import the root and intermediate certificates. In PL/SQL, reference the wallet with UTL_HTTP.SET_WALLET before making requests. Recent Oracle versions and Autonomous Database may include common certificates by default, simplifying this step.
Step 3: Call a REST Service With UTL_HTTP
A basic GET request with UTL_HTTP follows this pattern:
DECLARE
l_req UTL_HTTP.req;
l_resp UTL_HTTP.resp;
l_text VARCHAR2(32767);
l_body CLOB;
BEGIN
UTL_HTTP.SET_WALLET('file:/u01/wallet', NULL);
l_req := UTL_HTTP.BEGIN_REQUEST('https://api.example.com/rates', 'GET');
UTL_HTTP.SET_HEADER(l_req, 'Authorization', 'Bearer ' || l_token);
l_resp := UTL_HTTP.GET_RESPONSE(l_req);
BEGIN
LOOP
UTL_HTTP.READ_TEXT(l_resp, l_text, 32767);
l_body := l_body || l_text;
END LOOP;
EXCEPTION WHEN UTL_HTTP.END_OF_BODY THEN
UTL_HTTP.END_RESPONSE(l_resp);
END;
END;Always end the response to release the connection, including in error handlers.
Step 4: Send a POST Request
For POST requests, set the method to POST, add Content-Type and Content-Length headers, and write the body with UTL_HTTP.WRITE_TEXT before requesting the response. For larger payloads, write the body in chunks and consider setting transfer encoding to chunked.
Step 5: Simplify Calls With APEX_WEB_SERVICE
If APEX is installed, APEX_WEB_SERVICE greatly reduces boilerplate:
l_body := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
p_url => 'https://api.example.com/rates',
p_http_method => 'GET',
p_wallet_path => 'file:/u01/wallet');You can set request headers through the g_request_headers collection and check the status code using APEX_WEB_SERVICE.G_STATUS_CODE.
Step 6: Parse JSON Responses
Oracle 12.2 and later include native JSON support. Use JSON_TABLE to convert JSON arrays into relational rows, JSON_VALUE to extract single values, or the PL/SQL object types JSON_OBJECT_T and JSON_ARRAY_T for programmatic access. APEX_JSON is another option in environments with APEX installed.
Step 7: Calling SOAP Services
To call a SOAP service, build the XML envelope as a CLOB, send it via POST with a Content-Type of text/xml and the appropriate SOAPAction header, then parse the XML response using XMLTYPE and XMLTABLE. APEX_WEB_SERVICE.MAKE_REQUEST also supports SOAP envelopes and returns the response as XMLTYPE.
Step 8: Handle Errors and Timeouts
Use UTL_HTTP.SET_TRANSFER_TIMEOUT to prevent sessions from waiting indefinitely. Check the response status code before processing the body, and handle exceptions such as UTL_HTTP.REQUEST_FAILED and TRANSFER_TIMEOUT. Log failures to an error table with enough context to troubleshoot without exposing sensitive data.
Step 9: Avoid Calling Services Inside Transactions and Triggers
Calling external services from triggers or within long-running transactions can lock rows and slow the database if the remote service is slow. Instead, queue work using DBMS_SCHEDULER jobs or Advanced Queuing, and process calls asynchronously. This design keeps transactions fast and resilient.
Step 10: Secure Credentials
Do not hard-code API keys in PL/SQL source. Store them in protected tables with restricted access, use DBMS_CREDENTIAL where available, or rely on APEX web credentials. Grant execute permissions on integration packages only to necessary schemas.
Conclusion
Calling a web service from Oracle PL/SQL involves configuring network ACLs, setting up a wallet for HTTPS, and using UTL_HTTP or APEX_WEB_SERVICE to send requests. Parse responses with Oracle's native JSON and XML features, handle errors and timeouts carefully, and process calls asynchronously when possible. With these practices, your Oracle database can integrate securely and reliably with modern web services.