Re: problem sending long email message from oracle

1000 is also the default display size for a long text field - I wonder if
thats it?

On 26 May 2010 16:24, Inessa Weiner <ikar@xxxxxxxxxxxxxxx> wrote:

> I am having problem sending our emails from oracle stored procedure (using
> utl_smtp package).
> For a large email messages, message body is getting truncated to 1000
> characters. I am on 10gR2.
>
> Here is my procedure:
> PROCEDURE email ( p_sender in varchar2,
>                              p_recipient in varchar2,
>                              p_subject in varchar2,
>                              p_body in varchar2 ) is
>  l_mailhost    varchar2(30) := 'my.host.com';
>  l_mailconn   utl_smtp.connection;
>  l_mesg        varchar2(6000);
>
>  BEGIN
>    l_mailconn := utl_smtp.open_connection(l_mailhost, 25);
>
>    l_mesg := 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) ||
> chr(13) || chr(10) ||
>          'From: '||p_sender|| chr(13) || chr(10) ||
>          'Subject: '||p_subject|| chr(13) || chr(10) ||
>          'To: '||p_recipient|| chr(13) || chr(10) ||
>          'Content-type: text/plain'|| chr(13) || chr(10) ||
>          '' || chr(13) || chr(10) || p_body;
>
>   utl_smtp.helo(l_mailconn, mailhost);
>   utl_smtp.mail(l_mailconn, p_sender);
>   utl_smtp.rcpt(l_mailconn, p_recipient);
>   utl_smtp.data(l_mailconn, l_mesg);
>   utl_smtp.quit(l_mailconn);
>
>  END email;
> /
> ---
> Here is how I am trying to execute the procedure from sql plus:
>
> DECLARE
> P_SENDER VARCHAR2(32767);
> P_RECIPIENT VARCHAR2(32767);
> P_SUBJECT VARCHAR2(32767);
> P_BODY VARCHAR2(32767);
>
> BEGIN
> P_SENDER := 'xxxx@yyy';
> P_RECIPIENT := 'xxxx@yyy';
> P_SUBJECT := 'long text message';
> P_BODY :=
>
> '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
>
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
>
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
>
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
>
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
>
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
>
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
> 7
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
> 8
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
> 9
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
> 10
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
> 11
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
> 12
> 34567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
>
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
>
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
>
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
>
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
>
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
>
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
>
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
>
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
>
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
>
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
> 21
> 123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789';
>
> EMAIL ( P_SENDER, P_RECIPIENT, P_SUBJECT, P_BODY );
> COMMIT;
> END;
> /
> ---
>
> SQL> @long_sp;
>
> PL/SQL procedure successfully completed.
> --
>
> The email message is:
> long text message
> xxx@yyyy
> Sent: Tuesday, May 25, 2010 6:52 AM
> To:
> xxx@yyyy
>
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
> 7
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
> 8
> 1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890
> 9 12345678901234567890123456789012345678901234567890123456789012345678901234
>
>
>
>
> Does anybody had similar problems or have any ideas how to fix?
> Thanks!!
> Ina
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>


-- 
Howard A. Latham

Other related posts: