This forum is currently locked. You can't register or post questions at this time. (read more)

Some problem with web services and pl-sql

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Eremita
Member
Posts: 13
Joined: Fri May 25, 2012 2:49 pm

Re: Some problem with web services and pl-sql

Postby Eremita » Wed May 30, 2012 3:34 pm

Hi Tim,

now this web service works fine with my functions :D :

Code: Select all

-- First function without parameter
declare
    l_env t_soap_envelope;
    l_xml xmltype;
    fitt  xmltype;
    l_val varchar2(4000);
    l_start_date date;
begin

    l_env := t_soap_envelope ('http://www.webservicex.net', 'geoipservice.asmx', 'GetGeoIPContext', 'xmlns="http://www.webservicex.net/"');
    --l_env.add_param ('IPAddress', '10.1.1.138');
   
    l_start_date := sysdate;
    l_xml := flex_ws_api.make_request(p_url => l_env.service_url, p_action => l_env.soap_action, p_envelope => l_env.envelope);
   
    -- flex_ws_util.get_value() in questo caso non ci serve, ma:
    l_val := flex_ws_util.get_value (l_xml, 'GetGeoIPContextResponse', null, 'error');
   
    --flex_ws_util.log_request (l_env.service_url, l_env.service_method, l_env.envelope, l_xml, l_start_date,
    --    p_log_text => 'Converting 100 feet to meters', p_val1 => l_val);
   
    l_val := substr(l_xml.getClobVal(),39,length(l_xml.getClobVal()));
    dbms_output.put_line('XML DI RISPOSTA: ');
    dbms_output.put_line(l_val);
end;


Code: Select all

--Second function with parameter
declare
    l_env t_soap_envelope;
    l_xml xmltype;
    fitt  xmltype;
    l_val varchar2(4000);
    l_start_date date;
begin

    l_env := t_soap_envelope ('http://www.webservicex.net', 'geoipservice.asmx', 'GetGeoIP', 'xmlns="http://www.webservicex.net/"');
    l_env.add_param ('IPAddress', '10.1.1.138');
   
    l_start_date := sysdate;
    l_xml := flex_ws_api.make_request(p_url => l_env.service_url, p_action => l_env.soap_action, p_envelope => l_env.envelope);
   
    -- flex_ws_util.get_value() in questo caso non ci serve, ma:
    l_val := flex_ws_util.get_value (l_xml, 'GetGeoIPResponse', null, 'error');
   
    --flex_ws_util.log_request (l_env.service_url, l_env.service_method, l_env.envelope, l_xml, l_start_date,
    --    p_log_text => 'Converting 100 feet to meters', p_val1 => l_val);
   
    l_val := substr(l_xml.getClobVal(),39,length(l_xml.getClobVal()));
    dbms_output.put_line('XML DI RISPOSTA: ');
    dbms_output.put_line(l_val);
end;


though the response is:

Code: Select all

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Body>
    <GetGeoIPContextResponse xmlns="http://www.webservicex.net/">
      <GetGeoIPContextResult>
        <ReturnCode>1</ReturnCode>
        <IP>my ip</IP>
        <ReturnCodeDetails>Success</ReturnCodeDetails>
        <CountryName>European Union</CountryName>
        <CountryCode>EU</CountryCode>
      </GetGeoIPContextResult>
    </GetGeoIPContextResponse>
  </soap:Body>
</soap:Envelope>


How to do for extract the values??
I tested some solutions:
- https://forums.oracle.com/forums/thread ... dID=387277
and
- http://asitha.wordpress.com/2009/09/15/xmltype/

without success :(

For the complex types... i wait you for some suggestions!!
Thanks for your patience...

Cheers

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Some problem with web services and pl-sql

Postby Tim... » Wed May 30, 2012 4:32 pm

Hi.

There are several ways to get the data from the XML. You can see some examples here:

http://www.oracle-base.com/articles/9i/ ... nts-9i.php

If it's in an XMLTYPE, you can use the extract method shown here.

http://www.oracle-base.com/articles/9i/ ... tatype.php

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

Eremita
Member
Posts: 13
Joined: Fri May 25, 2012 2:49 pm

Re: Some problem with web services and pl-sql

Postby Eremita » Wed May 30, 2012 5:27 pm

Hi,

excellent!! :)
I solved in this way:

Code: Select all

declare
    l_env t_soap_envelope;
    l_xml xmltype;
    fitt  xmltype;
    l_val varchar2(4000);
    l_start_date date;
   
    mio_xml   XMLTYPE;
    mio_doc   CLOB;
    info1      VARCHAR2(4000);
    info2      VARCHAR2(4000);
    info3      VARCHAR2(4000); 
begin

    l_env := t_soap_envelope ('http://www.webservicex.net', 'geoipservice.asmx', 'GetGeoIP', 'xmlns="http://www.webservicex.net/"');
    l_env.add_param ('IPAddress', '10.1.1.138');
   
    l_start_date := sysdate;
    l_xml := flex_ws_api.make_request(p_url => l_env.service_url, p_action => l_env.soap_action, p_envelope => l_env.envelope);
   
    -- flex_ws_util.get_value() in questo caso non ci serve, ma:
    l_val := flex_ws_util.get_value (l_xml, 'GetGeoIPResponse', null, 'error');
   
    --flex_ws_util.log_request (l_env.service_url, l_env.service_method, l_env.envelope, l_xml, l_start_date,
    --    p_log_text => 'Converting 100 feet to meters', p_val1 => l_val);
   
    l_val := substr(l_xml.getClobVal(),39,length(l_xml.getClobVal()));
    dbms_output.put_line('XML DI RISPOSTA: ');
    --dbms_output.put_line(l_val);
   
    -- per estrarre i dati
    l_val := replace(l_val,':Envelope','');
    l_val := replace(l_val,'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"','');
    l_val := replace(l_val,'xmlns="http://www.webservicex.net/"','');
    l_val := replace(l_val,':Body','');
    dbms_output.put_line(l_val);
    mio_doc := l_val;
    mio_xml := sys.xmltype.createXML(mio_doc);
   
    SELECT extract(mio_xml,'//soap/soap/GetGeoIPResponse/GetGeoIPResult/IP/text()').getStringVal() into info1
    FROM   dual;
    SELECT extract(mio_xml,'//soap/soap/GetGeoIPResponse/GetGeoIPResult/CountryName/text()').getStringVal() into info2
    FROM   dual;
    SELECT extract(mio_xml,'//soap/soap/GetGeoIPResponse/GetGeoIPResult/CountryCode/text()').getStringVal() into info3
    FROM   dual;
    --WHERE  existsNode(mio_xml,'/soap:Envelope/soap:Body/GetGeoIPResult/CountryCode')  = 1;
   
    /*
    -- o anche
    SELECT extract(mio_xml,'//soap/soap/GetGeoIPResponse/GetGeoIPResult/IP/text()').getStringVal(),
           extract(mio_xml,'//soap/soap/GetGeoIPResponse/GetGeoIPResult/CountryName/text()').getStringVal(),
           extract(mio_xml,'//soap/soap/GetGeoIPResponse/GetGeoIPResult/CountryCode/text()').getStringVal()
           into info1, info2, info3
    FROM   dual;
    */
    dbms_output.put_line('IP: ' || info1);
    dbms_output.put_line('CountryName: ' || info2);
    dbms_output.put_line('CountryCode: ' || info3);
end;


Thanks much!!

Cheers

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Some problem with web services and pl-sql

Postby Tim... » Wed May 30, 2012 5:30 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 4 guests

cron