XML webservice

[Srinivas_Sharma]

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.

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

RETURN RESP.GETCLOBVAL()

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

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

[Srinivas_Sharma]

somehow the soap_request string data is not displayed previously

posting the soap_request data.

[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]