I am getting a response from Web Service, but unable to convert XML Response into proper value which can be returned in function.
In Block 1, an anonymous block is calling a web service, from which I am getting response ( attached in Block 2).
in Block 3, above block is wrapped in a function, which should return value ("1"- which is thr in xml) .
==========================================================
Block 1
==========================================================
declare
soap_request varchar2(30000);
soap_respond varchar2(30000);
http_req utl_http.req;
http_resp utl_http.resp;
resp XMLType;
i integer;
begin
soap_request:= '<?xml version="1.0" encoding="UTF-8"?>
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<SOAP-ENV:Body>
<ns0:initiateAdvRenewalRequestForBroadband xmlns:ns0="http://session.portal.project3.nib2.data.bsnl.india">
<ns0:pon>207623</ns0:pon>
</ns0:initiateAdvRenewalRequestForBroadband>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>';
http_req:= utl_http.begin_request
( 'http://10.10.10.28:10000/PortalEntAppPrepaidBBHttpRouter/services/SubInfoMngrPrepaidBB'
, 'POST'
, 'HTTP/1.1'
);
utl_http.set_header(http_req, 'Content-Type', 'text/xml'); -- since we are dealing with plain text in XML documents
utl_http.set_header(http_req, 'Content-Length', length(soap_request));
utl_http.set_header(http_req, 'SOAPAction', ''); -- required to specify this is a SOAP communication
utl_http.write_text(http_req, soap_request);
http_resp:= utl_http.get_response(http_req);
utl_http.read_text(http_resp, soap_respond);
utl_http.end_response(http_resp);
resp:= XMLType.createXML(soap_respond);
resp:= resp.extract('/soap:Envelop/soap:Body/child::node()'
, 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'
);
i:=0;
loop
dbms_output.put_line(substr(soap_respond,1+ i*255,250));
i:= i+1;
if i*250> length(soap_respond)
then
exit;
end if;
end loop;
end;
==========================================================
Block 2
==========================================================
--- XML Out Put :
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><soapenv:Header/>
env:Body><p123:initiateAdvRenewalRequestForBroadbandResponse xmlns:p123="http://session.portal.project3.nib2.data.bsnl.india"><initiateAdvRenewalRequestForBroadbandReturn>1</initiateAdvRenewalRequestForBroadbandReturn></p123:initiateAdvRenewalRequest
oadbandResponse></soapenv:Body></soapenv:Envelope>
==========================================================
Block 3
==========================================================
create or replace function get_conversion_rate_new
( p_country1 in number
/*, p_country2 in varchar2 default 'us'*/
)
return varchar2
as
soap_request varchar2(30000);
soap_respond varchar2(30000);
http_req utl_http.req;
http_resp utl_http.resp;
resp XMLType;
begin
soap_request:= '<?xml version = "1.0" encoding = "UTF-8"?>
<SOAP-ENV:Envelope
xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<SOAP-ENV:Body>
<ns1:initiateAdvRenewalRequestForBroadband xmlns:ns1="http://session.portal.project3.nib2.data.bsnl.india">
<country1 xsi:type="xsd:integer">'||p_country1||'</country1>
</ns1:initiateAdvRenewalRequestForBroadband>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
';
http_req:= utl_http.begin_request
( 'http://localhost:10000/PortalEntAppPrepaidBBHttpRouter/services/SubInfoMngrPrepaidBB'
, 'POST'
, 'HTTP/1.1'
);
utl_http.set_header(http_req, 'Content-Type', 'text/xml');
utl_http.set_header(http_req, 'Content-Length', length(soap_request));
utl_http.set_header(http_req, 'SOAPAction', '');
utl_http.write_text(http_req, soap_request);
http_resp:= utl_http.get_response(http_req);
utl_http.read_text(http_resp, soap_respond);
utl_http.end_response(http_resp);
-- Create an XMLType variable containing the Response XML
resp:= XMLType.createXML(soap_respond);
-- extract from the XMLType Resp the child-nodes of the <soap:Body> element
resp:= resp.extract('/soapenv:Envelope/env:Body/child::node()'
, 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'
);
return resp.getClobVal();
-- extract from the XMLType Resp the text() nodes from the n:getRateResponse/Result element
end;