This discussion is archived
4 Replies Latest reply: May 16, 2013 12:50 PM by 1009370 RSS

Web Service Call from PL/SQL

1009370 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points