Home » SQL & PL/SQL » SQL & PL/SQL » problem in sending email from UTL_SMTP pkg (oracle 9i)
problem in sending email from UTL_SMTP pkg [message #415116] Sat, 25 July 2009 00:33 Go to next message
npk_7
Messages: 5
Registered: July 2009
Location: mumbai
Junior Member
Hi,
I am facing one problem while sending e-mail using UTL_SMTP package.Problem is if the message text is large it breaks in middle by (!) sign
if the message text is small it goes properly.
I am passing a string of varchar2(4000) as a message text to the procedure I created.
Please help me to solve the issue.

npk_7
Re: problem in sending email from UTL_SMTP pkg [message #415117 is a reply to message #415116] Sat, 25 July 2009 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fix the code.


Regards
Michel
Re: problem in sending email from UTL_SMTP pkg [message #415118 is a reply to message #415117] Sat, 25 July 2009 00:44 Go to previous message
npk_7
Messages: 5
Registered: July 2009
Location: mumbai
Junior Member
Here is the code

CREATE OR REPLACE procedure multi_mails_send
(v_from_mail_id varchar2,
v_to_mail_id varchar2,
v_mssg varchar2,
v_subject varchar2) is
l_mailhost VARCHAR2(64) := 'localhost';
l_mail_conn UTL_SMTP.connection;
v_mail_mssg varchar2(4000);
v_to_mail_string varchar2(3000);
v_to_mail varchar2(100);

BEGIN
l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
UTL_SMTP.helo(l_mail_conn, l_mailhost);
UTL_SMTP.mail(l_mail_conn, v_from_mail_id);

v_to_mail_string := v_to_mail_id;
while not instr(v_to_mail_string,',') = 0 loop
v_to_mail := substr(v_to_mail_string,1,instr(v_to_mail_string,',')-1);
v_to_mail_string := substr(v_to_mail_string,instr(v_to_mail_string,',')+1);
UTL_SMTP.rcpt(l_mail_conn, v_to_mail);
end loop;


UTL_SMTP.open_data(l_mail_conn);

UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR((sysdate-5/24-30/1440), 'DD-MON-YYYY HH24:MI:SS') || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'From: ' || v_from_mail_id || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || v_subject || Chr(13));
UTL_SMTP.write_data(l_mail_conn, 'To: ' || substr(v_to_mail_id,1,length(v_to_mail_id)-1) || Chr(13));
UTL_SMTP.write_data(l_mail_conn, '' || Chr(13));

v_mail_mssg := v_mssg;

UTL_SMTP.write_data(l_mail_conn, v_mail_mssg );


UTL_SMTP.close_data(l_mail_conn);

UTL_SMTP.quit(l_mail_conn);
exception
when others then
null;

END;
/

here I am passing the parameter to this procedure from form 6i


Previous Topic: How to retrieve duplicate records from a table [merged]
Next Topic: A question about eliminate duplicates rows from a table
Goto Forum:
  


Current Time: Fri Dec 09 00:18:37 CST 2016

Total time taken to generate the page: 0.12496 seconds