Calling a web service by PL/SQL (utl_http)

Last day, a mission is given to me about calling a web services by pl/sql. Firstly, i started to search ; “What is a web service?” The term Web services describes a standardized way of integrating Web-based applications using the XML, SOAP, WSDL and UDDI open standards over an Internet protocol backbone. Actually we can summarize it;

Web Services can convert your applications into Web-applications.
By using Web services, your application can publish its function or message to the rest of the world.

Web Services can be used by other applications.
With Web services your accounting department’s Win 2k servers can connect with your IT supplier’s UNIX server.

The basic Web Services platform is XML + HTTP.
Web services uses XML to code and decode your data and SOAP to transport it.

We have some input parameters and make a request. Then, we get a response from it with output parameters. All this syntax is in XML format. So, we will use XMLtype in our pl/sql package.

 

Let’s start to write a soap access package or we can use this package 🙂 (www.oracle-base.com/dba/miscellaneous/soap_api.sql)

🙂 Let’s assume we have a function “myFunction” in this page http://www.mywebservice.com/web 🙂 Then,start to write our code

 

 

 CREATE OR REPLACE PACKAGE pkg_my_webservice IS
     FUNCTION call_myfuntion
     (
          vp_parameter1 VARCHAR2,
          vp_parameter2 VARCHAR2
     ) RETURN VARCHAR2;
END pkg_my_webservice;
/

CREATE OR REPLACE PACKAGE BODY pkg_my_webservice IS

     vg_funciton_fnc VARCHAR2(256) := 'myFunction';
     vg_ws_address   VARCHAR2(255) := 'http://www.mywebservice.com/web';

     FUNCTION call_myfuntion(
                             --lets assume that it inputs two parameters called string1, string2
                             vp_parameter1 VARCHAR2,
                             vp_parameter2 VARCHAR2)
     
      RETURN VARCHAR2 AS
          ol_req  soap_api.t_request;
          ol_resp soap_api.t_response;
     BEGIN
          -- we initilize a new request
          ol_req := soap_api.new_request(vg_funciton_fnc,
                                         'xmlns="' || vg_ws_address || '"');
          -- we started to add parameters
          soap_api.add_parameter(ol_req,
                                 'string1',
                                 'partns:string',
                                 vp_parameter1);
                                 
          soap_api.add_parameter(ol_req,
                                 'string2',
                                 'partns:string',
                                 vp_parameter1);
                                 
          -- we call the web service
          ol_resp := soap_api.invoke(ol_req, vg_ws_address, vg_funciton_fnc);
          -- we get back the results
          RETURN soap_api.get_return_value(ol_resp,
                                           'result', -- result tag name
                                           'xmlns:m="' || --can be change as "xmlns:n1"
                                           vg_ws_address || '"');
     
     END call_myfuntion;

END pkg_my_webservice;
/

 

pkg_my_webservice_blog.txt

You may need to change some tag attributes. The main basic function syntax is like this. If you have more than one return xml tags, then you need to call the get_return_value function as much as result attributes. You may also store the results in a pl/sql type and return it back.

There are also other ways to do this. You may also use oracle’s “utl_dbws” package, however “utl_http” is more faster than it. You can test it.

In my next post, i am gonna explain soap_api package in more details.