Home » SQL & PL/SQL » SQL & PL/SQL » utl_http.read_text : ORA-29266: end-of-body reached (Oracle 10g)  () 1 Vote
- utl_http.read_text : ORA-29266: end-of-body reached [message #450947] Sun, 11 April 2010 07:58 Go to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
Dear All,

I am getting the error (ORA-29266: end-of-body reached) at utl_http.read_text. Anybody know what is issue here?

below is a code

create or replace procedure sendMail(p_fromaddress IN VARCHAR2, --  Optional
                                               p_toaddress   IN VARCHAR2,
                                               p_bcc         IN VARCHAR2, --  Optional
                                               p_cc          IN VARCHAR2, --  Optional
                                               p_subject     IN VARCHAR2,
                                               p_contentType IN VARCHAR2, --  Optional
                                               p_msgbody     IN CLOB,
                                               p_appid       IN VARCHAR2,
                                               p_username    IN VARCHAR2,
                                               p_password    IN VARCHAR2,
                                               p_environment IN VARCHAR2, -- optional 'P' for Prod 'S' for Stggaing
                                               p_result      OUT VARCHAR2
                                               
                                               )

 is

  l_fromaddress    VARCHAR2(4000);
  l_plaindata      CLOB;
  l_subject        VARCHAR2(10000);
  l_toaddress      VARCHAR2(4000);
  l_contentType    VARCHAR2(1000);
  l_appid          VARCHAR2(50);
  l_username       VARCHAR2(50);
  l_password       VARCHAR2(50);
  l_bcc            VARCHAR2(2000);
  l_cc             VARCHAR2(2000);
  l_target_url     VARCHAR2(1000);
  l_error          VARCHAR2(500);
  l_correlation_id VARCHAR2(500);

  l_soap_request  CLOB;
  l_soap_response varchar2(32767);

  buffersize          NUMBER := 32000;
  chunk_buffer        VARCHAR2(32000);
  chunk_length        NUMBER;
  chunk_offset        NUMBER;
  request_body_length NUMBER;

  http_req  utl_http.req;
  http_resp utl_http.resp;
begin
  if p_contentType is NULL then
    l_contentType := 'text/plain;charset=UTF-8';
  else
    l_contentType := p_contentType;
  end if;

  l_fromaddress := p_fromaddress;
  l_plaindata   := p_msgbody;
  l_subject     := p_subject;
  l_toaddress   := p_toaddress;
  l_appid       := p_appid;
  l_username    := p_username;
  l_password    := p_password;
  l_bcc         := p_bcc;
  l_cc          := p_cc;

  if p_environment = 'P' then
    l_target_url := 'http://server1:8080/Process/PD-EmailNotifyService';
  else
    l_target_url := 'http://server2:8080/Process/PD-EmailNotifyService';
  end if;

  l_soap_request := '<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:mail="http://www.tibco.com/schemas/TibcoESB/CITP/MailMessage.xsd">
  <soap:Header/>
     <soap:Body>
      <mail:input_Mail_Type>
         <mail:Bcc>' || l_bcc || '</mail:Bcc>
         <mail:cc>' || l_cc || '</mail:cc>
         <mail:From>' || l_fromaddress ||
                    '</mail:From>
         <mail:plainData><![CDATA[' || l_plaindata ||
                    ']]></mail:plainData>
         <mail:subject>' || l_subject ||
                    '</mail:subject>
         <mail:To>' || l_toaddress ||
                    '</mail:To>
         <mail:contentType>' || l_contentType ||
                    '</mail:contentType>
         <mail:app_id>' || l_appid ||
                    '</mail:app_id>
         <mail:username>' || l_username ||
                    '</mail:username>
         <mail:password>' || l_password ||
                    '</mail:password>
      </mail:input_Mail_Type>
   </soap:Body>
 </soap:Envelope> ';

  -- DBMS_OUTPUT.put_line(l_soap_request);
  DBMS_OUTPUT.put_line(length(l_soap_request));

  http_req := utl_http.begin_request(l_target_url, 'POST', 'HTTP/1.1');

  utl_http.set_header(http_req, 'Content-Type', 'text/xml; charset=utf-8');
  -- added the below header to fix mail send 32kb issue
  utl_http.set_header(http_req, 'Transfer-Encoding', 'chunked');
  utl_http.set_header(http_req,
                      'SOAPAction',
                      '/CITP/Mail Notification/EmailNotifyService');
  -- added the below code to fix                       
  request_body_length := dbms_lob.getlength(l_soap_request);
  chunk_offset        := 1;
  while (chunk_offset < request_body_length) loop
    if chunk_offset + buffersize >= request_body_length then
      chunk_length := request_body_length - chunk_offset;
    else
      chunk_length := buffersize;
    end if;
    dbms_lob.read(l_soap_request, chunk_length, chunk_offset, chunk_buffer);
    
    utl_http.write_text(http_req, chunk_buffer);
    chunk_offset := chunk_offset + chunk_length;
    --DBMS_OUTPUT.put_line(chunk_buffer);
  end loop;
  -- utl_http.write_text(http_req, l_soap_request);
  -- the actual call to the service is made here
  http_resp := utl_http.get_response(http_req);
  
  [color=red]utl_http.read_text(http_resp, l_soap_response, 32767);[/color]
  utl_http.end_response(http_resp);  
 

  l_error := SYS.XMLTYPE.EXTRACT(xmltype(l_soap_response),'//ns0:error/text()','xmlns:ns0="' ||'http://www.tibco.com/schemas/TibcoESB/CITP/MailMessage.xsd' ||'"')
             .getstringval();

  p_result := SYS.XMLTYPE.EXTRACT(xmltype(l_soap_response),'//ns0:result/text()','xmlns:ns0="' ||'http://www.tibco.com/schemas/TibcoESB/CITP/MailMessage.xsd' ||'"')
              .getstringval();

  l_correlation_id := SYS.XMLTYPE.EXTRACT(xmltype(l_soap_response),'//ns0:correlation_id/text()','xmlns:ns0="' ||'http://www.tibco.com/schemas/TibcoESB/CITP/MailMessage.xsd' ||'"')
                      .getstringval();

  dbms_output.put_line(p_result || ' ' || l_error || ' ' ||
                       l_correlation_id);
  --p_result := l_soap_response;

EXCEPTION
  WHEN OTHERS THEN

    p_result := 'Exception  ' || chr(13) || SQLERRM;
end sendMail;


thanks,

[Updated on: Tue, 20 April 2010 01:37] by Moderator

Report message to a moderator

- Re: utl_http.read_text : ORA-29266: end-of-body reached [message #450953 is a reply to message #450947] Sun, 11 April 2010 11:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
ORA-29266: end-of-body reached
 *Cause:  The end of the HTTP response body was reached.
 *Action: If the end of the HTTP response is reached prematurely, check if
          the HTTP response terminates prematurely.  Otherwise, end the
          HTTP response.

Regards
Michel
- Re: utl_http.read_text : ORA-29266: end-of-body reached [message #450962 is a reply to message #450947] Sun, 11 April 2010 13:22 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
@Michel : this is the only answer i got from every where.. but really couldnt able to resolve this issue...

as i mentioned it is hapening when reading the text.

utl_http.read_text(http_resp, l_soap_response, 32767);

i tried to catch the exception and ending the response but still didnt get the required results which is to send the email.

this is happening only when send more then 32767 bytes in email.

any idea how to resolve it?
- Re: utl_http.read_text : ORA-29266: end-of-body reached [message #450963 is a reply to message #450962] Sun, 11 April 2010 13:31 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>this is happening only when send more then 32767 bytes in email.
PL/SQL VARCHAR2 variables are limited to 32767.
If messages will exceed 32767, then CLOB is required to hold them.
- Re: utl_http.read_text : ORA-29266: end-of-body reached [message #450975 is a reply to message #450947] Sun, 11 April 2010 23:32 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
even i am trying with clob at :

l_soap_response varchar2(32767);


Changed like
l_soap_response clob;


but still getting the same error:(
- Re: utl_http.read_text : ORA-29266: end-of-body reached [message #450988 is a reply to message #450947] Mon, 12 April 2010 00:21 Go to previous messageGo to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
Guys, i guess i got the issue :

problem is with the data itself,

when i send the following data in the email i got the issue:

Edit MC: Data removed at OP's request.



without this data i tried with 4mb of data and email was sent.
any idea about this?

[Updated on: Mon, 19 April 2010 12:53] by Moderator

Report message to a moderator

- Re: utl_http.read_text : ORA-29266: end-of-body reached [message #451002 is a reply to message #450947] Mon, 12 April 2010 01:28 Go to previous message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
guys issue was due to the CDATA tag was conficting in soap body and email data.

thanks
Previous Topic: truncate vs delete
Next Topic: sql
Goto Forum:
  


Current Time: Wed Jun 11 11:22:12 CDT 2025