Calling webservice from PLSQL

Sometimes for software architectural issue we are in need to call webservice API. Suppose, we are using database for all type of backend processing, in that scenario its very impractical to call Webservice using another application or language. We can easily overcome this scenario by calling webservice from plsql code. For this purpose, we can use the below code:


Its a package containing the Stored Procedure to call a soap service form PL Sql:

Package Spec:

create or replace PACKAGE PKG_ABS_ALERT_WS AS 

  /* TODO enter package declarations (types, exceptions, methods etc) here */ 

 PROCEDURE SP_CALL_SMS_SERVICE(

  P_MOBILE_NUM IN VARCHAR2,

  P_MESSAGE IN VARCHAR2

  );

END PKG_ABS_ALERT_WS;

Package Body:

create or replace PACKAGE BODY PKG_ABS_ALERT_WS AS
  PROCEDURE SP_CALL_SMS_SERVICE(
  P_MOBILE_NUM IN VARCHAR2,
  P_MESSAGE IN VARCHAR2
  ) AS

  vg_funciton_fnc VARCHAR2(256);
  vg_ws_address   VARCHAR2(255);
  l_namespace VARCHAR2(255);
  l_return    VARCHAR2(32767);
  ol_req  soap_api.t_request;
  ol_resp soap_api.t_response;

  BEGIN  
    DBMS_OUTPUT.PUT_LINE('P_MOBILE_NUM: '||P_MOBILE_NUM||'  P_MESSAGE:'||P_MESSAGE );   
     vg_funciton_fnc:= 'ns1:sendSMS';
     vg_ws_address:= 'http://:8080/NotificationService/SMSService?wsdl';
     l_namespace  := 'xmlns:ns1="http://sms.notification.dbbl.com/"';

       --we initilize a new request 
           ol_req := soap_api.new_request(vg_funciton_fnc,l_namespace);
        
       -- we started to add parameters
          
              
          soap_api.add_parameter(ol_req,
                                 'channelName','ABS'
                                 );
                             
          soap_api.add_parameter(ol_req,
                                 'mobileNo',P_MOBILE_NUM
                                  );                               
          soap_api.add_parameter(ol_req,
                                 'messageText',P_MESSAGE
                                 );
          soap_api.add_parameter(ol_req,
                                 'refId',
                                 alrt_seq.nextval);
                                 
        
                       
          -- we call the web service
          ol_resp := soap_api.invoke(ol_req, vg_ws_address, vg_funciton_fnc);
          
          -- we get back the results
          l_return := soap_api.get_return_value(ol_resp,
                                           'return', -- result tag name
                                           'xmlns:m="' || --can be change as "xmlns:n1"
                                           vg_ws_address || '"');
                                           
         DBMS_OUTPUT.PUT_LINE('Output: '||l_return);                          
     
  END SP_CALL_SMS_SERVICE;

END PKG_ABS_ALERT_WS;




There is an dependant package for the above code. We can get that package from Oracle Base site.