SQL & PL/SQL

Hi all,

I'm having to connect to a web service using pl/sql for the first time and things are going slow.

I found a function on the internet that i changed to connect to the web service and the code is as follows:

function get_account_name
(
pi_account_number in varchar2
)
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.1" 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">
<soapenv:Header/>
<SOAP-ENV:Body>
<xs:GetAccountName xmlns:xs="http://10.3.8.47/SOB_Account/Account.xamlx">
<xs:element name="GetAccountNameREQ">'||pi_account_number||'</xs:element>
</xs:GetAccountName>
</soap:Body>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>
';

http_req:= utl_http.begin_request
( 'http://10.3.8.47/SOB_Account/Account.xamlx'
, '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);
EXCEPTION
WHEN utl_http.end_of_body THEN
utl_http.end_response(http_resp);
utl_http.read_line(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('/soap:Envelope/soap:Body/child::node()'
, 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'
);
-- extract from the XMLType Resp the text() nodes from the n:getRateResponse/Result element
resp:= resp.extract('xs:GetAccountNameRPL/Result/text()','xmlns:xs="urn:xmethods-GetAccountName"');
dbms_output.put_line('12');
return resp.getClobVal();
end;



I'm assuming that the fault lies in the xml structure of the function as it seems to be able to connect but the error I'm getting is:

ORA-06503: PL/SQL: Function returned without value

I'm using Oracle 10g.

Any help or direction regarding this would be much appreciated.

Thank you

Edited by: britssteph on 10-Feb-2012 00:58