XML webservice

Hi

I m calling a webservice from from pl/sql

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production

I have the code in a package and when i call it from pl/sql it fails, giving the following error.

Quote:

---

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "XXHCC_ABC_CRM", line 571

---

and my line 571 is

Quote:

---

RETURN RESP.GETCLOBVAL()

---

The webservice works fine when called directly from browser. my function is like this.

Quote:

---

FUNCTION GETENQUIRYSTATUSCHANGES RETURN VARCHAR2 AS

SOAP_REQUEST VARCHAR2(30000);
L_XML VARCHAR2(30000);
SOAP_RESPOND VARCHAR2(30000);
HTTP_REQ UTL_HTTP.REQ;
HTTP_RESP UTL_HTTP.RESP;
RESP XMLTYPE;
I INTEGER;

BEGIN

L_XML:='';
L_XML:= L_XML || '';
L_XML:= L_XML || '' || 'TEST' || '';
L_XML:= L_XML || '' || '';
L_XML:= L_XML || '' || 'Test' ||'';
L_XML:= L_XML || '';
L_XML:= L_XML || '';
L_XML:= L_XML || '<'||'GetEnquiryStatusChanges'||'>';

L_XML:= L_XML || '2007-11-13T14:45:35';

L_XML:= L_XML || '2007-11-15T14:45:35';

L_XML:= L_XML || '';
L_XML:= L_XML || '';
L_XML:= L_XML || '';

SOAP_REQUEST:= '



'||L_XML||'


';

HTTP_REQ:= UTL_HTTP.BEGIN_REQUEST
( 'http://hcc-065/ConnectorWebService/Connector.asmx'
, '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', '"http://www.abc.co.uk/schema/am/connector/webservice/ProcessOperations"'); -- 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);

IF RESP IS NULL THEN
DBMS_OUTPUT.PUT_LINE('NOTHING IN PAYLOAD');
ELSE
RESP:= RESP.EXTRACT('/soap:Envelope/soap:Body/child::node()','xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/');
RESP:= RESP.EXTRACT('//GetEnquiryStatusChangesResponse');
RETURN RESP.GETCLOBVAL();
END IF;

END GETENQUIRYSTATUSCHANGES;

---

I think its to do with GetCLOBVAL function, can someone point me in the right direction and let me know if this is the best way of retrieving data.

Srini

somehow the soap_request string data is not displayed previously

posting the soap_request data.

HTML Code:

---

[QUOTE]SOAP_REQUEST:= <?xml version = "1.0" encoding = "UTF-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
    <ProcessOperationsRequest xmlns="http://www.confirm.co.uk/schema/am/connector/webservice">
      '||L_XML||'
    </ProcessOperationsRequest>
  </soap:Body>
</soap:Envelope>;[/QUOTE]

---