Wednesday, April 18, 2012

Send -email from Oracle Database Using smtp and G-mail account free


There are many tools which can be used to send mail using smtp, but Stunnel is quite easy to install and configure.

Installing and configuring Stunnel
  • Go to stunnel.org and download the latest Windows binaries
  • Install Stunnel ;Remember path.  In my example it is c:\Programe Files\stunnel
  • Edit the file stunnel.conf located in installation folder to (just backup the original, and replace all the original text with the text below):
; Use it for client mode
client = yes
 
[ssmtp]
accept  = 1925
connect = smtp.gmail.com:465
Here I use port 1925 on my localhost (unused as far as I know) to connect to smtp.gmail.com.

Start Stunnel.exe, and test the configuration:
  • Start cmd
  • Write: telnet localhost 1925
  • You should then see something like "220 mx.google.com ESMTP 5sm18031572eyh.34"
  • Write: quit

Troubleshooting: If you cannot reach smtp.gmail.com, there can be any number of things gone wrong.
  • Try a normal ping to smtp.gmail.com
  • Check to see if stunnel.exe is excepted properly in all firewalls (Windows native and other software firewalls)

Once stunnel is working, and if you are familiar with UTL_SMTP, don't bother reading on. This is the same as UTL_SMTP with any other smtp-host requiring authentication

To create an access control list for your application user, and enabling it to connect to localhost on port 1925, do the following :

-- create acl  
 begin
            dbms_network_acl_admin.create_acl (
                    acl             => 'gmail.xml',
                   description     => 'Normal Access',
                    principal       => 'CONNECT',
                    is_grant        => TRUE,
                    privilege       => 'connect',
                    start_date      => null,
                    end_date        => null
           );
    end;
   
   
   
    -- add priviliege to acl
    begin
              dbms_network_acl_admin.add_privilege (
              acl       => 'gmail.xml',
              principal    => 'ORBABG',
              is_grant    => TRUE,
              privilege    => 'connect',
              start_date    => null,
              end_date    => null);
            end;
   
   
   
    -- assign host, port to acl
            begin
              dbms_network_acl_admin.assign_acl (
              acl => 'gmail.xml',
              host => '192.168.240.12',
              lower_port => 1925,
              upper_port => 1925);
            end;



 After That create the table from the following script or from  following script :
**********************From Here ***************************
 CREATE TABLE SMTPTST
(
  CAGRPCDE  VARCHAR2(3 BYTE),
  CACMPCDE  VARCHAR2(3 BYTE),
  SENDRADD  VARCHAR2(500 BYTE) ,
  SENDRPAS  VARCHAR2(500 BYTE),
  LCLSMTPH  VARCHAR2(500 BYTE),
  LCLSMTPP  INTEGER,
  SMTPDOMN  VARCHAR2(200 BYTE)
)
TABLESPACE NANO
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON COLUMN SMTPTST.SENDRPAS IS 'e-mail sender e-mails password for authentication in server ( Its mostly needed in gmail server)';

COMMENT ON COLUMN SMTPTST.LCLSMTPH IS 'Local smtp IP address, where stunnel is used. Generally for sending e-mail we install stunnel on database';

COMMENT ON COLUMN SMTPTST.LCLSMTPP IS 'Local smtp port, where stunnel is used. Generally for sending e-mail we install stunnel on database';

COMMENT ON COLUMN SMTPTST.SMTPDOMN IS 'server domain like gmail.com; if smtp.gmail.com, then gmail.com will be the value of this field';

COMMENT ON COLUMN SMTPTST.SENDRADD IS 'E-mail Sender Address';
**********************Upto  Here ***************************

Insert Example Data by using Example Data  by following script

**************************Start here***************
SET DEFINE OFF;
Insert into SMTPTST
   (CAGRPCDE, CACMPCDE, SENDRADD, SENDRPAS, LCLSMTPH, LCLSMTPP, SMTPDOMN)
 Values
   ('001', '154', 'akter.babu@gmail.com', '<your mail password>', '192.168.75.232', 1925, 'gmail.com');
