Integrating With Fusion Application Using Services (PLSQL: UTL_HTTP)

Fusion Applications provides Web services that allow external systems to integrate with Fusion Applications. There are two types of services: ADF services and composite services. ADF services are created for a logical business object and provide functionality to access and manipulate these objects. The composite services are mostly process oriented and provide an orchestration of multiple steps.  

Information about the web services provided by Fusion Applications is hosted in Oracle Enterprise Repository (OER). The information provided by OER can be used to understand the functionality provided by the service and how the service can be called.

This series of articles describes how one can invoke SOAP web services provided by Fusion Applications using various technologies. In this article we will cover how to invoke a Fusion Application web service using PLSQL package UTL_HTTP.

Prerequisites

There are various configurations that need to be done before using UTL_HTTP to call a service. In order to make the call the user must have access to network; the access can be granted with DBMS_NETWORK_ACL_ADMIN. If the DBMS_NETWORK_ACL_ADMIN is not installed in the database refer to note 1118447.1 in Oracle Support on how to install it.
If accessing service over SSL you will also need to create a wallet containing the certificate.  The certificate to be used can be obtained using the browser. For example in FireFox open the WSDL URL, click the lock icon besides the URL in the address bar, select "More Information -> Details -> View Certificate -> Details -> Export" and save to a file. Next create a wallet and import the certificate to the wallet:

orapki wallet create -wallet /scratch/oradba/wallet -pwd Welcome1 -auto_login
orapki wallet add -wallet /scratch/oradba/wallet -trusted_cert -cert /scratch/oradba/wallet/test.cer -pwd Welcome1

For further details on how to create the wallet and store certificates refer to Oracle Wallet Manager in administrator guide.

Implementing Web Service Call

With UTL_HTTP we are directly constructing and processing the XML for the SOAP:

DECLARE
  -- Construct xml payload, which is used to invoke the service. In the example case it is a "hard coded" string.
  l_envelope VARCHAR2(32767) := '<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/types/">
                                       <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', 'Welcome1');
 
  -- Creates new HTTP request
  l_http_request := UTL_HTTP.begin_request('https://host:port/icCnSetupCreditRulesPublicService/CreditRuleService', 'POST','HTTP/1.1');
 
  -- Configure the authentication details on the request
  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;
/

The above code does the following:

  1. Constructs xml payload, which is used to invoke the service. In the example case it is a “hard coded” string
  2. Sets the Oracle wallet used for request. The wallet used needs to contain certificate for the server hosting the service. The certificate is used to access the service using HTTPS.
  3. Creates new HTTP request for POST method
  4. Configure the request authentication information to be used for the service invocation.
  5. Configure the request content type to be xml, HTTP method to be POST and set the content length
  6. Set the SOAP action to be invoked; while the call works without this the value is expected to be set based on standards
  7. Write the xml payload to the request
  8. Get the response and process it, in this example we simply print out the response


Summary

In this article we covered an example using UTL_HTTP PLSQL package to integrate with Fusion Applications using web services. In future articles other technologies for invoking Fusion Applications web services will be covered.

References



Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow us on twitter Fusion Applications Extensibility, Customizations and Integration forum Fusion Applications Dev Relations YouTube Channel
This blog offers news, tips and information for developers building extensions, customizations and integrations for Oracle Fusion Applications.

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
4
5
6
8
11
12
13
15
16
18
19
20
22
23
24
25
26
27
28
29
30
   
       
Today