Context
More and more industries today are integrating and embracing Service Oriented Approach to do their business. During our recent rollout of one of such projects, we found ourselves in the same situation where the client was on EBS 11.5.10 and the vendor was offering web services as touch points for data handshake. Client additionally wanted to schedule this integration on daily basis.
We explored few options like CSV file sharing, DB links etc but none of them were feasible as vendor only offered web service. So should our client buy SOA Suite to integrate with vendor? Or else how will they call a web service?
Bridging the gap
The webservices are exposed to the external parties via a WSDL (Web Services Description Language). This WSDL is provided to the external partner. So the question now is how to use this WSDL from Oracle EBS?
From within EBS, we can call this web service via WSDL using Java or PL/SQL. In our case, we used a PL/SQL procedure to invoke the webservice as it suited the requirement better. To simply the actual business case, let us consider a simple scenario where a supplier responds back with the Inventory Quantity when presented with a Item Code. If the web service exposed by the supplier is called InvQty, the PL/SQL procedure to invoke this web service will be as follows:
CREATE OR REPLACE
PROCEDURE CALL_INV_QTY_WS
IS
soap_request varchar2(30000);
soap_respond varchar2(30000);
http_req utl_http.req;
http_resp utl_http.resp;
launch_url varchar2(240) ;
BEGIN
soap_request:='<?xml version="1.0" encoding="UTF-8"?> <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Header/>
<soap:Body xmlns:ns1="http://xmlns.oracle.com/InvQty_jws/Project1/InvQty">
<ns1:process><ns1:ItemCode>MS787</ns1:ItemCode></ns1:process>
</soap:Body>
</soap:Envelope>';
http_req:= utl_http.begin_request('http://soabpm-vm:7001/soa-infra/services/default/Project1/InvQty_client_ep','POST','HTTP/1.1');
utl_http.set_header(http_req, 'Content-Type', 'text/xml') ;
utl_http.set_header(http_req, 'Content-Length', length(soap_request)) ;
utl_http.set_header(http_req, 'SOAPAction', 'process');
utl_http.write_text(http_req, soap_request) ;
http_resp:= utl_http.get_response(http_req) ;
utl_http.read_text(http_resp, soap_respond) ;
utl_http.end_response(http_resp) ;
dbms_output.put_line(soap_respond);
EXCEPTION WHEN UTL_HTTP.end_of_body THEN
utl_http.end_response(http_resp);
END;
This PL/SQL procedure thus embeds a SOAP (protocol for exchanging the structured information in the implementation of web service) call in the procedure's body. The web service is thus invoked via the procedure CALL_INV_QTY_WS.
Now with the basic code in place, we just need to schedule it!
Based on when the web service needs to be invoked, this procedure can be scheduled in any of the following ways:
1. System scripts such as .bat or .sh invoking with PL/SQL procedure
2. Using DBMS_JOB package
3. Oracle EBS Concurrent Program
Conclusion
While the above scenario is a simple use case, the other business scenarios may be more complex. With Oracle SOA Suite 11g, these complex scenarios can be handled with ease. Further, the transaction management, security and exception handling features in Oracle SOA Suite are capable of enhancing the message delivery and integrity. With a rich Fusion Middleware stack, Oracle completely addresses interaction both within and outside of the Oracle technologies and offers umpteen prebuilt adapter for FTP, Database, EBS, Message Queues etc.