Insert into SMTPTST
   (CAGRPCDE, CACMPCDE, SENDRADD, SENDRPAS, LCLSMTPH, LCLSMTPP, SMTPDOMN)
 Values
   ('001', '184', 'akter.babu@gmail.com', '<your mail password>', '192.168.75.232', 1925, 'gmail.com');
Insert into SMTPTST
   (CAGRPCDE, CACMPCDE, SENDRADD, SENDRPAS, LCLSMTPH, LCLSMTPP, SMTPDOMN)
 Values
   ('001', '343', 'akter.babu@gmail.com', '<your mail password>', '192.168.75.232', 1925, 'gmail.com');
Insert into SMTPTST
   (CAGRPCDE, CACMPCDE, SENDRADD, SENDRPAS, LCLSMTPH, LCLSMTPP, SMTPDOMN)
 Values
   ('001', '344', 'akter.babu@gmail.com', '<your mail password>', '192.168.75.232', 1925, 'gmail.com');
Insert into SMTPTST
   (CAGRPCDE, CACMPCDE, SENDRADD, SENDRPAS, LCLSMTPH, LCLSMTPP, SMTPDOMN)
 Values
   ('001', '342', 'akter.babu@gmail.com', '<your mail password>', '192.168.75.232', 1925, 'gmail.com');
Insert into SMTPTST
   (CAGRPCDE, CACMPCDE, SENDRADD, SENDRPAS, LCLSMTPH, LCLSMTPP, SMTPDOMN)
 Values
   ('001', '044', 'akter.babu@gmail.com', '<your mail password>', '192.168.75.232', 1925, 'gmail.com');
COMMIT;
****************Up to here*****************************************


