How to call a WSO2 web service from Stored Procedure

I am using oracle database for calling a web service created in WSO2. You can call any web service from the oracle database or procedure. Oracle provides UTL_HTTP package, which is used to make HTTP call out from the procedure or SQL query.  You can use it to access the data on the internet.

Important parameter:

UTL_HTTP.BEGIN_REQUEST: This is used to send request in URL up to 32767 bytes.

UTL_HTTP.SET_HEADER: This is used to set request header of the request.

UTL_HTTP.READ_TEXT:  This is used to read response in text format and you can then extract values from the response.

Example:

I have created a web service in DSS which fetch the unique transaction number from a sequence created in database. On the top of it I have created wrapper in ESB. Now I will create a stored procedure and try to call this ESB service from it.

Stored Procedure:

 create or replace PROCEDURE "GET_UNIQUE_NUMBER" (  
   Result_Desc OUT VARCHAR2,  
   Result_Value OUT VARCHAR2)  
 IS  
  HTTP_REQ UTL_HTTP.REQ;  
  HTTP_RESP UTL_HTTP.RESP;  
  L_ENDPOINT VARCHAR2(100);  
  L_ENV    VARCHAR2(32767);  
  L_WS_RESP  VARCHAR2(32767);  
  L_START_POS NUMBER;  
  L_END_POS  NUMBER;  
 BEGIN  
  L_ENV :=  
  '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://services.nzc/xsd">  
   <soapenv:Header/>  
   <soapenv:Body>  
    <xsd:getUniqueNumber>  
      <xsd:request>  
       <xsd:channelID>DUMMY</xsd:channelID>  
       <xsd:dateTimeStamp>DUMMY</xsd:dateTimeStamp>  
      </xsd:request>  
    </xsd:getUniqueNumber >  
   </soapenv:Body>  
 </soapenv:Envelope>';  
  L_ENDPOINT:='http://localhost:8280/services/GetUniqueNumberProxy';  
  UTL_HTTP.SET_DETAILED_EXCP_SUPPORT(TRUE);  
  UTL_HTTP.SET_PERSISTENT_CONN_SUPPORT (TRUE);  
  HTTP_REQ:= UTL_HTTP.BEGIN_REQUEST(L_ENDPOINT,'POST', 'HTTP/1.1');  
  UTL_HTTP.SET_HEADER (HTTP_REQ, 'Content-Type', 'text/xml' );  
  UTL_HTTP.SET_HEADER (HTTP_REQ, 'charset', 'UTF-8' );  
  UTL_HTTP.SET_HEADER (HTTP_REQ, 'Content-Length', LENGTH (L_ENV) );  
  UTL_HTTP.SET_HEADER (HTTP_REQ, 'SOAPAction', '' );  
  UTL_HTTP.WRITE_TEXT (HTTP_REQ,L_ENV);  
  HTTP_RESP := UTL_HTTP.GET_RESPONSE (HTTP_REQ);  
  UTL_HTTP.READ_TEXT (HTTP_RESP,L_WS_RESP);  
  UTL_HTTP.END_RESPONSE (HTTP_RESP);  
  L_WS_RESP:= REPLACE(SRCSTR => L_WS_RESP, OLDSUB => ' xmlns=""', NEWSUB => '');  
  L_START_POS   := INSTR(L_WS_RESP, '<ns:uniqueNumber')+LENGTH('<ns: uniqueNumber'>');  
  L_END_POS    := INSTR(L_WS_RESP, '</ns: uniqueNumber'>');  
  Result_Value := SUBSTR(L_WS_RESP, L_START_POS, (L_END_POS - L_START_POS));  
   DBMS_OUTPUT.PUT_LINE('UniquerNumber : ' || Result_Value);  
   Result_Desc     := 'Success';  
 EXCEPTION  
 WHEN OTHERS THEN  
  Result_Desc     := 'Unknown Issue Found';  
  Result_Value:='0000';  
  ROLLBACK;  
 END GET_UNIQUE_NUMBER;  

Result:

Connecting to the database <Schema_Name>.

UniqueNumber : 201601211159403411

Process exited.

Disconnecting from the database <Schema_Name>.

Screenshots: