Send_email_from_database

How to send an email from  a 11.2.0.3 Oracle database

    • Use source  and add info  from following Link: 
    • Check the database JVM version :
supsunhh3>  $ORACLE_HOME/jdk/bin/java  -version
java version "1.5.0_30"
  •  For JDK 1.5 we need  activation.jar and mail.jar ( use the database JAR files )
supsunhh3> setenv CLASSPATH .:$ORACLE_HOME/oc4j/j2ee/home/lib/mail.jar:$ORACLE_HOME/oc4j/j2ee/home/lib/activation.jar
supsunhh3> $ORACLE_HOME/jdk/bin/javac SendMailSSL.java
supsunhh3> $ORACLE_HOME/jdk/bin/java SendMailSSL
    • Continue only when you can send an email via command line !
    • Use script below
Set privs for sending email:
SQL> connect sys/sys as  sysdba
SQL> exec dbms_java.grant_permission( 'SCOTT', 'SYS:java.lang.RuntimePermission', 'getClassLoader', '' );
SQL> exec dbms_java.grant_permission( 'SCOTT', 'SYS:java.net.SocketPermission', 'stbeehive.oracle.com', 'resolve' );
SQL> exec dbms_java.grant_permission( 'SCOTT', 'SYS:java.net.SocketPermission', '141.146.118.10:465', 'connect,resolve' );

Load the related java JARs into SCOTT schema:
SQL> connect scott/tiger 
SQL> ! loadjava -force -user scott/tiger ./DB_JARS/activation.jar
SQL> ! loadjava -force -user scott/tiger ./DB_JARS/mail.jar

Load Class files , create PLSQL function and run the Code
SQL> ! loadjava -force -user scott/tiger *.class
SQL> show errors

SQL> CREATE OR REPLACE FUNCTION test_mail  RETURN VARCHAR2
as LANGUAGE JAVA NAME 'SendMailSSL.run_it() return String';
/
SQL> show errors

SQL> select test_mail  from dual;

TEST_MAIL
--------------------------------------------------------------------------------
Mail sent

 

Leave a Reply

Your email address will not be published. Required fields are marked *