hi there,

starting from oracle 8i we are eligible to send emails directly from PL/SQL by using either UTL_SMTP or UTL_TCP packages. However, oracle provides us valuable package called UTL_MAIL for sending emails in Oracle 10g.

here is example of how to use UTL_MAIL to send emails from PL/SQL :

BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
  UTL_MAIL.send(sender => 'me@address.com',
            recipients => 'you@address.com',
               subject => 'Test Mail',
               message => 'Hello World',
             mime_type => 'text; charset=us-ascii');
END;

Note : for security purpose, UTL_MAIL is not enabled by default. so you should enable it by connecting to SYS user and executing the utlmail.sql and prvtmail.plb scripts in the $ORACLE_HOME/RDBMS/admin directory. In addition, you must configure an initialization parameter, SMTP_OUT_SERVER, to point to an outgoing SMTP server.
in addition, you must give EXECUTE permission to PUBLIC by run below script :

grant execute on UTL_MAIL to public

hope this helpful 🙂
share with others to stretch our experiences