Home » Developer & Programmer » JDeveloper, Java & XML » problems to read xml data from web service (oracle 10g)
problems to read xml data from web service [message #485958] Sun, 12 December 2010 03:13
yairk30
Messages: 14
Registered: May 2006
Location: israel
Junior Member
hey all,
I running an function that consuming a web service and supose to retrieve back an xml file.
(doing it on oracle 10g on xindows xp)

Since the service looks as of a soap kind , i'm using utl.dbws to do it.
my problem is that the xml file that i've manage to produce is
missing the 3 first lines (including the 1st encoding line),
and the last two.
(i know that by comparing the file i'de produced with my function ,with an xml file i'de produced
from the ws with a ws tester software.
as a result of the missed lines , the data at the corrupted file is represented as '?????').

in another forum i have been told that maybie i shouldent use the utl.dbws , since i dealing here with a REST kind of ws.

Another thing, i've used before utl.http to do it but it did'nt work and eighter i've been told that in my case , it was not the apropriate method to do it.
I'm deeply stuck here , any help will be aprecieted!

thanks,
yair

this is my stuff:

the uncompleeted xml produced with my function:

- <GetFieldsNameResponse xmlns="http://tempuri.org/">
- <GetFieldsNameResult>
- <xs:schema id="DataSet_FRM_GANERIC_PROP" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
- <xs:element name="DataSet_FRM_GANERIC_PROP" msdata:IsDataSet="true" msdata:Locale="he-IL">
- <xs:complexType>
- <xs:choice minOccurs="0" maxOccurs="unbounded">
- <xs:element name="FRM_GANERIC_PROP">
- <xs:complexType>
- <xs:sequence>
  <xs:element name="L1" type="xs:string" minOccurs="0" /> 
  <xs:element name="L2" type="xs:string" minOccurs="0" /> 
  <xs:element name="L3" type="xs:string" minOccurs="0" /> 
  <xs:element name="L4" type="xs:string" minOccurs="0" /> 
  <xs:element name="L5" type="xs:string" minOccurs="0" /> 
  <xs:element name="L6" type="xs:string" minOccurs="0" /> 
  <xs:element name="L7" type="xs:string" minOccurs="0" /> 
  <xs:element name="L8" type="xs:string" minOccurs="0" /> 
  <xs:element name="L9" type="xs:string" minOccurs="0" /> 
  <xs:element name="L10" type="xs:string" minOccurs="0" /> 
  <xs:element name="L11" type="xs:string" minOccurs="0" /> 
  <xs:element name="L12" type="xs:string" minOccurs="0" /> 
  <xs:element name="L13" type="xs:string" minOccurs="0" /> 
  <xs:element name="L14" type="xs:string" minOccurs="0" /> 
  <xs:element name="L15" type="xs:string" minOccurs="0" /> 
  <xs:element name="L16" type="xs:string" minOccurs="0" /> 
  <xs:element name="L17" type="xs:string" minOccurs="0" /> 
  <xs:element name="L18" type="xs:string" minOccurs="0" /> 
  <xs:element name="L19" type="xs:string" minOccurs="0" /> 
  <xs:element name="L20" type="xs:string" minOccurs="0" /> 
  </xs:sequence>
  </xs:complexType>
  </xs:element>
  </xs:choice>
  </xs:complexType>
  </xs:element>
  </xs:schema>
- <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
- <DataSet_FRM_GANERIC_PROP xmlns="">
- <FRM_GANERIC_PROP diffgr:id="FRM_GANERIC_PROP1" msdata:rowOrder="0">
  <L1>?©?? ?₪?¨????</L1> 
  <L2>?©?? ???©?₪????</L2> 
  <L3>?????₪????</L3> 
  <L4>? ??????</L4> 
  <L5>????????????</L5> 
  <L6>?¢???¨ ???????¨????</L6> 
  <L7>???????× ???₪? ??????</L7> 
  </FRM_GANERIC_PROP>
  </DataSet_FRM_GANERIC_PROP>
  </diffgr:diffgram>
  </GetFieldsNameResult>
  </GetFieldsNameResponse>


the xml file produced with a ws tester:

<?xml version="1.0" encoding="UTF-8" ?> 
- <soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
- <soap:Body>
- <GetFieldsNameResponse xmlns="http://tempuri.org/">
- <GetFieldsNameResult>
- <xs:schema id="DataSet_FRM_GANERIC_PROP" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
- <xs:element name="DataSet_FRM_GANERIC_PROP" msdata:IsDataSet="true" msdata:Locale="he-IL">
- <xs:complexType>
- <xs:choice minOccurs="0" maxOccurs="unbounded">
- <xs:element name="FRM_GANERIC_PROP">
- <xs:complexType>
- <xs:sequence>
  <xs:element name="L1" type="xs:string" minOccurs="0" /> 
  <xs:element name="L2" type="xs:string" minOccurs="0" /> 
  <xs:element name="L3" type="xs:string" minOccurs="0" /> 
  <xs:element name="L4" type="xs:string" minOccurs="0" /> 
  <xs:element name="L5" type="xs:string" minOccurs="0" /> 
  <xs:element name="L6" type="xs:string" minOccurs="0" /> 
  <xs:element name="L7" type="xs:string" minOccurs="0" /> 
  <xs:element name="L8" type="xs:string" minOccurs="0" /> 
  <xs:element name="L9" type="xs:string" minOccurs="0" /> 
  <xs:element name="L10" type="xs:string" minOccurs="0" /> 
  <xs:element name="L11" type="xs:string" minOccurs="0" /> 
  <xs:element name="L12" type="xs:string" minOccurs="0" /> 
  <xs:element name="L13" type="xs:string" minOccurs="0" /> 
  <xs:element name="L14" type="xs:string" minOccurs="0" /> 
  <xs:element name="L15" type="xs:string" minOccurs="0" /> 
  <xs:element name="L16" type="xs:string" minOccurs="0" /> 
  <xs:element name="L17" type="xs:string" minOccurs="0" /> 
  <xs:element name="L18" type="xs:string" minOccurs="0" /> 
  <xs:element name="L19" type="xs:string" minOccurs="0" /> 
  <xs:element name="L20" type="xs:string" minOccurs="0" /> 
  </xs:sequence>
  </xs:complexType>
  </xs:element>
  </xs:choice>
  </xs:complexType>
  </xs:element>
  </xs:schema>
- <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
- <DataSet_FRM_GANERIC_PROP xmlns="">
- <FRM_GANERIC_PROP diffgr:id="FRM_GANERIC_PROP1" msdata:rowOrder="0">
  <L1>name</L1> 
  <L2>last name</L2> 
  <L3>cell phone</L3> 
  <L4>email</L4> 
  <L6>remarksL6> 
  </FRM_GANERIC_PROP>
  </DataSet_FRM_GANERIC_PROP>
  </diffgr:diffgram>
  </GetFieldsNameResult>
  </GetFieldsNameResponse>
  </soap:Body>
  </soap:Envelope>


this is my function:

CREATE OR REPLACE
FUNCTION read_from_web_service(in_username in varchar2 , in_password in varchar2)
  RETURN CLOB
AS
  l_service          UTL_DBWS.service;
  l_call             UTL_DBWS.call;
  l_a_ns             VARCHAR2(32767);
  l_wsdl_url         VARCHAR2(32767);
  l_namespace        VARCHAR2(32767);
  l_service_qname    UTL_DBWS.qname;
  l_port_qname       UTL_DBWS.qname;
  l_operation_qname  UTL_DBWS.qname;
  string_type_qname1     sys.utl_dbws.qname;
  string_type_qname2     sys.utl_dbws.qname;

  l_xmltype_in       SYS.XMLTYPE;
  l_xmltype_out      SYS.XMLTYPE;
  l_return           CLOB;
BEGIN 


  
  l_wsdl_url        := 'http://www.company.com/Service/ServiceGetserviceTable.asmx?wsdl';
  l_namespace       := 'http://tempuri.org/';
  l_service_qname   := UTL_DBWS.to_qname(l_namespace, 'ServiceGetserviceTable');
  l_port_qname      := UTL_DBWS.to_qname(l_namespace, 'ServiceGetserviceTableSoap');
  l_operation_qname := UTL_DBWS.to_qname(l_namespace, 'GetFieldsName');
  l_service := UTL_DBWS.create_service (
    wsdl_document_location => URIFACTORY.getURI(l_wsdl_url),
    service_name           => l_service_qname);

  l_call := UTL_DBWS.create_call (
    service_handle => l_service,
    port_name      => l_port_qname,
    operation_name => l_operation_qname);
    
  sys.utl_dbws.set_property(l_call, 'SOAPACTION_USE', 'TRUE');
  sys.utl_dbws.set_property(l_call, 'SOAPACTION_URI', 'http://tempuri.org/GetFieldsName'); 
  sys.utl_dbws.set_property(l_call, 'ENCODINGSTYLE_URI', 'http://schemas.xmlsoap.org/soap/encoding/');
  sys.utl_dbws.set_property(l_call, 'OPERATION_STYLE', 'document');
  string_type_qname1 := sys.utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'string');
  string_type_qname2 := sys.utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'string');
  sys.utl_dbws.add_parameter(l_call, 'user', string_type_qname1, 'ParameterMode.IN');
  sys.utl_dbws.add_parameter(l_call, 'password', string_type_qname2, 'ParameterMode.IN');
  sys.utl_dbws.set_return_type(l_call, string_type_qname1);
  sys.utl_dbws.set_return_type(l_call, string_type_qname2);

    

  l_xmltype_in := SYS.XMLTYPE('<?xml version="1.0" encoding="utf-8"?>
    <GetFieldsName xmlns="' || l_namespace || '">
    <user>' || in_username || '</user>
    <password>'|| in_password || '</password>
    </GetFieldsName>');
    
  l_xmltype_out := UTL_DBWS.invoke(call_Handle => l_call,
                                   request     => l_xmltype_in);
  if  l_xmltype_out.extract('/*') is not null then
  l_return := l_xmltype_out.extract('/*').getclobval();

  else
  l_return := null;
  end if;
  -- dbms_output.put_line(l_return); 
   
    UTL_DBWS.release_call (call_handle => l_call);
  UTL_DBWS.release_service (service_handle => l_service);     
  RETURN (l_return);

END;


Previous Topic: Embeding HTML in OA Framework.
Next Topic: XML to Oracle Database
Goto Forum:
  


Current Time: Thu Mar 28 15:45:18 CDT 2024