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.

The following is a sample client code to:

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

Use UTL_HTTP package over HTTPS to invoke a SOAP web service by constructing and processing the XML payload. Configure the following before using the UTL_HTTP package:

Access to the database network
Create wallet containing the certificate if you want to access the web service over SSL

For more information, refer to Fusion Middleware Administrator's Guide available at Oracle Help Center. The following is a sample client code to:

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 to POST, 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)

Use Apex to integrate the web services by using the Web Service Reference objects. These objects work with the APEX_WEB_SERVICE API to invoke the web services. To define a reference for a ADF business component service, use values such as:

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.

Related Topics

Invoking SOAP Web Services