Create Package using package script named DATABASE_MAIL.pks for package header and DATABASE_MAIL.pkb for Body.
 ****************Start DATABASE_MAIL.pks**********************
 CREATE OR REPLACE PACKAGE ORBABG.Database_mail IS
 
  ----------------------- Customizable Section -----------------------
 
  -- Customize the SMTP host, port and your domain name below.
  sender_mail VARCHAR2(256);
  sender_pass VARCHAR2(256);
  smtp_host   VARCHAR2(256);-- := '192.168.75.232';
  smtp_port   PLS_INTEGER ;--  := 1925;
  smtp_domain VARCHAR2(256);-- := 'gmail.com';
  smtp_recepient VARCHAR2(4000);
  smtp_cc VARCHAR2(4000);
  smtp_bcc VARCHAR2(4000);
  smtp_bcc_otner VARCHAR2(1);--O for To and CC and B for Bcc only

  -- Customize the signature that will appear in the email's MIME header.
  -- Useful for versioning.
  MAILER_ID   CONSTANT VARCHAR2(256) := 'Mailer by Orbits UTL_SMTP';
 
  --------------------- End Customizable Section ---------------------

  -- A unique string that demarcates boundaries of parts in a multi-part email
  -- The string should not appear inside the body of any part of the email.
  -- Customize this if needed or generate this randomly dynamically.
  BOUNDARY        CONSTANT VARCHAR2(256) := '-----7D81B75CCC90D2974F7A1CBD';

  FIRST_BOUNDARY  CONSTANT VARCHAR2(256) := '--' || BOUNDARY || utl_tcp.CRLF;
  LAST_BOUNDARY   CONSTANT VARCHAR2(256) := '--' || BOUNDARY || '--' ||
                                              utl_tcp.CRLF;

  -- A MIME type that denotes multi-part email (MIME) messages.
  MULTIPART_MIME_TYPE CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="'||
                                                  BOUNDARY || '"';
  MAX_BASE64_LINE_WIDTH CONSTANT PLS_INTEGER   := 76 / 4 * 3;

  -- A simple email API for sending email in plain text in a single call.
  -- The format of an email address is one of these:
  --   someone@some-domain
  --   "Someone at some domain" <someone@some-domain>
  --   Someone at some domain <someone@some-domain>
  -- The recipients is a list of email addresses  separated by
  -- either a "," or a ";"
 
  --For Getting SMTP Host which is database        
 
  PROCEDURE mail(
         recipients IN VARCHAR2,
         cc         in Varchar2 DEFAULT NULL,
         bcc        In Varchar2 DEFAULT NULL,
         subject    IN VARCHAR2,
         iSender    In VARCHAR2,
         iSenderPass    In VARCHAR2,
         ismtp_host    In VARCHAR2,
         ismtp_port    In pls_integer,
         ismtp_domain    In VARCHAR2,
         message    IN VARCHAR2,
         i_bcc_other In varchar2);

  -- Extended email API to send email in HTML or plain text with no size limit.
  -- First, begin the email by begin_mail(). Then, call write_text() repeatedly
  -- to send email in ASCII piece-by-piece. Or, call write_mb_text() to send
  -- email in non-ASCII or multi-byte character set. End the email with
  -- end_mail().
  FUNCTION begin_mail(
              recipients IN VARCHAR2,
              cc         IN VARCHAR2 DEFAULT NULL,
              bcc        IN VARCHAR2 DEFAULT NULL,
              subject    IN VARCHAR2,
              mime_type  IN VARCHAR2    DEFAULT 'text/plain',
              priority   IN PLS_INTEGER DEFAULT NULL,
              iSender    IN VARCHAR2,
              iSenderPass IN VARCHAR2,
              ismtp_host IN VARCHAR2,
              ismtp_port IN PLS_INTEGER  ,
              ismtp_domain  IN VARCHAR2    ,
              i_bcc_other In varchar2            
              )
              RETURN utl_smtp.connection;

  -- Write email body in ASCII
  PROCEDURE write_text(conn    IN OUT NOCOPY utl_smtp.connection,
               message IN VARCHAR2);

  -- Write email body in non-ASCII (including multi-byte). The email body
  -- will be sent in the database character set.
  PROCEDURE write_mb_text(conn    IN OUT NOCOPY utl_smtp.connection,
              message IN            VARCHAR2);
 
  -- Write email body in binary
  PROCEDURE write_raw(conn    IN OUT NOCOPY utl_smtp.connection,
              message IN RAW);

  -- APIs to send email with attachments. Attachments are sent by sending
  -- emails in "multipart/mixed" MIME format. Specify that MIME format when
  -- beginning an email with begin_mail().
 
  -- Send a single text attachment.
  PROCEDURE attach_text(conn         IN OUT NOCOPY utl_smtp.connection,
            data         IN VARCHAR2,
            mime_type    IN VARCHAR2 DEFAULT 'text/plain',
            inline       IN BOOLEAN  DEFAULT TRUE,
            filename     IN VARCHAR2 DEFAULT NULL,
                last         IN BOOLEAN  DEFAULT FALSE);
 
  -- Send a binary attachment. The attachment will be encoded in Base-64
  -- encoding format.
  PROCEDURE attach_base64(conn         IN OUT NOCOPY utl_smtp.connection,
              data         IN RAW,
              mime_type    IN VARCHAR2 DEFAULT 'application/octet',
              inline       IN BOOLEAN  DEFAULT TRUE,
              filename     IN VARCHAR2 DEFAULT NULL,
              last         IN BOOLEAN  DEFAULT FALSE);
 
  -- Send an attachment with no size limit. First, begin the attachment
  -- with begin_attachment(). Then, call write_text repeatedly to send
  -- the attachment piece-by-piece. If the attachment is text-based but
  -- in non-ASCII or multi-byte character set, use write_mb_text() instead.
  -- To send binary attachment, the binary content should first be
  -- encoded in Base-64 encoding format using the this package for 8i,
  -- or the native one in 9i. End the attachment with end_attachment.
  PROCEDURE begin_attachment(conn         IN OUT NOCOPY utl_smtp.connection,
                 mime_type    IN VARCHAR2 DEFAULT 'text/plain',
                 inline       IN BOOLEAN  DEFAULT TRUE,
                 filename     IN VARCHAR2 DEFAULT NULL,
                 transfer_enc IN VARCHAR2 DEFAULT NULL);
 
  -- End the attachment.
  PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection,
               last IN BOOLEAN DEFAULT FALSE);
 
  -- End the email.
  PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection);

  -- Extended email API to send multiple emails in a session for better
  -- performance. First, begin an email session with begin_session.
  -- Then, begin each email with a session by calling begin_mail_in_session
  -- instead of begin_mail. End the email with end_mail_in_session instead
  -- of end_mail. End the email session by end_session.
  FUNCTION begin_session RETURN utl_smtp.connection;
 
  -- Begin an email in a session.
  PROCEDURE begin_mail_in_session(conn       IN OUT NOCOPY utl_smtp.connection,               
                  subject    IN VARCHAR2,
                  mime_type  IN VARCHAR2  DEFAULT 'text/plain',
                  priority   IN PLS_INTEGER DEFAULT NULL);
 
  -- End an email in a session.
  PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection);
 
  -- End an email session.
  PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection);

