Oracle Database
Use the various functionalities of Oracle database such as APEX_WEB_SERVICE, UTL_HTTP package, and Application Express (Apex) to invoke SOAP web services.
APEX Web Service
Use APEX_WEB_SERVICE to invoke web services on a database with Application Express (APEX) schema.
-
Construct an xml payload to invoke the service. This example code passes a hard coded string to the request.
-
Invoke the service using the APEX_WEB_SERVICE API, which in turn uses the UTL_HTTP package.
-
Receive the response and process it. This example code just prints the response.
declare
l_result XMLTYPE;
l_envelope CLOB;
BEGIN
-- Construct xml payload to invoke the service. In this example, it is a hard coded string.
l_envelope := '<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http ://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<findRule
xmlns="http://xmlns.oracle.com/apps/incentiveCompensation/cn/creditSetup/creditRule/creditRuleService/ty
pes/">
<findCriteria>
<fetchStart xmlns="http://xmlns.oracle.com/adf/svc/types/">0</fetchStart>
<fetchSize xmlns="http://xmlns.oracle.com/adf/svc/types/">-1</fetchSize>
<filter xmlns="http://xmlns.oracle.com/adf/svc/types/">
<group>
<upperCaseCompare>false</upperCaseCompare>
<item>
<upperCaseCompare>false</upperCaseCompare>
<attribute>RuleId</attribute>
<operator>=</operator>
<value>300000000851162</value>
</item>
</group>
</filter>
<excludeAttribute xmlns="http://xmlns.oracle.com/adf/svc/types/">false</excludeAttribute>
</findCriteria>
<findControl>
<retrieveAllTranslations
xmlns="http://xmlns.oracle.com/adf/svc/types/">false</retrieveAllTranslations>
</findControl>
</findRule>
</soap:Body>
</soap:Envelope>';
-- Call the web service
l_result := apex_web_service.make_request(
p_url => 'https://host:port/icCnSetupCreditRulesPublicService/CreditRuleService?wsdl',
p_action => 'http://xmlns.oracle.com/apps/incentiveCompensation/cn/creditSetup/creditRule/creditRuleService/findRule',
p_envelope => l_envelope,
p_username => 'username',
p_password => 'password' );
dbms_output.put_line(l_result.getClobVal());
END;
UTL_HTTP Package
-
Access to the database network
-
Create wallet containing the certificate if you want to access the web service over SSL
-
Construct an xml payload to invoke the service. This example code passes a hard coded string to the request.
-
Set the Oracle Wallet used for the request. This wallet must contain the certificate for the server that hosts the service, and is used to access the service using HTTPS.
-
Create a new HTTP request for the
POST
method. -
Configure the request to use the authentication information to invoke the service.
-
Configure the request content type to
xml
, HTTP method toPOST
, and set the content length. -
Set the
SOAPAction
to be invoked. Though the call works without this value, it is a recommended standard. -
Write the xml payload to the connection as request.
-
Receive the response and process it. This example code only prints the response.
DECLARE
-- Construct xml payload to invoke the service. In this example, it is a hard coded string.
l_envelope VARCHAR2(32767) := '<soap:Envelope
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<findRule
xmlns="http://xmlns.oracle.com/apps/incentiveCompensation/cn/creditSetup/creditRule/creditRuleService/ty
pes/">
<findCriteria>
<fetchStart
xmlns="http://xmlns.oracle.com/adf/svc/types/">0</fetchStart>
<fetchSize xmlns="http://xmlns.oracle.com/adf/svc/types/">-
1</fetchSize>
<filter xmlns="http://xmlns.oracle.com/adf/svc/types/">
<group>
<upperCaseCompare>false</upperCaseCompare>
<item>
<upperCaseCompare>false</upperCaseCompare>
<attribute>RuleId</attribute>
<operator>=</operator>
<value>300000000851162</value>
</item>
</group>
</filter>
<excludeAttribute
xmlns="http://xmlns.oracle.com/adf/svc/types/">false</excludeAttribute>
</findCriteria>
<findControl>
<retrieveAllTranslations
xmlns="http://xmlns.oracle.com/adf/svc/types/">false</retrieveAllTranslations>
</findControl>
</findRule>
</soap:Body>
</soap:Envelope>';
l_result VARCHAR2(32767) := null;
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
l_counter PLS_INTEGER;
l_length PLS_INTEGER;
BEGIN
-- Sets the Oracle wallet used for request; required for HTTPS
UTL_HTTP.set_wallet('file:/scratch/oradba/wallet', 'password');
-- Creates a new HTTP request
l_http_request :=
UTL_HTTP.begin_request('https://host:port/icCnSetupCreditRulesPublicService/CreditRuleService',
'POST','HTTP/1.1');
-- Configure the authentication details
UTL_HTTP.SET_AUTHENTICATION(l_http_request, 'username', 'password');
-- Configure the request content type to be xml and set the content length
UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml');
UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_envelope));
-- Set the SOAP action to be invoked; while the call works without this, the value is expected to be set based on
standards
UTL_HTTP.set_header(l_http_request, 'SOAPAction',
'http://xmlns.oracle.com/apps/incentiveCompensation/cn/creditSetup/creditRule/creditRuleService/findRule');
-- Write the xml payload to the request
UTL_HTTP.write_text(l_http_request, l_envelope);
-- Get the response and process it. In this example, we simply print out the response
l_http_response := UTL_HTTP.get_response(l_http_request);
UTL_HTTP.read_text(l_http_response, l_result);
UTL_HTTP.end_response(l_http_response);
l_counter := 1;
l_length := LENGTH(l_result);
WHILE (l_counter <= l_length) LOOP
DBMS_OUTPUT.put_line(SUBSTR(l_result, l_counter, 80));
l_counter := l_counter + 80;
END LOOP;
END;
/
Oracle Application Express (Apex)
-
URL, for example,
https://(ICDomain,Incentive Compensation)/icCnSetupCreditRulesPublicService/CreditRuleService?WSDL
. -
Action: Defines the service and the operation, including the namespace. For example,
{http://xmlns.oracle.com/apps/incentiveCompensation/cn/creditSetup/creditRule/creditRuleService/}RuleAttribute
. -
Basic Authentication: Enable this to pass credentials to a secured service.
-
SOAP Envelope: Defines the content to be passed to the service.
-
Store Response in Collection: Temporary structure to hold the response from the web service call. Use this object to parse the response XML to be used in the application.