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.com',
6 'b@c.com',
7 'b@c.com',
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>