END;
/
**************End Of DATABASE_MAIL.pks****************

**************DataBase_MAil.pkb ***********
 CREATE OR REPLACE PACKAGE BODY ORBABG.Database_mail IS
 
  -- Return the next email address in the list of email addresses, separated
  -- by either a "," or a ";".  The format of mailbox may be in one of these:
  --   someone@some-domain
  --   "Someone at some domain" <someone@some-domain>
  --   Someone at some domain <someone@some-domain>
  FUNCTION get_address(addr_list IN OUT VARCHAR2) RETURN VARCHAR2 IS

    addr VARCHAR2(256);
    i    pls_integer;

    FUNCTION lookup_unquoted_char(str  IN VARCHAR2,
                  chrs IN VARCHAR2) RETURN pls_integer AS
      c            VARCHAR2(5);
      i            pls_integer;
      len          pls_integer;
      inside_quote BOOLEAN;
    BEGIN
       inside_quote := false;
       i := 1;
       len := length(str);
       WHILE (i <= len) LOOP

     c := substr(str, i, 1);

     IF (inside_quote) THEN
       IF (c = '"') THEN
         inside_quote := false;
       ELSIF (c = '\') THEN
         i := i + 1; -- Skip the quote character
       END IF;
       GOTO next_char;
     END IF;
   
     IF (c = '"') THEN
       inside_quote := true;
       GOTO next_char;
     END IF;
    
     IF (instr(chrs, c) >= 1) THEN
        RETURN i;
     END IF;
    
     <<next_char>>
     i := i + 1;

       END LOOP;
  
       RETURN 0;
  
    END;

  BEGIN

    addr_list := ltrim(addr_list);
    i := lookup_unquoted_char(addr_list, ',;');
    IF (i >= 1) THEN
      addr      := substr(addr_list, 1, i - 1);
      addr_list := substr(addr_list, i + 1);
    ELSE
      addr := addr_list;
      addr_list := '';
    END IF;
 
    i := lookup_unquoted_char(addr, '<');
    IF (i >= 1) THEN
      addr := substr(addr, i + 1);
      i := instr(addr, '>');
      IF (i >= 1) THEN
    addr := substr(addr, 1, i - 1);
      END IF;
    END IF;

    RETURN addr;
  END;

  -- Write a MIME header
  PROCEDURE write_mime_header(conn  IN OUT NOCOPY utl_smtp.connection,
                  name  IN VARCHAR2,
                  value IN VARCHAR2) IS
  BEGIN
    utl_smtp.write_data(conn, name || ': ' || value || utl_tcp.CRLF);
  END;

  -- Mark a message-part boundary.  Set <last> to TRUE for the last boundary.
  PROCEDURE write_boundary(conn  IN OUT NOCOPY utl_smtp.connection,
               last  IN            BOOLEAN DEFAULT FALSE) AS
  BEGIN
    IF (last) THEN
      utl_smtp.write_data(conn, LAST_BOUNDARY);
    ELSE
      utl_smtp.write_data(conn, FIRST_BOUNDARY);
    END IF;
  END;

  ------------------------------------------------------------------------
  PROCEDURE mail(
         recipients IN VARCHAR2,
         cc IN VARCHAR2 DEFAULT NULL,
         bcc IN VARCHAR2 DEFAULT NULL,
         subject    IN VARCHAR2,
         iSender    In VARCHAR2,
         iSenderPass    In VARCHAR2,
         ismtp_host    In VARCHAR2,
         ismtp_port    In pls_integer,
         ismtp_domain    In VARCHAR2,
         message    IN VARCHAR2,
         i_bcc_other In varchar2) IS
    conn utl_smtp.connection;
  BEGIN
    conn := begin_mail( recipients,cc,bcc, subject,
                         'text/plain',Null,iSender,
                         iSenderPass,ismtp_host,ismtp_port,
                         ismtp_domain,i_bcc_other);
    write_text(conn, message);
    end_mail(conn);
 
  END;

  ------------------------------------------------------------------------
  FUNCTION begin_mail(
              recipients IN VARCHAR2,
              cc IN VARCHAR2  DEFAULT NULL,
              bcc IN VARCHAR2 DEFAULT NULL,
              subject    IN VARCHAR2,
              mime_type  IN VARCHAR2    DEFAULT 'text/plain',
              priority   IN PLS_INTEGER DEFAULT NULL,
              iSender     IN VARCHAR2,
              iSenderPass IN VARCHAR2,
              ismtp_host    IN VARCHAR2,
              ismtp_port  IN pls_integer  ,
              ismtp_domain  IN VARCHAR2 ,
              i_bcc_other In varchar2
              )
              RETURN utl_smtp.connection IS
    conn utl_smtp.connection;
  BEGIN
 
    sender_mail   :=iSender;
    sender_pass   :=iSenderPass;
    smtp_host     :=ismtp_host;
    smtp_port     :=ismtp_port;
    smtp_domain   :=ismtp_domain;
    smtp_recepient:=recipients;
    smtp_cc       :=cc;
    smtp_bcc      :=bcc;
    smtp_bcc_otner :=i_bcc_other;
 
   conn := begin_session;
    begin_mail_in_session(conn, subject, mime_type,
      priority);
    RETURN conn;
  END;

  ------------------------------------------------------------------------
  PROCEDURE write_text(conn    IN OUT NOCOPY utl_smtp.connection,
               message IN VARCHAR2) IS
  BEGIN
    utl_smtp.write_data(conn, message);
  END;

  ------------------------------------------------------------------------
  PROCEDURE write_mb_text(conn    IN OUT NOCOPY utl_smtp.connection,
              message IN            VARCHAR2) IS
  BEGIN
    utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(message));
  END;

  ------------------------------------------------------------------------
  PROCEDURE write_raw(conn    IN OUT NOCOPY utl_smtp.connection,
              message IN RAW) IS
  BEGIN
    utl_smtp.write_raw_data(conn, message);
  END;

  ------------------------------------------------------------------------
  PROCEDURE attach_text(conn         IN OUT NOCOPY utl_smtp.connection,
            data         IN VARCHAR2,
            mime_type    IN VARCHAR2 DEFAULT 'text/plain',
            inline       IN BOOLEAN  DEFAULT TRUE,
            filename     IN VARCHAR2 DEFAULT NULL,
                last         IN BOOLEAN  DEFAULT FALSE) IS
  BEGIN
    begin_attachment(conn, mime_type, inline, filename);
    write_text(conn, data);
    end_attachment(conn, last);
  END;

  ------------------------------------------------------------------------
  PROCEDURE attach_base64(conn         IN OUT NOCOPY utl_smtp.connection,
              data         IN RAW,
              mime_type    IN VARCHAR2 DEFAULT 'application/octet',
              inline       IN BOOLEAN  DEFAULT TRUE,
              filename     IN VARCHAR2 DEFAULT NULL,
              last         IN BOOLEAN  DEFAULT FALSE) IS
    i   PLS_INTEGER;
    len PLS_INTEGER;
  BEGIN
  
    begin_attachment(conn, mime_type, inline, filename, 'base64');

    -- Split the Base64-encoded attachment into multiple lines
    i   := 1;
    len := utl_raw.length(data);
    WHILE (i < len) LOOP
       IF (i + MAX_BASE64_LINE_WIDTH < len) THEN
     utl_smtp.write_raw_data(conn,
        utl_encode.base64_encode(utl_raw.substr(data, i,
        MAX_BASE64_LINE_WIDTH)));
       ELSE
     utl_smtp.write_raw_data(conn,
       utl_encode.base64_encode(utl_raw.substr(data, i)));
       END IF;
       utl_smtp.write_data(conn, utl_tcp.CRLF);
       i := i + MAX_BASE64_LINE_WIDTH;
    END LOOP;
  
    end_attachment(conn, last);

  END;

  ------------------------------------------------------------------------
  PROCEDURE begin_attachment(conn         IN OUT NOCOPY utl_smtp.connection,
                 mime_type    IN VARCHAR2 DEFAULT 'text/plain',
                 inline       IN BOOLEAN  DEFAULT TRUE,
                 filename     IN VARCHAR2 DEFAULT NULL,
                 transfer_enc IN VARCHAR2 DEFAULT NULL) IS
  BEGIN
    write_boundary(conn);
    write_mime_header(conn, 'Content-Type', mime_type);

    IF (filename IS NOT NULL) THEN
       IF (inline) THEN
      write_mime_header(conn, 'Content-Disposition',
        'inline; filename="'||filename||'"');
       ELSE
      write_mime_header(conn, 'Content-Disposition',
        'attachment; filename="'||filename||'"');
       END IF;
    END IF;

    IF (transfer_enc IS NOT NULL) THEN
      write_mime_header(conn, 'Content-Transfer-Encoding', transfer_enc);
    END IF;
  
    utl_smtp.write_data(conn, utl_tcp.CRLF);
  END;

  ------------------------------------------------------------------------
  PROCEDURE end_attachment(conn IN OUT NOCOPY utl_smtp.connection,
               last IN BOOLEAN DEFAULT FALSE) IS
  BEGIN
    utl_smtp.write_data(conn, utl_tcp.CRLF);
    IF (last) THEN
      write_boundary(conn, last);
    END IF;
  END;

  ------------------------------------------------------------------------
  PROCEDURE end_mail(conn IN OUT NOCOPY utl_smtp.connection) IS
  BEGIN
    end_mail_in_session(conn);
    end_session(conn);
  END;

  ------------------------------------------------------------------------
  FUNCTION begin_session RETURN utl_smtp.connection IS
    conn utl_smtp.connection;
  
  nls_charset Varchar2(255);
  receipient  Varchar2(4000);
  p_smtp_recepient Varchar2(4000):=smtp_recepient;
  p_smtp_cc  Varchar2(4000):=smtp_cc;
  p_smtp_bcc  Varchar2(4000):=smtp_bcc;
  BEGIN
    -- open SMTP connection
    conn := utl_smtp.open_connection(smtp_host, smtp_port);
    utl_smtp.helo(conn, smtp_domain);

          select value
          into   nls_charset
          from   nls_database_parameters
          where  parameter = 'NLS_CHARACTERSET';
        
       -- utl_smtp.command(conn, 'STARTTLS');
          utl_smtp.command(conn, 'auth login');
          utl_smtp.command(conn,utl_encode.text_encode(sender_mail, nls_charset, 1));
          utl_smtp.command(conn, utl_encode.text_encode(sender_pass, nls_charset, 1));
        
           utl_smtp.command(conn, 'MAIL FROM: <'||sender_mail||'>');    
         
          If smtp_bcc_otner='O' then
                WHILE (p_smtp_recepient IS NOT NULL) LOOP          
                   utl_smtp.command(conn, 'RCPT TO: <'||get_address(p_smtp_recepient)||'>'||chr(13));
                END LOOP;
              
               WHILE (p_smtp_cc IS NOT NULL) LOOP        
                   utl_smtp.command(conn, 'RCPT TO: <'||get_address(p_smtp_cc)||'>'||chr(13));
                END LOOP;
          Else
            WHILE (p_smtp_bcc IS NOT NULL) LOOP         
               utl_smtp.command(conn, 'RCPT TO: <'||get_address(p_smtp_bcc)||'>');
            END LOOP;
          End If;
         
        
    RETURN conn;
  END;

  ------------------------------------------------------------------------
  PROCEDURE begin_mail_in_session(conn       IN OUT NOCOPY utl_smtp.connection,             
                  subject    IN VARCHAR2,
                  mime_type  IN VARCHAR2  DEFAULT 'text/plain',
                  priority   IN PLS_INTEGER DEFAULT NULL) IS
  
     nls_charset    varchar2(255);
  BEGIN

    -- Specify sender's address (our server allows bogus address
    -- as long as it is a full email address (xxx@yyy.com).
    /*utl_smtp.mail(conn, get_address(my_sender));*/

    -- Specify recipient(s) of the email.
  /* WHILE (my_recipients IS NOT NULL) LOOP
      utl_smtp.rcpt(conn, get_address(my_recipients));
    END LOOP;*/
  
      
        
    -- Start body of email
    utl_smtp.open_data(conn);
         
       
     
    -- Set "From" MIME header
    write_mime_header(conn, 'From', sender_mail);--barack@obama.com
 

    -- Set "To" MIME header
    If smtp_bcc_otner='O' then 
      write_mime_header(conn, 'To', smtp_recepient);
      write_mime_header(conn, 'cc', smtp_cc);
    Else
      write_mime_header(conn, 'To', smtp_recepient);
      write_mime_header(conn, 'cc', smtp_cc);
      write_mime_header(conn, 'Bcc', smtp_bcc);
   End If;
    -- Set "Subject" MIME header
    write_mime_header(conn, 'Subject', subject);

    -- Set "Content-Type" MIME header
    write_mime_header(conn, 'Content-Type', mime_type);

    -- Set "X-Mailer" MIME header
    write_mime_header(conn, 'X-Mailer', MAILER_ID);

    -- Set priority:
    --   High      Normal       Low
    --   1     2     3     4     5
    IF (priority IS NOT NULL) THEN
      write_mime_header(conn, 'X-Priority', priority);
    END IF;

    -- Send an empty line to denotes end of MIME headers and
    -- beginning of message body.
    utl_smtp.write_data(conn, utl_tcp.CRLF);

    IF (mime_type LIKE 'multipart/mixed%') THEN
      write_text(conn, 'This is a multi-part message in MIME format.' ||
    utl_tcp.crlf);
    END IF;

  END;

  ------------------------------------------------------------------------
  PROCEDURE end_mail_in_session(conn IN OUT NOCOPY utl_smtp.connection) IS
  BEGIN
    utl_smtp.close_data(conn);
  END;
  
  ------------------------------------------------------------------------
  PROCEDURE end_session(conn IN OUT NOCOPY utl_smtp.connection) IS
  BEGIN
    utl_smtp.quit(conn);
  END;

