Search This Blog

Sunday, September 4, 2011

How to configure 11GXE to sent email from database

 How to configure 11GXE to sent email from database
Step
1. connect with sys as sysdba user and run the following two scripts
1.1 utlmail.sql
1.2 prvtmail.plb
NOTE: both scripts are located under $ORACLE_HOME/rdbms/admin directory


2. set SMTP_OUT_SERVER parameter with your exchange server name or IP address


3. grant EXECUTE privs to user which user use utl_mail package (conditional)


4. TEST


Details
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> --run following scritps
SQL> @C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\utlmail.sql
Package created.

Synonym created.
SQL> @C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\prvtmail.plb

Package created.
Package body created.
Grant succeeded.
Package body created.
No errors.
SQL> --configure SMTP_OUT_SERVER parameter
SQL> alter system set smtp_out_server='192.147.09.01';
System altered.
SQL> --grant execute privs to user which user use UTL_MAIL package (conditional)
SQL> --TESTING
SQL> begin
  2  utl_mail.send (
  3  sender =>'askdbataj@gmail.com',
  4  recipients =>'askdbataj@gmail.com',
  5  message => 'TESTING ... ');
  6  end;
  7  /

PL/SQL procedure successfully completed.

3 comments:

Nahar said...

Thanks Taj....

your blog is so nice and knowledge based.

Nahar

Mohammed Taj said...

Thanks Nahar...

Dev said...

I'VE FOLLOWED YOUR STEPS BUT CANNOT SEND THE MAIL.. HELP PLEASE..
SHOULD SMTP BE CONFIGURED PRIOR.?