How To Call a Web Service from Oracle PL/SQL – Part II

A while back I posted on how to call a web service from Oracle PL/SQL here, but it was more of a basic starting point.  I had further enhanced the version to be more generic but never posted it.  Sorry.

I’ve written this version in a single declare so you can easily run the example.  When you put it in practice you will want to break it out in to the proper packages and add logging as needed.

declare
   ls_result  clob;
                                                                                --
                                                                                --
   -------------------------------------------------------------------------------
   -- f_callWebService
   --
   -- Helper method to call a web service.
   --
   -- Returns the value returned from the web service method.
   --
   -------------------------------------------------------------------------------
   function f_callWebService (
               as_serviceUrl      in varchar2,
               as_targetNamespace in varchar2,
               as_serviceName     in varchar2,
               as_portName        in varchar2,
               as_wsMethodName    in varchar2,
               as_parmName1       in varchar2, -- TODO: Update to pass an array of name/value pairs.
               as_parmValue1      in varchar2)
   return clob
   is
      cs_soapActionUseKey  constant varchar2(14) := 'SOAPACTION_USE';
      cs_soapActionUseVal  constant varchar2(4)  := 'TRUE';
      cs_soapActionUriKey  constant varchar2(14) := 'SOAPACTION_URI';
      ls_targetNamespace            varchar2(500);
      ls_serviceQname               sys.utl_dbws.qname;
      ls_portQname                  sys.utl_dbws.qname;
      ls_operationQname             sys.utl_dbws.qname;
      ln_service                    sys.utl_dbws.service;
      ln_call                       sys.utl_dbws.call;
      lx_serviceRequest             sys.xmltype;
      lx_serviceResponse            sys.xmltype;
      ls_resultXpath                varchar2(100);
                                                                                --
                                                                                --
      function extractResult(
                  ac_webServiceResponse in xmltype,
                  as_targetNamespace    in varchar2,
                  as_wsMethodName       in varchar2)
      return clob
      is
         lx_result                       sys.xmltype;
      begin
         --
         -- Parse the result from the service response.
         -- Example Xpath: ExecuteResponse/ExecuteResult/child::text()
         --
         ls_resultXpath := as_wsMethodName || 'Response/'
            || as_wsMethodName || 'Result/child::text()';
                                                                                --
         -- Extract return value.
         lx_result := lx_serviceResponse.extract(ls_resultXpath,
            'xmlns="' || as_targetNamespace || '"');
                                                                                --
         if lx_result is not null then
            return lx_result.getClobVal();
         end if;
         return null;
      end extractResult;
   begin
                                                                                --
      if as_serviceUrl is null or trim(as_serviceUrl) is null then
         raise_application_error( -20001,
            'Argument not passed exception. Argument: ''as_serviceUrl''');
      end if;
      --TODO: Validate other parameters.
                                                                                --
      -- Append url separator to end if one does not exist.
      ls_targetNamespace := as_targetNamespace;
      if substr(ls_targetNamespace, length(ls_targetNamespace), 1) != '/' then
         ls_targetNamespace := ls_targetNamespace || '/';
      end if;
      --
      -- Initialize the service components.
      --
      ls_serviceQname := sys.utl_dbws.to_qname(
         name_Space    => ls_targetNamespace,
         name          => as_serviceName);
                                                                                --
      ls_portQname := sys.utl_dbws.to_qname(
         name_Space    => ls_targetNamespace,
         name          => as_portName);
                                                                                --
      ls_operationQname := sys.utl_dbws.to_qname(
         name_Space    => ls_targetNamespace,
         name          => as_wsMethodName);
                                                                                --
      -- Create the service and call objects.
      ln_service := sys.utl_dbws.create_service (
         service_name   => ls_serviceQname);
                                                                                --
      ln_call := sys.utl_dbws.create_call (
         service_handle => ln_service,
         port_name      => ls_portQname,
         operation_name => ls_operationQname);
                                                                                --
      -- Set values on the call object.
      sys.utl_dbws.set_target_endpoint_address(
         call_Handle   => ln_call,
         endpoint      => as_serviceUrl);
                                                                                --
      sys.utl_dbws.set_property(
         call_Handle   => ln_call,
         key           => cs_soapActionUseKey,
         value         => cs_soapActionUseVal);
                                                                                --
      sys.utl_dbws.set_property(
         call_Handle   => ln_call,
         key           => cs_soapActionUriKey,
         value         => ls_targetNamespace
                           || as_wsMethodName);
                                                                                --
      --
      -- Create service request xml.
      --
      lx_serviceRequest := sys.xmltype(
         '<' || as_wsMethodName || ' xmlns="' || ls_targetNamespace || '">'
         ||   '<' || as_parmName1 || '>'
         ||      as_parmValue1
         ||   '</' || as_parmName1 || '>'
         || '</' || as_wsMethodName || '>');
         dbms_output.put_line(lx_serviceRequest.getclobval());
                                                                                --
      --
      -- Call the service.
      --
      begin
         lx_serviceResponse :=
          sys.utl_dbws.invoke(ln_call, lx_serviceRequest);

      exception
         when others then
            -- TODO: Log Error
            dbms_output.put_line(sqlerrm);
            raise;
         end;
                                                                                --
      sys.utl_dbws.release_call (call_handle => ln_call);
      sys.utl_dbws.release_service (service_handle => ln_service);
                                                                                --
      return extractResult(lx_serviceResponse, ls_targetNamespace, as_wsMethodName);
   end f_callWebService;
begin
   ls_result :=
      f_callWebService(
         as_serviceUrl => 'http://interpressfact.net/webservices/getjoke.asmx',
         as_targetNamespace => 'http://interpressfact.net/webservices/',
         as_serviceName => 'getJoke',
         as_portName => '',
         as_wsMethodName => 'getJoke',
         as_parmName1 => 'Category',
         as_parmValue1 => 'Excuses-10');
   dbms_output.put_line(ls_result);
   return;
end;

Disclaimer: This code could very well be improved upon.  Please test it first and please let me know of your improvements.  Thanks!

Run It

I Run the example in Golden6.

  1. Paste the code into a query tab
  2. Open the DBMS.Output window (F10).  This is were the output will be for this example.
  3. Execute Query (F5).

I have noticed that sometimes, the first time I run it, it takes a long time to run (more than a minute).  I assume that this is because the joke web service has not be hit lately and is spinning up, but I’m not certain.  My point is be patient.

Notes

There is currently only a single parameter being passed in because I used it to pass xml to a single parameter web service.

Errors

If the target namespace does not end with a separator ‘/’ the following exception will be raised.  However, you should not have to worry about it because the code sample appends the separator if it is not passed in.

ORA-29532: Java call terminated by uncaught Java exception: javax.xml.rpc.soap.SOAPFaultException: System.Web.Services.Protocols.SoapException: Server did not recognize the value of HTTP Header SOAPAction: http://interpressfact.net/webservicesgetJoke.
at System.Web.Services.Protocols.Soap11ServerProtocolHelper.RouteRequest()
at System.Web.Services.Protocols.SoapServerProtocol.Initialize()
at System.Web.Services.Protocols.ServerProtocolFactory.Create(Type type, HttpContext context, HttpRequest req

Explore posts in the same categories: Oracle PL/SQL

Tags:

You can comment below, or link to this permanent URL from your own site.

6 Comments on “How To Call a Web Service from Oracle PL/SQL – Part II”

  1. Steve Says:

    I been trying to get this to work and have asked the dba to help out as well at this point, but have you ever encountered this error before:

    ORA-29540: class oracle/jpub/runtime/dbws/DbwsProxy does not exist
    ORA-06512: at “SYS.UTL_DBWS”, line 144
    ORA-06512: at line 90
    ORA-06512: at line 145

    Any ideas or suggestions?!
    Thanks!

    • Tom Krueger Says:

      Hi Steve,

      It has been a while now. Basically it can’t find DbwsProxy. I know that I had asked our dba to enable or allow access to some packages. Your dba should know how to find the DbwsProxy and how to grant access to it. However, I am by no have in depth knowledge of oracle security to packages or java libraries.

      Hope that helps at least a little.
      Tom

  2. Gerald Says:

    Hi,
    I looking to do the same thing for a project I’m working on. I want to test this, and use as a guide but I notice that some of the code is cut off. Would it be possible to download this function or email it to me.

    • Tom Krueger Says:

      Hi Gerald,

      Yeah I have to fix the blog template someday as it cuts off the right. However, all of the code is there. If you copy it from the page, the code cut off to the right will actually be copied as well.

      Good Luck,
      Tom

  3. Jean Says:

    Hi Tom,

    The web service is off (http://interpressfact.net/webservicesgetJoke). Is it possible to get its source to get some inspiration? If yes, please send it to me by email. Thanks in advance.

    Jean

    • Tom Krueger Says:

      Hey Jean,

      That was just a public web service that I used as an example. Sorry I don’t have the code for it.

      Thanks,
      Tom


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: