Posted tagged ‘PL/SQL’

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

February 2, 2010

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.

   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
      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             ;
      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
         lx_result                       sys.xmltype;
         -- 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;
      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.
         call_Handle   => ln_call,
         endpoint      => as_serviceUrl);
         call_Handle   => ln_call,
         key           => cs_soapActionUseKey,
         value         => cs_soapActionUseVal);
         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 || '>');
      -- Call the service.
         lx_serviceResponse :=
          sys.utl_dbws.invoke(ln_call, lx_serviceRequest);

         when others then
            -- TODO: Log Error
      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;
   ls_result :=
         as_serviceUrl => '',
         as_targetNamespace => '',
         as_serviceName => 'getJoke',
         as_portName => '',
         as_wsMethodName => 'getJoke',
         as_parmName1 => 'Category',
         as_parmValue1 => 'Excuses-10');

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.


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


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:
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


How To Load XML values into a PL/SQL Collection.

January 27, 2009

The following example demonstrates how to load a PL/SQL table type collection from xml utilizing “bulk collect” and XMLSequence.

   -- Declare collection type to bulk load into.
   type document_rt is record (
        doc_id         number,
        doc_name       varchar2(255)
   type documents_tt is table of document_rt;

   docsXml        xmltype;
   docTable       documents_tt;
   docRecord      document_rt;

   -- Create Example Xml for documents.
   docsXml := xmltype(
      || '  <Document>'
      || '    <Id>111</Id>'
      || '    <Name>First Document</Name>'
      || '  </Document>'
      || '  <Document>'
      || '    <Id>222</Id>'
      || '    <Name>Second Document</Name>'
      || '  </Document>'
      || '</Documents>');

   -- Bulk load the docTable collection based on the xml.
   select extractValue( value( t ), 'Document/Id' ) doc_id,
          extractValue( value( t ), 'Document/Name' ) doc_name
     bulk collect into docTable
     from table( XMLSequence(
                    extract( docsXml, 'Documents/Document') ) ) t;

   -- Loop through collection to display results.
   if docTable.count > 0 then
      for i in docTable.FIRST..docTable.LAST loop
         docRecord := docTable( i );
         dbms_output.put_line( 'Id: ' || docRecord.doc_id );
         dbms_output.put_line( 'Name: ' || docRecord.doc_name );
      end loop;
   end if;


Id: 111
Name: First Document
Id: 222
Name: Second Document

How To Call a Web Service from Oracle PL/SQL

September 17, 2008

I would like to say that calling a web service has been made easy with Sys.UTL_DBWS package but it took me a bit of time to get it right.  This biggest issue that I ran into is that the samples that I found online and in the forums simply don’t work.  The sample below was provided by Oracle support and helped a lot in getting started so I thought that I would pass it along.  The sample uses a public web service so if the service is still running this code should just work for you.  My ultimate goal was to call a Microsoft WCF service which I have been able to do and plan to post the code soon.

function get_joke
return varchar2
  service_           sys.utl_dbws.SERVICE;
  call_              sys.utl_dbws.CALL;
  service_qname      sys.utl_dbws.QNAME;
  port_qname         sys.utl_dbws.QNAME;
  xoperation_qname   sys.utl_dbws.QNAME;
  xstring_type_qname sys.utl_dbws.QNAME;
  response           sys.xmltype;
  request            sys.xmltype;
  service_qname := sys.utl_dbws.to_qname(null, 'getJoke');
  service_ := sys.utl_dbws.create_service(service_qname);
  call_ := sys.utl_dbws.create_call(service_);
  sys.utl_dbws.set_target_endpoint_address(call_, '');
  sys.utl_dbws.set_property( call_, 'SOAPACTION_USE', 'TRUE');
  sys.utl_dbws.set_property( call_, 'SOAPACTION_URI', '');
  sys.utl_dbws.set_property( call_, 'OPERATION_STYLE', 'document');
  request := sys.xmltype(
       '<getJoke xmlns="">'
    || '<Category>Excuses-10</Category>'
    || '</getJoke>');
  response :=sys. utl_dbws.invoke(call_, request);
  return response.extract('//getJokeResult/child::text()',