Consuming Web Services from PL/SQL

create or replace procedure      XXSOA_SOAP_REQUEST     ( P_URL          in  varchar2
                                 , P_SOAP_REQUEST in  clob
                                 , p_proxy        IN  VARCHAR2
                                 , x_response     OUT CLOB)
IS
   lb_debug      BOOLEAN := TRUE;
   soap_request  CLOB;
   soap_respond  CLOB;
   http_req      utl_http.req;
   http_resp     utl_http.resp;
   resp          XMLType;
   soap_err      EXCEPTION;
   v_code        VARCHAR2(200);
   v_msg         VARCHAR2(1800);
   v_len         NUMBER ;
   v_txt         VARCHAR2(32767);
   Pv_error      VARCHAR2(3000);
   l_chunkData   VARCHAR2(32766);
   l_chunkLength NUMBER:=32766;
   l_chunkstart  number:=1;
 
BEGIN
   DBMS_OUTPUT.PUT_LINE('XSR-001a : Begin Request');
    http_req:= utl_http.begin_request( p_url,'POST', 'HTTP/1.1');  --begin_request
    utl_http.set_header(http_req, 'Content-Type', 'text/xml');
    utl_http.set_header(http_req, 'Content-Length', length(p_soap_request));
    utl_http.set_header(http_req, 'initiate', ''); -- header requirements of particular web service
    --

LOOP
l_chunkData := null;
        l_chunkData := SUBSTR(p_soap_request, l_chunkStart, l_chunkLength);
        utl_http.write_text(http_req, l_chunkData);
        if (length(l_chunkData) < l_chunkLength)
        then     
        NULL;
          exit;
        end if;
        l_chunkStart := l_chunkStart + l_chunkLength;
END LOOP;
DBMS_OUTPUT.PUT_LINE('XSR-002 : After Write Text');
http_resp:= utl_http.get_response(http_req);
    DBMS_OUTPUT.PUT_LINE('XSR-003 : After Get Response');
    utl_http.get_header_by_name(http_resp, 'Content-Length', v_len, 1); -- Obtain the length of the response
    DBMS_OUTPUT.PUT_LINE('XSR-004 : After get header by name');
    --
FOR i in 1..CEIL(v_len/32767) -- obtain response in 32K blocks just in case it is greater than 32K
    LOOP
        DBMS_OUTPUT.PUT_LINE('XSR-005 : in for loop to read text');
        utl_http.read_text(http_resp, v_txt, case when i < CEIL(v_len/32767) then 32767 else mod(v_len,32767) end);
        soap_respond := soap_respond || v_txt; -- build up CLOB
        DBMS_OUTPUT.PUT_LINE('XSR-006 : in for loop after read text');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('XSR-007 : Out of Loop read text');
    utl_http.end_response(http_resp);
    DBMS_OUTPUT.PUT_LINE('XSR-008 : After end response'  );
    resp:= XMLType.createXML(soap_respond); -- Convert CLOB to XMLTYPE
    DBMS_OUTPUT.PUT_LINE('XSR-009 : Afer createXML');
    x_response := soap_respond;
    DBMS_OUTPUT.PUT_LINE('XSR-010 : End of Procedure');
  EXCEPTION
  WHEN UTL_HTTP.REQUEST_FAILED THEN
      Pv_error := SUBSTR('Request_Failed 1: ' || SUBSTR(Utl_Http.Get_Detailed_Sqlerrm, 1, 282), 1, 300);
      x_response :=  Pv_error||' HTTP ERORR '||'---ERROR=='||SQLERRM;
 
    WHEN UTL_HTTP.Http_Server_Error THEN
      Pv_error := SUBSTR('Http_Server_Error 1: ' ||  SUBSTR(Utl_Http.Get_Detailed_Sqlerrm, 1, 282), 1, 300);
      x_response :=  Pv_error||' HTTP ERORR '||'---ERROR=='||SQLERRM;
 
    WHEN UTL_HTTP.Http_Client_Error THEN
      Pv_error := SUBSTR('Http_Client_Error: 1' ||  SUBSTR(Utl_Http.Get_Detailed_Sqlerrm, 1, 282), 1, 300);
      x_response :=  Pv_error||' HTTP ERORR '||'---ERROR=='||SQLERRM;
 
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('XSR-ERR : Error Occured in SOAP Request call : ' || sqlerrm);
      X_RESPONSE :=  PV_ERROR||' HTTP ERORR '||'---ERROR=='||SQLERRM;
END XXSOA_SOAP_REQUEST;


--=================================================================
create or replace procedure XXSOA_SOAP_REQUEST     ( P_URL          in  varchar2
                                 , P_SOAP_REQUEST in  clob
                                 , p_proxy        IN  VARCHAR2
                                 , x_response     OUT CLOB)
IS
   lb_debug      BOOLEAN := TRUE;
   soap_request  CLOB;
   soap_respond  CLOB;
   http_req      utl_http.req;
   http_resp     utl_http.resp;
   resp          XMLType;
   soap_err      EXCEPTION;
   v_code        VARCHAR2(200);
   v_msg         VARCHAR2(1800);
   v_len         NUMBER;
   v_txt         VARCHAR2(32767);
   Pv_error      VARCHAR2(3000);
 
   req_length binary_integer;
   offset pls_integer:=1;
   amount pls_integer:=2000;
   buffer varchar2(2000);
 
BEGIN
   DBMS_OUTPUT.PUT_LINE('XSR-001a : Begin Request');
   http_req:= utl_http.begin_request( p_url,'POST', 'HTTP/1.1');  --begin_request
    utl_http.set_header(http_req, 'Content-Type', 'text/xml');
 
    --req_length:=length(p_soap_request);
 
    utl_http.set_header(http_req, 'initiate', ''); -- header requirements of particular web service
 
    if length(p_soap_request)<=32767 then
        utl_http.set_header(http_req, 'Content-Length', length(p_soap_request));
        utl_http.write_text(http_req, to_clob(p_soap_request));
    else
        utl_http.set_header(http_req, 'Transfer-Encoding', 'Chunked');
     
        while(offset < length(p_soap_request))
        loop     
            dbms_lob.read(p_soap_request,amount,offset,buffer);
            utl_http.write_text(http_req,buffer);
            offset:=offset+amount;     
        end loop;             
    end if;
--    UTL_HTTP.set_transfer_timeout (180);
    DBMS_OUTPUT.PUT_LINE('XSR-002 : After Write Text');
    http_resp:= utl_http.get_response(http_req);
    DBMS_OUTPUT.PUT_LINE('XSR-003 : After Get Response');
    utl_http.get_header_by_name(http_resp, 'Content-Length', v_len, 1); -- Obtain the length of the response
    DBMS_OUTPUT.PUT_LINE('XSR-004 : After get header by name');
 
    FOR i in 1..CEIL(v_len/32767) -- obtain response in 32K blocks just in case it is greater than 32K
    LOOP
        DBMS_OUTPUT.PUT_LINE('XSR-005 : in for loop to read text');
        utl_http.read_text(http_resp, v_txt, case when i < CEIL(v_len/32767) then 32767 else mod(v_len,32767) end);
        soap_respond := soap_respond || v_txt; -- build up CLOB
        DBMS_OUTPUT.PUT_LINE('XSR-006 : in for loop after read text');
    END LOOP;
 
    DBMS_OUTPUT.PUT_LINE('XSR-007 : Out of Loop read text');
    utl_http.end_response(http_resp);
    DBMS_OUTPUT.PUT_LINE('XSR-008 : After end response'  );
    resp:= XMLType.createXML(soap_respond); -- Convert CLOB to XMLTYPE
    DBMS_OUTPUT.PUT_LINE('XSR-009 : Afer createXML');
    x_response := soap_respond;
    DBMS_OUTPUT.PUT_LINE('XSR-010 : End of Procedure');
  EXCEPTION
  WHEN UTL_HTTP.REQUEST_FAILED THEN
      Pv_error := SUBSTR('Request_Failed 1: ' || SUBSTR(Utl_Http.Get_Detailed_Sqlerrm, 1, 282), 1, 300);
      x_response :=  Pv_error||' HTTP ERORR '||'---ERROR=='||SQLERRM;
 
    WHEN UTL_HTTP.Http_Server_Error THEN
      Pv_error := SUBSTR('Http_Server_Error 1: ' ||  SUBSTR(Utl_Http.Get_Detailed_Sqlerrm, 1, 282), 1, 300);
      x_response :=  Pv_error||' HTTP ERORR '||'---ERROR=='||SQLERRM;
 
    WHEN UTL_HTTP.Http_Client_Error THEN
      Pv_error := SUBSTR('Http_Client_Error: 1' ||  SUBSTR(Utl_Http.Get_Detailed_Sqlerrm, 1, 282), 1, 300);
      x_response :=  Pv_error||' HTTP ERORR '||'---ERROR=='||SQLERRM;
 
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('XSR-ERR : Error Occured in SOAP Request call : ' || sqlerrm);
      X_RESPONSE :=  PV_ERROR||' HTTP ERORR '||'---ERROR=='||SQLERRM;
END XXSOA_SOAP_REQUEST;

How to invoke secured webservices(https) from oracle SOA Composite

In this post i will explain you to how to import secured web services from oracle SOA composite.

For invoking the secured webservices (https) from oracle BPEL you need to import the certificates in to your server.

• Open the endpoint URL of the webservice in internet explorer After connecting to the server, a pop-up window displays the security alert and asks whether you trust this certificate or not?

• Click on “yes” to accept the certificate.

• Once the page gets loaded double click on the lock displays in the status bar in the bottom right corner of the browser window.

• A new popup window titled “Certificate” would be displayed click on the details tab and press “copy the file” button to save the certificate in a file.

• When you press the “Copy to File” button a wizard would guide to save the certificate in “(.cer)” format.

• Click on next and finish the wizard by pressing the “Finish” button.

Now let’s import the certificate  in to our local server location .

Now we have “myIdentitystore.jks” containing your own host specific certificate and private key, and “trust.jks” containing the trusted certificates.

Now we can list ‘mytrust.jks”to check if the export was successful or not.

We are using the below command to do that .

Restart Oracle SOA suite .

• Create a new BPEL process project named “InvokeHTTPS“ with the Synchronous BPEL Process.

• Click on next and accept all the defaults and finish the wizard.

• Right click on the services area and choose “Create Partner Link” from the context menu.

• Name this partner link “MySecSer”.

• Browse the WSDL file from the file system. JDeveloper would ask to make a local copy of the external WSDL file and ask to add partner link in the WSDL. Click on “Yes” on both the dialog boxes.

• Select Partner Link Type and Partner Role and click on “OK” button.

• Add “invoke” activity named “InvokeMySecSer” and link it with the partner link. Select the operation “New” and create input and output variables.

• Add 2 assign activities to assign the input and out variables. Final BPEL process will look like the following image:

• Deploy the BPEL process using EM or jdeveloper.

Now you canable to invoke web services exposed over https from Oracle BPEL Process Manager.