END;
/
***********DATABSE_MAIL.pkb**********
Create TEST_PACKAGE_MAIL.prc procedure  to test your e-mail from database.

CREATE OR REPLACE procedure ORBABG.Test_Package_mail is
  conn      utl_smtp.connection;
  req       utl_http.req; 
  resp      utl_http.resp;
  data      RAW(200);
  oSenderMail   Varchar2(600);
  oSenderMlPas   Varchar2(600);
  oLclSmtpHost   Varchar2(600);
  oLclSmtpPort   pls_integer;
  oLclSmtpDom   Varchar2(600); 
    f_message         varchar2(32767);
    f_message1         varchar2(32767);
      f_message2         varchar2(32767);
 
BEGIN
  smtp_port_domain('344',oSenderMail,oSenderMlPas,oLclSmtpHost,oLclSmtpPort,oLclSmtpDom);
  
             
  conn := Database_mail.begin_mail(
    recipients => 'mrifai@etainfotech.com',
    cc => 'akter@etainfotech.com',
    bcc => 'rashel@etainfotech.com',
    subject    => 'Attachment Test BCC',
    mime_type  => Database_mail.MULTIPART_MIME_TYPE,
    iSender     => oSenderMail,
    iSenderPass     => oSenderMlPas,
    ismtp_host     => oLclSmtpHost,
    ismtp_port     => oLclSmtpPort,
    ismtp_domain     => oLclSmtpDom,
    i_bcc_other      =>'O');
   
 /* conn := Database_mail.begin_mail(
    recipients => 'akter@etainfotech.com',
    cc => 'akter_cs@yahoo.com',
    bcc => 'rashel@etainfotech.com',
    subject    => 'Attachment Test BCC',
    mime_type  => Database_mail.MULTIPART_MIME_TYPE,
    iSender     => oSenderMail,
    iSenderPass     => oSenderMlPas,
    ismtp_host     => oLclSmtpHost,
    ismtp_port     => oLclSmtpPort,
    ismtp_domain     => oLclSmtpDom,
    i_bcc_other      =>'B');  */
