Re: problem sending long email message from oracle
- From: Christopher Boyle <cboyle@xxxxxxxxxxxxxxxxxxxxxx>
- To: ikar@xxxxxxxxxxxxxxx
- Date: Wed, 26 May 2010 11:40:23 -0400
I'm not sure if this is applicable but I use the procedure at this link
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1739411218448
to send largish generated html reports from within the database. It might
be adaptable.
On Wed, May 26, 2010 at 11:24 AM, 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
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
