Oracle 10g - Configure Email

UTL Mail Package for sending mail

1. Install the package
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb

Package Paths my be something like this:
i)On live db > @/global/oracle/app/product/10.2.0/db_1/rdbms/admin/utlmail.sql
ii)On local db > @> @/home/oracle/oracle/product/10.2.0/db_1/rdbms/admin/utlmail.sql

2. GRANT
GRANT execute ON utl_mail TO ;
(or)
GRANT execute ON utl_mail TO PUBLIC;

3. Configure smtp server
> alter system set smtp_out_server = '172.100.3.45:25' scope=both;

I prefer setting this inside the procedure/function sending the mail


4. Restart the DBMS
>shutdown immediate
>startup


SENDING EMAIL

Showing only the relevant part of the function :

IF(email = '1')THEN

--EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = '172.100.3.98:25';
UTL_MAIL.send(sender => 'imisadmin@cit.go.ke', --to confirm that this email is from imis
recipients => recepts, --client email address(es)
cc => rec_user.email, --carbon copy to relevant officer
--bcc => 'iitambo@dewcis.com', --bind cc to developer
subject => sub,
message => msg,
mime_type => 'text; charset=us-ascii');
END IF;