Send Email From Oracle Database

 The steps to send emails from an Oracle database are as follows:

Step 1:

CREATE DIRECTORY MYEMAIL AS 'C:\file_data';

GRANT READ,WRITE ON DIRECTORY MYEMAIL TO SCOTT;

Step 2:

CREATE OR REPLACE PROCEDURE send_mail (p_to          IN VARCHAR2,

                                       p_from        IN VARCHAR2,

                                       p_subject     IN VARCHAR2,

                                       p_text_msg    IN VARCHAR2 DEFAULT NULL,

                                       p_attach_name IN VARCHAR2 DEFAULT NULL,

                                       p_attach_mime IN VARCHAR2 DEFAULT NULL,

                                       p_attach_blob IN BLOB DEFAULT NULL,

                                       p_smtp_host   IN VARCHAR2,

                                       p_smtp_port   IN NUMBER DEFAULT 25)

                                                                            

AS

  l_mail_conn   UTL_SMTP.connection;

  l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';

  l_step        PLS_INTEGER  := 12000; -- make sure you set a multiple of 3 not higher than 24573

BEGIN

  

DBMS_OUTPUT.put_line('TEST1');

DBMS_OUTPUT.put_line('TEST1');

  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);

  UTL_SMTP.helo(l_mail_conn, p_smtp_host);

  UTL_SMTP.mail(l_mail_conn, p_from);

  UTL_SMTP.rcpt(l_mail_conn, p_to);

  UTL_SMTP.open_data(l_mail_conn);

  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);

  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);

  UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);

  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);

  UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);

  UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);

  UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

  IF p_text_msg IS NOT NULL THEN

    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, p_text_msg);

    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);

  END IF;

  IF p_attach_name IS NOT NULL THEN

    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || p_attach_mime || '; name="' || p_attach_name || '"' || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);

    UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || p_attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

    FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_attach_blob) - 1 )/l_step) LOOP

      UTL_SMTP.write_data(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_attach_blob, l_step, i * l_step + 1))));

    END LOOP;

    UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);

  END IF;

  UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);

  UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);

END;

Step 3:

CREATE OR REPLACE FUNCTION loadBlobFromFile(p_file_name VARCHAR2) RETURN BLOB AS

  dest_loc  BLOB := empty_blob();

  src_loc   BFILE := BFILENAME('MYEMAIL', p_file_name);

BEGIN

  

  -- Open source binary file from OS

 DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);

  -- Create temporary LOB object

  DBMS_LOB.CREATETEMPORARY(

        lob_loc => dest_loc

      , cache   => true

      , dur     => dbms_lob.session

  );

  -- Open temporary lob

  DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);

  -- Load binary file into temporary LOB

  DBMS_LOB.LOADFROMFILE(

        dest_lob => dest_loc

      , src_lob  => src_loc

      , amount   => DBMS_LOB.getLength(src_loc));

  -- Close lob objects

  DBMS_LOB.CLOSE(dest_loc);

  DBMS_LOB.CLOSE(src_loc);

  -- Return temporary LOB object

  RETURN dest_loc;

END loadBlobFromFile;

Step 4:

DECLARE

  l_blob BLOB;

BEGIN

  

  SELECT   loadBlobFromFile('ayaz.pdf')

  INTO   l_blob

  FROM   dual;

  send_mail(p_to          => '(Email)@(Domain).com.pk',

            p_from        => '(Email)@(Domain).com.pk',

            p_subject     => 'Message attached',

            p_text_msg    => 'This is a test message',

            p_attach_name => 'abc.pdf',

            p_attach_mime => 'image/gif',

            p_attach_blob => l_blob,

            p_smtp_host   => '(SMTP Email)');

END;