Skip to content
June 19, 2013 / doganay

HOW TO SEND MAIL FROM ORACLE DB


You can either use utl_mail or utl_smtp both.


Both of them creates an smtp package and send it to smtp host:

begin
execute immediate 'alter session set smtp_out_server = ''smtp_host:smtp_port''';
utl_mail.send(
sender => 'sender@sender_mail_address',
recipients => 'recipient@recipient_mail_address',
cc=> 'cc@cc',
bcc=> 'bcc@bcc',
subject => 'MAIL SUBJECT',
message => 'MAIL BODY',
mime_type => '');
end;
/


or use utl_mail:

declare
v_c UTL_SMTP.connection;
v_mailserver varchar2(40);
v_from VARCHAR2 (40);
v_to VARCHAR2 (40);
v_cc VARCHAR2 (40);
v_subject VARCHAR2 (40);
v_resp UTL_SMTP.reply;
begin
v_mailserver:='smtp_host';
v_from:='sender@sender_mail_address';
v_to:='recipient@recipient_mail_address';
v_cc:='cc@cc';
v_subject:='MAIL SUBJECT';
v_resp:=UTL_SMTP.open_connection (v_mailserver, c=>v_c);
v_resp:=UTL_SMTP.helo (v_c, 'helo_text');
v_resp:=UTL_SMTP.mail (v_c, v_to);
v_resp:=UTL_SMTP.rcpt(v_c, v_from);
v_resp:=UTL_SMTP.open_data(v_c);
UTL_SMTP.write_data(v_c, 'From: ' || v_from || UTL_TCP.crlf);
UTL_SMTP.write_data (v_c,'To: ' || v_to || UTL_TCP.crlf);
UTL_SMTP.write_data (v_c,'Cc: ' || v_cc || UTL_TCP.crlf);
UTL_SMTP.write_data (v_c,'Subject: ' || v_subject || UTL_TCP.crlf);
UTL_SMTP.write_data (v_c, 'Test Body');
v_resp:=UTL_SMTP.close_data(v_c);
UTL_SMTP.quit (v_c);
end;
/
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: