Oracle PL/SQL Tutorial/System Packages/utl smtp

send email

SQL> CREATE OR REPLACE PROCEDURE p_send_email
  2                              (p_mailhost VARCHAR2,
  3                               p_from_address VARCHAR2,
  4                               p_to_address VARCHAR2,
  5                               p_message_text VARCHAR2,
  6                               p_returnCode OUT NUMBER,
  7                               p_err_msg OUT VARCHAR2)
  8  IS
  9    mail_conn utl_smtp.connection;
 10  BEGIN
 11    mail_conn :=utl_smtp.open_connection(p_mailhost,25);
 12    utl_smtp.helo(mail_conn,p_mailhost);
 13    utl_smtp.mail(mail_conn,p_from_address);
 14    utl_smtp.rcpt(mail_conn,p_to_address);
 15    utl_smtp.open_data(mail_conn);
 16    utl_smtp.write_data(mail_conn,p_message_text);
 17    utl_smtp.close_data(mail_conn);
 18    utl_smtp.quit(mail_conn);
 19    p_returnCode :=0;
 20  EXCEPTION
 21    WHEN OTHERS THEN
 22      p_returnCode :=SQLCODE;
 23      p_err_msg :=SQLERRM;
 24  END;
 25  /
SQL>
SQL> -- Sample code to execute p_send_email
SQL> declare
  2    returnCode number;
  3    err_msg varchar2(1000);
  4  begin
  5    p_send_email("mail.c.ru",
  6                 "b@c.ru",
  7                 "b@c.ru",
  8                 "From:"||"B"||CHR(13)||CHR(10)||
  9                 "Subject:"||"Message from B"||CHR(13)||CHR(10)||
 10                 CHR(13)||CHR(10)||
 11                 "This is a test message.",
 12                 returnCode,
 13                 err_msg);
 14    IF (returnCode <>0)THEN
 15      dbms_output.put_line("ERR:"||err_msg);
 16    END IF;
 17  end;
 18  /
SQL>

Send out email with utl_smtp

SQL>
SQL>
SQL> set echo on
SQL>
SQL> create or replace
  2  PROCEDURE send_mail (p_sender    IN VARCHAR2,
  3                       p_recipient IN VARCHAR2,
  4                       p_message   IN VARCHAR2)
  5  as
  6     l_mailhost VARCHAR2(255) := "mailhost.yourServer.ru";
  7     l_mail_conn utl_smtp.connection;
  8  BEGIN
  9     l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
 10     utl_smtp.helo(l_mail_conn, l_mailhost);
 11     utl_smtp.mail(l_mail_conn, p_sender);
 12     utl_smtp.rcpt(l_mail_conn, p_recipient);
 13     utl_smtp.open_data(l_mail_conn );
 14     utl_smtp.write_data(l_mail_conn, p_message);
 15     utl_smtp.close_data(l_mail_conn );
 16     utl_smtp.quit(l_mail_conn);
 17  end;
 18  /
Warning: Procedure created with compilation errors.
SQL>