Oracle PL SQL tutorial day 16
Introduction
In enterprise applications, integrating with external systems is a common requirement. Often, we need to interact with a SOAP-based web service for functionalities like payment processing, customer validation, shipment tracking, and more.
While Oracle PL/SQL is not typically used for direct web service calls, Oracle provides a way to consume SOAP services using the UTL_HTTP package. In this blog, we will walk you through how to call a SOAP service from PL/SQL using an example and explain each step in detail.
Prerequisites
1. Oracle 11g or later.
2. Access to a working SOAP web service.
3. ACL (Access Control List) configured for the database user to allow outbound HTTP requests.
We will use a sample public SOAP web service for currency conversion:
WSDL: http://currencyconverter.kowabunga.net/converter.asmx?WSDL
Method: GetConversionRate
Parameters:
- CurrencyFrom: string
- CurrencyTo: string
- RateDate: dateTime
Objective
We will call the GetConversionRate method from PL/SQL and fetch the conversion rate between USD and INR for a given date.
Step 1: Configure ACL for UTL_HTTP
Oracle requires that you explicitly grant access to network resources via Access Control Lists (ACLs). Run the following as SYS or DBA:
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'soap_access.xml',
description => 'SOAP Web Service Access',
principal => 'YOUR_DB_USER',
is_grant => TRUE,
privilege => 'connect'
);
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'soap_access.xml',
host => 'currencyconverter.kowabunga.net'
);
END;
/
Replace YOUR_DB_USER with your actual schema name.
Step 2: Create a PL/SQL Procedure to Call the SOAP Service
Here's a sample procedure that constructs the SOAP envelope, sends an HTTP POST request, and parses the response:
CREATE OR REPLACE PROCEDURE call_currency_soap (
p_currency_from IN VARCHAR2,
p_currency_to IN VARCHAR2,
p_rate_date IN DATE,
p_result OUT NUMBER
) IS
l_http_req UTL_HTTP.req;
l_http_resp UTL_HTTP.resp;
l_buffer VARCHAR2(32767);
l_response CLOB;
l_soap_envelope CLOB;
l_rate_tag_start PLS_INTEGER;
l_rate_tag_end PLS_INTEGER;
BEGIN
-- Construct SOAP Envelope
l_soap_envelope := '<?xml version="1.0" encoding="utf-8"?>' ||
'<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" ' ||
'xmlns:xsd="http://www.w3.org/2001/XMLSchema" ' ||
'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">' ||
'<soap:Body>' ||
'<GetConversionRate xmlns="http://www.webserviceX.NET/">' ||
'<CurrencyFrom>' || p_currency_from || '</CurrencyFrom>' ||
'<CurrencyTo>' || p_currency_to || '</CurrencyTo>' ||
'<RateDate>' || TO_CHAR(p_rate_date, 'YYYY-MM-DD"T"HH24:MI:SS') || '</RateDate>' ||
'</GetConversionRate>' ||
'</soap:Body>' ||
'</soap:Envelope>';
-- Initialize HTTP request
l_http_req := UTL_HTTP.begin_request(
url => 'http://currencyconverter.kowabunga.net/converter.asmx',
method => 'POST',
http_version => 'HTTP/1.1'
);
-- Set headers
UTL_HTTP.set_header(l_http_req, 'Content-Type', 'text/xml; charset=utf-8');
UTL_HTTP.set_header(l_http_req, 'Content-Length', LENGTH(l_soap_envelope));
UTL_HTTP.set_header(l_http_req, 'SOAPAction', '"http://www.webserviceX.NET/GetConversionRate"');
-- Write the SOAP request
UTL_HTTP.write_text(l_http_req, l_soap_envelope);
-- Get the response
l_http_resp := UTL_HTTP.get_response(l_http_req);
-- Read the full response
BEGIN
LOOP
UTL_HTTP.read_text(l_http_resp, l_buffer);
l_response := l_response || l_buffer;
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
NULL;
END;
UTL_HTTP.end_response(l_http_resp);
-- Parse the response
l_rate_tag_start := INSTR(l_response, '<GetConversionRateResult>') + LENGTH('<GetConversionRateResult>');
l_rate_tag_end := INSTR(l_response, '</GetConversionRateResult>');
IF l_rate_tag_start > 0 AND l_rate_tag_end > 0 THEN
p_result := TO_NUMBER(SUBSTR(l_response, l_rate_tag_start, l_rate_tag_end - l_rate_tag_start));
ELSE
p_result := NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('Error: ' || SQLERRM);
p_result := NULL;
END;
/
Step 3: Execute the Procedure
Now call the procedure and pass in the values for currency and date:
DECLARE
l_rate NUMBER;
BEGIN
call_currency_soap(
p_currency_from => 'USD',
p_currency_to => 'INR',
p_rate_date => TO_DATE('2024-06-01', 'YYYY-MM-DD'),
p_result => l_rate
);
DBMS_OUTPUT.put_line('Conversion Rate (USD to INR): ' || l_rate);
END;
/
Sample Output:
Conversion Rate (USD to INR): 83.123
Note: The actual number will vary depending on the service response.
Explanation of Key Components
1. UTL_HTTP: Core package to send and receive HTTP requests in PL/SQL.
2. SOAP Envelope: XML formatted request body required by the SOAP endpoint.
3. SOAPAction: Specific header indicating the SOAP method to call.
4. Response Parsing: We use string functions (INSTR, SUBSTR) to extract the value between XML tags.
Important Tips
- Always ensure your database user has network access permissions via ACL.
- UTL_HTTP has a 32KB limit per call. Use CLOBs if you expect large responses.
- Use DBMS_LOB.CONVERTTOCLOB if you're dealing with RAW/BLOB data formats.
- If the endpoint uses HTTPS, ensure Oracle Wallet is configured.
Security Considerations
- Never hard-code credentials or sensitive data in plain text. Use secure vaults or encryption.
- Make sure the service is trusted, especially if calling from a production database.
- Monitor network usage and log SOAP calls for auditing.
Conclusion
Calling a SOAP service from PL/SQL is perfectly feasible using the UTL_HTTP package. While Oracle doesn't offer native SOAP client libraries, you can achieve SOAP integration by constructing the envelope manually and handling the HTTP response.
This approach is especially useful in legacy systems or when PL/SQL must trigger external service calls without relying on middleware. With a little setup and careful XML handling, SOAP integration from Oracle becomes straightforward.
Stay tuned for more PL/SQL integration tutorials!
0 Comments