Oracel Send Email...

wsql

---Oracel Send Email----

-- Create table

create table TB_EMAIL

(

ID NUMBER(10),

USER_NAME VARCHAR2(20),

EMAIL_ADDR VARCHAR2(100)

)

---Oracel Send Email----

declare

mail_conn utl_smtp.connection; --declare Mail Object

CRLF varchar2(2) := CHR( 13 ) || CHR( 10 );

v_message varchar2(255);

begin

-- open Mail Connection object

mail_conn := utl_smtp.open_connection('192.168.4.10');

-- Smtp Server

utl_smtp.helo(mail_conn, '192.168.4.10');

-- Set Send Address

utl_smtp.mail(mail_conn, 'admin@sql.dba.com');

-- Set Receive Address

declare email_addr tb_email.email_addr%type;

cursor Cur_Email is

select email_addr from tb_email;

begin

open Cur_Email;

loop

fetch cur_email into email_addr;

utl_smtp.rcpt(mail_conn,email_addr);

exit when cur_email%notFound;

end loop;

close cur_email;

end;

-- utl_smtp.rcpt(mail_conn, 'admin@sql.dba.com');

-- utl_smtp.rcpt(mail_conn, 'dba@sql.dba.com');

-- Define send context

utl_smtp.open_data(mail_conn);

v_message := 'Subject:Oracle Send Mail Test' ||

chr(13) || chr(10)

||'<table border="1" >'

||'<tr><td>Col1</td><td >Col2</td></tr>'

||'<tr><td>'

||'Hello'

||'</td><td >'

||'World !'

||'</td></tr></table>';

-- Write Mail Header

utl_smtp.write_raw_data( mail_conn, utl_raw.cast_to_raw('Subject:Oracle Send Mail Test' || CRLF) );

utl_smtp.write_data( mail_conn, 'MIME-Version: 1.0' || CRLF );

utl_smtp.write_data( mail_conn, 'Content-Type: text/html; charset=utf-8/big5' || CRLF );

utl_smtp.write_data( mail_conn, 'Content-Transfer-Encoding: 8bit' || CRLF );

-- Write Mail Body

utl_smtp.write_raw_data( mail_conn, utl_raw.cast_to_raw(v_message) );

-- close mail connect

utl_smtp.close_data(mail_conn);

utl_smtp.quit(mail_conn);

end;