How to Send Email From An Oracle Procedure?

Hi Ali,

Didn't quite understand!!! You only posted the answer also!!

 

create or replace procedure
   e_mail_message
(
   from_name varchar2,
   to_name varchar2,
   subject varchar2,
   message varchar2
)

is
  l_mailhost    VARCHAR2(64) := 'burleson.cc';

  l_from        VARCHAR2(64) := '[email protected]';

  l_to          VARCHAR2(64) := '[email protected]';

  l_mail_conn   UTL_SMTP.connection;

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, l_from);

  UTL_SMTP.rcpt(l_mail_conn, l_to);

 

  UTL_SMTP.open_data(l_mail_conn);

 

  UTL_SMTP.write_data(l_mail_conn, 'Date: '    || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || Chr(13));

  UTL_SMTP.write_data(l_mail_conn, 'From: '    || l_from || Chr(13));

  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || l_subject || Chr(13));

  UTL_SMTP.write_data(l_mail_conn, 'To: '      || l_to || Chr(13));

  UTL_SMTP.write_data(l_mail_conn, ''          || Chr(13));

 

  FOR i IN 1 .. 10 LOOP

    UTL_SMTP.write_data(l_mail_conn, 'This is a test message. Line ' || To_Char(i) || Chr(13));

  END LOOP;

 

  UTL_SMTP.close_data(l_mail_conn);

 

  UTL_SMTP.quit(l_mail_conn);

END;

As of Oracle 8.1, you can now send email from within an Oracle application. This is done by using the UTL_SMTP package. However, it should be noted that there are some shortcomings with this package that may or may not be a problem for you:

for details visit.
http://www.globalguideline.com/db