problem sending long email message from oracle
- From: Inessa Weiner <ikar@xxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 26 May 2010 11:24:54 -0400
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:
