Oracel Send Email...

---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;