4 Replies Latest reply: May 16, 2013 2:50 PM by 1009370 RSS

    Web Service Call from PL/SQL

    1009370
      Hi Gurus,

      I am calling a Web Service(RoadNet Application) from Oracle PL/SQL(11g Database). My code is working for simple xml type to get the response. But when I tried with a complex XML type, I am getting the response, but it does not have the complete output.

      Working Sample Code
      ----------------------------------
      Here is the code and the calling block which I am using to call. This does not have any input parameters, just one output parameter.
      -----------------Start of the procedure----------------------------------------------------------------------
      create or replace
      PROCEDURE ORACLE_TO_ROADNET_PROC_TEST (p_input IN NUMBER,
                                        l_out_return OUT NUMBER)
      AS
        l_service          UTL_DBWS.service;--TransportationWebService.Service?? what is DB data service?
        l_call             UTL_DBWS.call;--TransportationWebService.
      
        l_wsdl_url         VARCHAR2(32767);
        l_namespace        VARCHAR2(32767);
        l_soap_env         VARCHAR2(32767);
        l_soap_enc         VARCHAR2(32767);
        l_instance         VARCHAR2(32767);
        l_schema           VARCHAR2(32767);
        l_service_qname    UTL_DBWS.qname;
        l_port_qname       UTL_DBWS.qname;
        l_operation_qname  UTL_DBWS.qname;
      
        l_xmltype_in       SYS.XMLTYPE;
        l_xmltype_out      SYS.XMLTYPE;
      l_xsdoc     DBMS_XMLDOM.DOMDocument;
        l_dn        DBMS_XMLDOM.DOMNode;
        
        l_return           NUMBER;
      BEGIN
      
        l_wsdl_url        := 'http://10.81.8.39:80/TransportationWebService.wsdl';
        l_namespace       := 'http://www.roadnet.com/RTS/TransportationSuite/TransportationWebService';
      
        l_service_qname   := UTL_DBWS.to_qname(l_namespace, 'TransportationWebService');
        l_port_qname      := UTL_DBWS.to_qname(l_namespace, 'TransportationWebService');
        l_operation_qname := UTL_DBWS.to_qname(l_namespace, 'VersionInformation');
      
        l_service := UTL_DBWS.create_service (
          wsdl_document_location => URIFACTORY.getURI(l_wsdl_url),
          service_name           => l_service_qname);
        dbms_output.put_line('Created the Web Service');
      
        l_call := UTL_DBWS.create_call (
          service_handle => l_service,
          port_name      => l_port_qname,
          operation_name => l_operation_qname);
          dbms_output.put_line('Created the Web Service Operation');
      
      --UTL_DBWS.set_target_endpoint_address(l_call, 'http://www.roadnet.com/RTS/TransportationSuite/TransportationWebService/');
      UTL_DBWS.set_property(l_call, 'USERNAME', 'SAE');
      UTL_DBWS.set_property(l_call, 'PASSWORD', 'CxzMte22');
      --UTL_DBWS.set_property(l_call, 'DOMAIN', 'rhea');
      UTL_DBWS.set_property(l_call, 'OPERATION_STYLE', 'document');
      UTL_DBWS.set_property(l_call, 'SOAPACTION_USE', 'true');
      UTL_DBWS.set_property(l_call, 'SOAPACTION_URI', 'http://www.roadnet.com/RTS/TransportationSuite/TransportationWebService/');
       
      l_xmltype_in := SYS.XMLTYPE('<?xml version="1.0" encoding="utf-8"?>
          <VersionInformation xmlns="' || l_namespace || '">
          <parameters>' || p_input || '</parameters>
          </VersionInformation>');
          dbms_output.put_line('Prepared the XML String to pass');
        l_xmltype_out := UTL_DBWS.invoke(call_Handle => l_call ,
                                         request     => l_xmltype_in);
      dbms_output.put_line('Invoked the Web Services');
      
        UTL_DBWS.release_call (call_handle => l_call);
        UTL_DBWS.release_service (service_handle => l_service);
      
      dbms_output.put_line('Before Reading the XML output data');
       l_return := l_xmltype_out.extract(''//ns1:VersionInformationResponse/ns1:version/text()','xmlns:ns1="http://www.roadnet.com/RTS/TransportationSuite/TransportationWebService">').getNumberVal();
       --dbms_output.put_line('After Reading the XML output data'||l_xmltype_out);
      
      --dbms_output.put_line('l_return'||l_return);
      -- l_out_return := l_return;
        --dbms_output.put_line('l_dn '||l_dn);
      
      END ORACLE_TO_ROADNET_PROC_TEST;
      
      -------------------------------End Of Procedure----------------------------------------------------
      
      ---Call to Web Service----------------------------------------
      
      declare
      x_return NUMBER;
      Begin
      Oracle_to_RoadNet_proc_test (p_input => 1,
                             l_out_return => x_return);
      dbms_output.put_line(  ' x_return'||x_return);                    
      end;          
      -----------------------------------------------------------------------------
      Sample Code Which is not giving output
      ----------------------------------------------------
      Here is the other one which is not returing the right output. It got 3 input parameters and multiple output parameters, looks like it is not reading the inputs and not able to get the response.
      -----------------------Start of the procedure---------------------------------------------
      create or replace
      PROCEDURE ORACLE_TO_ROADNET_PROC(p_region_id IN NUMBER,
                                       p_location_type NUMBER,
                                       p_location_id   NUMBER,
                                       l_out_return OUT varchar2)
      AS
        l_service          UTL_DBWS.service;
        l_call             UTL_DBWS.call;
      
        l_wsdl_url         VARCHAR2(32767);
        l_namespace        VARCHAR2(32767);
        l_soap_env         VARCHAR2(32767);
        l_soap_enc         VARCHAR2(32767);
        l_instance         VARCHAR2(32767);
        l_schema           VARCHAR2(32767);
        l_service_qname    UTL_DBWS.qname;
        l_port_qname       UTL_DBWS.qname;
        l_operation_qname  UTL_DBWS.qname;
      
        l_xmltype_in       SYS.XMLTYPE;
        l_xmltype_out      SYS.XMLTYPE;
        l_xsdoc     DBMS_XMLDOM.DOMDocument;
        l_dn        DBMS_XMLDOM.DOMNode;
        
        l_locationinfo      varchar2(100);
      BEGIN
      
        l_wsdl_url        := 'http://10.81.8.39:80/TransportationWebService.wsdl';
        l_namespace       := 'http://www.roadnet.com/RTS/TransportationSuite/TransportationWebService';
      
        l_service_qname   := UTL_DBWS.to_qname(l_namespace, 'TransportationWebService');
        l_port_qname      := UTL_DBWS.to_qname(l_namespace, 'TransportationWebService');
        l_operation_qname := UTL_DBWS.to_qname(l_namespace, 'RetrieveLocationByIdentity');
      
        l_service := UTL_DBWS.create_service (
          wsdl_document_location => URIFACTORY.getURI(l_wsdl_url),
          service_name           => l_service_qname);
        dbms_output.put_line('Created the Web Service');
      
        l_call := UTL_DBWS.create_call (
          service_handle => l_service,
          port_name      => l_port_qname,
          operation_name => l_operation_qname);
          dbms_output.put_line('Created the Web Service Operation');
      
      --UTL_DBWS.set_target_endpoint_address(l_call, 'http://www.roadnet.com/RTS/TransportationSuite/TransportationWebService/');
      UTL_DBWS.set_property(l_call, 'USERNAME', 'SAE');
      UTL_DBWS.set_property(l_call, 'PASSWORD', 'CxzMte22');
      --UTL_DBWS.set_property(l_call, 'DOMAIN', 'rhea');
      UTL_DBWS.set_property(l_call, 'OPERATION_STYLE', 'document');
      UTL_DBWS.set_property(l_call, 'SOAPACTION_USE', 'true');
      UTL_DBWS.set_property(l_call, 'SOAPACTION_URI', 'http://www.roadnet.com/RTS/TransportationSuite/TransportationWebService/');
       
      l_xmltype_in := SYS.XMLTYPE('<?xml version="1.0" encoding="utf-8"?>
          <RetrieveLocationByIdentity xmlns="' || l_namespace || '">
          <identity>
          <regionID>' || p_region_id || '</regionID>
          <locationType>' || p_location_type || '</locationType>
          <locationID>' || p_location_id || '</locationID>
          </identity>
          </RetrieveLocationByIdentity>');
          dbms_output.put_line('Prepared the XML String to pass');
        l_xmltype_out := UTL_DBWS.invoke(call_Handle => l_call ,
                                         request     => l_xmltype_in);
      dbms_output.put_line('Invoked the Web Services');
      
        UTL_DBWS.release_call (call_handle => l_call);
        UTL_DBWS.release_service (service_handle => l_service);
      
      dbms_output.put_line('Before Reading the XML output data');
      insert into  utldbws.EBH_XMLDATA 
      values
      (l_xmltype_out, sysdate);
      commit;
      
      select extractvalue(l_xmltype_out,'//ns1:RetrieveLocationByIdentityResponse/ns1:location/ns1:description', 'xmlns:ns1="http://www.roadnet.com/RTS/TransportationSuite/TransportationWebService">' )
      into l_locationinfo
      from dual;
      
      l_out_return := l_locationinfo;
      dbms_output.put_line('l_locationinfo '||l_locationinfo);
      
      END ORACLE_TO_ROADNET_PROC;
      ----------------------End Of the Procedure-------------------------------------------------------
      
      ----------------Calling Block------------------------
      DECLARE
        p_region_id VARCHAR2(50);
        p_location_type VARCHAR2(50);
        p_location_id VARCHAR2(50);
        L_OUT_RETURN VARCHAR2(100);
      BEGIN
      
        ORACLE_TO_ROADNET_PROC(
          p_region_id => '310',
          p_location_type => '200',
          p_location_id => '499022',
          L_OUT_RETURN => L_OUT_RETURN
        );
        DBMS_OUTPUT.PUT_LINE('L_OUT_RETURN = ' || L_OUT_RETURN);
      END;
      -----------------------------------------------------------------------------------------------
      Looks like this is not reading the input parameters and not returning the output but execution is successful. Please go throguh this and let me know, if I am missing anything. If you have worked on similar Web Services, please let me know.

      Thanks in advance!

      Ravi

      Edited by: BluShadow on 16-May-2013 15:19
      added {noformat}
      {noformat} tags for readability.  Please read the FAQ: {message:id=9360002} and do this yourself in future posts.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
        • 1. Re: Web Service Call from PL/SQL
          odie_63
          Not sure about the rest, but there's at least a typo in the namespace mapping.

          Remove the extra ">" at the end of the string :
          extractvalue(l_xmltype_out,'//ns1:RetrieveLocationByIdentityResponse/ns1:location/ns1:description', 'xmlns:ns1="http://www.roadnet.com/RTS/TransportationSuite/TransportationWebService">' )
          • 2. Re: Web Service Call from PL/SQL
            1009370
            Thank you! I have corrected that still the same issue. It's not an issue with the data retrieval from response. Even before extract I am writing to the table. I do not see the complete output.
            • 3. Re: Web Service Call from PL/SQL
              Billy~Verreynne
              1006367 wrote:

              I am calling a Web Service(RoadNet Application) from Oracle PL/SQL(11g Database).
              I would not use UTL_DBWS. I had to do another web service interface recently, had a look at UTL_DBWS, and again decided not to use it. Simply put. It lacks.

              I use SOAPUI (OpenSource/free version) to interrogate the web service (read wsdl) and format a sample SOAP envelope to use.

              I then use UTL_HTTP and code a HTML POST for that web service, using the SOAPUI generated envelope as template. Supporting WSSE is also not a problem (easily done using PL/SQL).

              I posted an example of my approach in {message:id=10448611} almost a year ago - and recent experience having looked at both UTL_DBWS and the plan vanilla PL/SQL HTTP approach, I still recommend the latter.
              • 4. Re: Web Service Call from PL/SQL
                1009370
                We got the issue. It's nothing to do with code. It's the wsdl url which we were using, was pointing to different server. Corrected the host server to the right box then it started working with the same code.