hancho
hancho

Reputation: 13

ORA-06512 numeric or value error when converting from clob to xmltype using APEX process

I have PLSQL function "ws_get_test" that returns SOAP response in CLOB format. I try to get response with PLSQL code in APEX page:

DECLARE
l_xml_response  CLOB;
l_xml XMLTYPE;
BEGIN
select ws_get_test(p_no => '12345' ) into l_xml_response from dual;   
l_xml XMLTYPE := XMLTYPE(l_xml_response);

I get error numeric or value error ORA-06512 on line of code: l_xml XMLTYPE := XMLTYPE(l_xml_response);

I dont get this error executing this process using sql developer.

This is code of ws_get_test function:

create or replace function ws_get_test(p_no varchar2)
return  clob
as
l_envelope CLOB;
l_xml XMLTYPE;
l_result clob;
begin
l_envelope := '<soapenv:Envelope xmlns:soapenv="http:schemas.xmlsoap.org/soap/envelope/" xmlns:wsei="http://wsei.test.com">

   <soapenv:Header/>

   <soapenv:Body>

      <wsei:CatchData>

         <wsei:lk_no>' || p_no ||  '</wsei:lk_no>

      </wsei:CatchData>

   </soapenv:Body>

</soapenv:Envelope>';

l_xml :=APEX_WEB_SERVICE.make_request(

p_url   => 'http://example.com/services/DS_CatchData',

p_action => 'urn:CatchData',

p_envelope => l_envelope

);
l_result := APEX_WEB_SERVICE.parse_xml_clob(
p_xml =>remove_namespace(l_xml),
p_xpath =>'/Envelope/Body/Data',
p_ns=> ''

);
return l_result;
END;

Upvotes: 0

Views: 42

Answers (0)

Related Questions