Integrating With Fusion Application Using Services (PLSQL: UTL_HTTP)
By Jani Rautiainen on Dec 05, 2013
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.
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:
- Constructs xml payload, which is used to invoke the service. In the example case it is a “hard coded” string
- 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.
- Creates new HTTP request for POST method
- Configure the request authentication information to be used for the service invocation.
- Configure the request content type to be xml, HTTP method to be POST and set the content length
- Set the SOAP action to be invoked; while the call works without this the value is expected to be set based on standards
- Write the xml payload to the request
- Get the response and process it, in this example we simply print out the response
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.
- PL/SQL Packages and Types Reference - UTL_HTTP
- Oracle Database PL/SQL Packages and Types Reference - DBMS_NETWORK_ACL_ADMIN
- Oracle Wallet Manager
- Oracle Enterprise Repository (OER)