Oracle PL/SQL Tutorial/System Packages/utl smtp

send email

   <source lang="sql">

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></source>

Send out email with utl_smtp

   <source lang="sql">

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></source>