Email From Oracle PLSQL

The first step is to create the ACL and define the privileges for it:

The general syntax is as follows:

Conn sys as sysdba 

BEGIN

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'www.xml',

                                    description => 'WWW ACL',

                                    principal   => 'SCOTT',

                                    is_grant    => true,

                                    privilege   => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'www.xml',

                                       principal => 'SCOTT',

                                       is_grant  => true,

                                       privilege => 'resolve');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'www.xml',

                                    host => 'smtp.mycompany.com');

END;

COMMIT;

Simple Email

DECLARE

v_Subject     VARCHAR2(80) := 'test subject';

v_Mail_Host VARCHAR2(30) := 'smtp.mycompany.com';

v_Mail_Conn                              utl_smtp.Connection;

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

BEGIN 

 v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);

 utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);

 utl_smtp.Rcpt(v_Mail_Conn, 'me@company.com’);

utl_smtp.Mail(v_Mail_Conn, 'me@company.com');

---UTL_SMTP.DATA: Sends the (complete) e-mail body

utl_smtp.DATA(v_Mail_Conn,

'From: ' ||  'me@company.com' ||crlf ||

'Subject: '|| v_Subject || crlf ||

'Cc  '|| 'me@company.com' || crlf || 

'To  '|| 'me@company.com' || crlf || 'Message body' || crlf   );

utl_smtp.Quit(v_mail_conn);

END;

Email with Attachment

CREATE TABLE  "FILE_UPLOAD" 

   (                "FILE_UPLOAD_ID" NUMBER NOT NULL ENABLE,  

                    "BLOB_CONTENT" BLOB, 

                    "FILE_NAME" VARCHAR2(255), 

                    "MIME_TYPE" VARCHAR2(255), 

                    "DOC_SIZE" NUMBER ) ;

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; 

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

BEGIN

  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') || crlf);

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

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

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

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

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

  UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed;

boundary="' || l_boundary || '"' || crlf || crlf);

  IF p_text_msg IS NOT NULL THEN

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

    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain;

charset="iso-8859-1"' || crlf || crlf);    

    UTL_SMTP.write_data(l_mail_conn, p_text_msg);

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

  END IF;

  IF p_attach_name IS NOT NULL THEN

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

    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || p_attach_mime

    || '; name="' || p_attach_name || '"' || crlf);

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

    UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment;

 filename="' || p_attach_name || '"' || crlf || 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, crlf || crlf);

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

 UTL_SMTP.close_data(l_mail_conn);

 UTL_SMTP.quit(l_mail_conn);

END send_mail;

DECLARE

l_blob blob;

l_file_name varchar2(30);

l_mime_type varchar2(30);

BEGIN

Select t.Blob_Content, t.FILE_NAME, t. MIME_TYPE

INTO  l_blob , l_file_name ,  l_mime_type from file_upload t Where t.file_upload_id = 1;

  send_mail(p_to          => 'smtp.mycompany.com',

            p_from        => 'smtp.mycompany.com',

            p_subject     => 'Test Message',

            p_text_msg    => 'This is a test message.',

            p_attach_name => l_file_name,

            p_attach_mime =>  l_mime_type,

            p_attach_blob => l_blob,

            p_smtp_host   => 'smtp.mycompany.com');

END;

Enable email in oracle Apex

Create the ACL and define the privileges for it:

The general syntax is as follows:

Conn sys as sysdba 

BEGIN

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'www.xml',

                                    description => 'WWW ACL',

                                    principal   => 'APEX_050100',  

                                    is_grant    => true,

                                    privilege   => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'www.xml',

                                       principal => 'APEX_050100',

                                       is_grant  => true,

                                       privilege => 'resolve');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'www.xml',

                                    host => 'smtp.mycompany.com');

END;

COMMIT;