Email in PL/SQL Oracle 10G

The UTL_MAIL package was introduced in Oracle 10g and it is easier to use when compared to UTL_SMTP. In order to use Oracle UTL_MAIL package you need to set a new init.ora parameter "SMTP_OUT_SERVER", set to your outgoing mailserver.

rajesh@ORA10GR2> alter system set smtp_out_server = 'mailinbound.domain.com';

System altered.

Elapsed: 00:00:00.14

rajesh@ORA10GR2> alter system set utl_file_dir = 'FILE_DIR' scope=spfile;

System altered.

Elapsed: 00:00:00.14
rajesh@ORA10GR2>

rajesh@ORA10GR2> connect sys/***** as sysdba
Connected.
sys@ORA10GR2> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORA10GR2> startup;
ORACLE instance started.
Database mounted.
Database opened.
sys@ORA10GR2>
sys@ORA10GR2>
sys@ORA10GR2> show parameter utl_file_dir;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      FILE_DIR
sys@ORA10GR2>

You must set UTL_FILE_DIR to a directory, where the attachment files exists

sys@ORA10GR2>
sys@ORA10GR2> show parameter smtp_out_server;
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------------
smtp_out_server                      string      mailinbound.domain.com
sys@ORA10GR2> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlmail.sql;

Package created.

Elapsed: 00:00:00.70

Synonym created.

Elapsed: 00:00:00.10
sys@ORA10GR2>
sys@ORA10GR2> @C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\prvtmail.plb;

Package body created.

Elapsed: 00:00:00.45
No errors.
sys@ORA10GR2>
sys@ORA10GR2> grant execute on utl_mail to rajesh,scott;

Grant succeeded.

Elapsed: 00:00:00.04
sys@ORA10GR2>
sys@ORA10GR2> connect scott/tiger
Connected.
scott@ORA10GR2>
scott@ORA10GR2> begin
  2     utl_mail.SEND_ATTACH_RAW(
  3     sender=>'rajeshwaran_jeyabal@domain.com',
  4     recipients=>'rajeshwaran_jeyabal@domain.com',
  5     subject=>'Hello World',
  6     message=>'Hello World',
  7     attachment =>utl_raw.cast_to_raw('PDF_Contents'),
  8     att_filename=>'TEST_PDF.pdf');
  9
 10     utl_mail.SEND_ATTACH_VARCHAR2(
 11     sender=>'rajeshwaran_jeyabal@domain.com',
 12     recipients=>'rajeshwaran_jeyabal@domain.com',
 13     subject=>'Hello World',
 14     message=>'Hello World',
 15     attachment =>'Text_Contents',
 16     att_filename=>'log.txt');
 17  end;
 18  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.64
scott@ORA10GR2>

Oracle 10g has added over fifty new PL/SQL packages and enhanced many of the existing packages, thus expanding the Oracle DBA's toolkit once again !