Environment Details

• Oracle Linux Server release 7.4
• Oracle Database 12.1.0.2.0
• Putty -> For accessing the Linux Server
• Database Credentials
  • SYS user
  • Scott for configuring mail
• Postfix already configured and Running
• Access to Internet

Before configuring make sure you are able to send mail from Operating system, if not Click here to configure mail on Linux

Send test Mail & Confirm

Login to oracle OS user and send test mail to confirm postfix is configured correctly.

echo “mail is working”| mail -r oracleprohowto@gmail.com -s “linux it’s ok” oracleprohowto@gmail.com

Login to Oracle Database

To send mail from Oracle database we will use UTL_MAIL package which will allow to send email from PL/SQL.

login to Database as sys user and execute

CONN sys/password AS SYSDBA
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb

create directory MY_PDF_DIR as ‘/home/oracle/MY_PDF_DIR’;
grant read,write on directory MY_PDF_DIR to scott;
GRANT EXECUTE ANY PROGRAM TO scott;
grant execute on utl_mail to scott;
grant execute on UTL_SMTP to scott;

set pages 1000
set lines 1000
column HOST format a20
column LOWER_PORT format 999
column UPPER_PORT format 9999
column ACL format a50
column PRINCIPAL format a20
column PRIVILEGE format a20
column ACL_OWNER format a20
SELECT ACL,PRINCIPAL,PRIVILEGE,ACL_OWNER FROM dba_network_acl_privileges where principal=’SCOTT’;
SELECT HOST,LOWER_PORT,UPPER_PORT,ACL FROM dba_network_acls ORDER BY host;
SELECT ACL,PRINCIPAL,PRIVILEGE,ACL_OWNER FROM dba_network_acl_privileges where principal=’SCOTT’;
select * from DBA_HOST_ACES;

12c

begin
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => ‘127.0.0.1’,
ace => xs$ace_type(privilege_list => xs$name_list(‘connect’),
principal_name => ‘SCOTT’,
principal_type => xs_acl.ptype_db));
END;
/
Commit;

Login to Oracle database as scott user and execute below command

BEGIN
EXECUTE IMMEDIATE ‘ALTER SESSION SET smtp_out_server = ”127.0.0.1”’;
UTL_MAIL.send(sender => ‘oracleprohowto@gmail.com’,
recipients => ‘oracleprohowto@gmail.com’,
subject => ‘FDSL DB Mail -‘,
message => ‘The mail is working now from fsl10 with the script shared’,
mime_type => ‘text; charset=us-ascii’);
END;
/

SEND MAIL WITH ATTACHMENT

CREATE OR REPLACE PROCEDURE send_email (FileName IN VARCHAR2, AttachmentName IN VARCHAR2)
AS
Attachment_Too_Large EXCEPTION;
PRAGMA EXCEPTION_INIT (Attachment_Too_Large, -6502);
FileHandle BFILE;
Buffer RAW(32767);
Amount INTEGER;
Offset INTEGER := 1;
BEGIN
EXECUTE IMMEDIATE ‘ALTER SESSION SET smtp_out_server = ”127.0.0.1”’;
FileHandle := BFILENAME(‘MY_PDF_DIR’, FileName);
Amount := DBMS_LOB.GETLENGTH(FileHandle);
DBMS_OUTPUT.PUT_LINE(‘Attachment Size: ‘ || Amount || ‘ bytes’);
DBMS_LOB.FILEOPEN(FileHandle, DBMS_LOB.FILE_READONLY);
DBMS_LOB.READ(FileHandle, Amount, Offset, Buffer);
DBMS_LOB.FILECLOSE(FileHandle);
UTL_MAIL.SEND_ATTACH_RAW(sender => ‘oracleprohowto@gmail.com’,
recipients => ‘oracleprohowto@gmail.com’,
subject => ‘Attachment mail from Oracle 12c Linux DB (Scott)’,
message => ‘Mail from database user Scott’,
attachment => Buffer,
att_inline => FALSE,
att_filename => AttachmentName);
EXCEPTION
WHEN Attachment_Too_Large THEN
DBMS_OUTPUT.PUT_LINE(‘Unable to Send Email – Attachment Too Large’);
END;
/

EXEC send_email(‘dummy.pdf’, ‘test.pdf’)