Send Mail Using Oracle Package

1) Check Package UTL_MAIL is available

desc sys.utl_mail

PROCEDURE SEND

Argument Name                  Type                    In/Out Default?

------------------------------ ----------------------- ------ --------

SENDER                         VARCHAR2                IN

RECIPIENTS                     VARCHAR2                IN

CC                             VARCHAR2                IN     DEFAULT

BCC                            VARCHAR2                IN     DEFAULT

SUBJECT                        VARCHAR2                IN     DEFAULT

MESSAGE                        VARCHAR2                IN     DEFAULT

MIME_TYPE                      VARCHAR2                IN     DEFAULT

PRIORITY                       BINARY_INTEGER          IN     DEFAULT

 Package is available, If Package is not available create it using,

SQL> @?/rdbms/admin/utlmail.sql
Package created
Synonym created

2) Create UTL_MAIL Package Body

SQL> sho user

USER is "SYS"

SQL> @?/rdbms/admin/prvtmail.plb

Package created.

Package body created.

Grant succeeded.

Package body created.

No errors.

3) Edit the host file with Mail Server hostname and IP

10.11.1.1       hostname.domainname   hostname

4) Port must be Open to access Mail Server

$ telnet delhint1 100

Trying...

Connected to delhint1.iffco.nic.in.

Escape character is '^]'.

220 hostname ESMTP Service (Lotus Domino Release 8.5.2) ready at Wed, 7 Sep 2011 17:25:06 +0530

<<Ctrl c>>

[1] + Stopped (SIGTSTP)        telnet hostname 100

5) Create Directory 'MAIL_DIR'

Conn / as sysdba

CREATE OR REPLACE DIRECTORY MAIL_DIR AS '/home/orauat/vivek/scr/sql/';

 

 6) Run the Procedure

@/home/orauat/vivek/scr/sql/dbstatusmail.sql

$ cat dbstatusmail.sql

alter session SET smtp_out_server = 'hostname:100';

declare

v_utl_filehandler       UTL_FILE.FILE_TYPE;

v_rawfile                       RAW(32767);

v_size                               NUMBER;

v_block                              NUMBER;

v_boolean                   BOOLEAN;

v_file_dir                      varchar2(100);

v_transfer_location varchar2(100) := 'reciever@gmail.com';

v_file_name varchar2(100) := 'dbdetail.txt';

begin

v_file_dir := 'MAIL_DIR';

v_utl_filehandler := UTL_FILE.FOPEN(v_file_dir, v_file_name, 'r');

UTL_FILE.fgetattr(v_file_dir, v_file_name, v_boolean, v_size, v_block);

UTL_FILE.get_raw(v_utl_filehandler, v_rawfile, v_size);

UTL_FILE.FCLOSE(v_utl_filehandler);

sys.utl_mail.send_attach_raw( sender            =>      'sender@gmail.com'

, recipients    =>      v_transfer_location

, subject               =>      'Database backup status from Production'

, message               =>      'Dear Sir'||CHR(13)||CHR(13)||

'Please find attached database status file from Production.'||CHR(13)||CHR(13)||

'For any issue reported in it, please check it on server.'||CHR(13)||CHR(13)||

'Thanks'||CHR(13)||

'Vivek Bhutani'||CHR(13)||CHR(13)

, attachment    =>      v_rawfile

, att_inline    =>      FALSE

, att_filename  =>      v_file_name

);

end;

/

exit;