f_message:= ' <table border="1" style="width: 100%; font-family: Tahoma; font-size: smaller;
border-right: dimgray 1px solid; border-top: dimgray 1px solid; border-left: dimgray 1px
solid; border-bottom: dimgray 1px solid;" cellpadding="5" cellspacing="0">
       <tr>
           <td colspan="3" style="text-align: center; border-top-style: none;
border-right-style: none; border-left-style: none; border-bottom-style: none; font-weight:
bold; font-size: large; text-decoration: underline;">
               DHAKA BANGLADESH</td>
       </tr>

              </table>';
                          
                          
   f_message1:= '<html>
                           <head>
                           <script type="text/javascript">
                            function printpage()
                              {
                              window.print()
                              }
                              </script>
                           </head>
                           <body>'||f_message||'
                         <input type="button" value="Print" onclick="printpage()" />  
                           </body>
                           </html>';
    
     f_message2:= '<html>
                           <head>                         
                           </head>
                           <body>'||f_message||'
                          </body>
                           </html>';
                                                  
  Database_mail.attach_text(
    conn      => conn,
    data      => f_message2,
    mime_type => 'text/html');


  Database_mail.attach_text(
    conn      => conn,
    data      => f_message1,
    mime_type => 'text/html',
    inline    => FALSE,
    filename  => 'report.htm',
    last      => TRUE);
 
  Database_mail.end_mail( conn => conn );

END;
/
**************************************************************************
Now smtp_port_domain procedure details below

CREATE OR REPLACE PROCEDURE smtp_port_domain(iCampcde VARCHAR2,
                                                    oSenderMail  Out Varchar2,
                                                    oSenderMlPas Out Varchar2,                           
                                                    oLclSmtpHost Out Varchar2,
                                                    oLclSmtpPort Out pls_integer,
                                                    oLclSmtpDom  Out Varchar2)
IS
 
BEGIN

 Begin
  SELECT SENDRADD, SENDRPAS, LCLSMTPH, LCLSMTPP, SMTPDOMN
  INTO  oSenderMail,oSenderMlPas,oLclSmtpHost,oLclSmtpPort,oLclSmtpDom
  FROM SMTPTST
  WHERE CAGRPCDE='001' And CACMPCDE=iCampcde and rownum=1; 
   Exception
  When Others Then
   Raise_Application_Error (-20001,sqlerrm);
  End;
 
END ;
/
Now using TEST_PACKAGE_MAIL procedure, you can send e-mail to any e-mail. Its fully tested and ready to use.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)