Features at a glance
Java in the database: other examples
the Oracle database there are two PL/SQL APIs to send mails: UTL_SMTP
and UTL_MAIL. Application Express (APEX) contains another Package:
APEX_MAIL. So sending emails is no problem and frequently done from
Oracle database installations. But there is no package which is able to
retrieve messages from a mailbox on a server. The here provided package
closes this gap.
Message listing in Oracle SQL*Plus
New Features (current version 1.1.3)
New Features (version 1.0)
Technical backgroundThis package utilizes the JVM which is embedded in the database since Oracle8i. Since Oracle10g the standard Java Mail APIis included with this JVM. With the Java Mail API messages can be constructed and sent as well as being retrieved from a mail server (mailclient capability). This package makes the java mail functionality available to SQL and PL/SQL - it fully relies on the given POP3 / IMAP implementation and does not contain any own POP3 or IMAP code. Therefore it works with all mailservers the Java Mail API works with. If a mailserver does not work with Java Mail it does also not work with this package.
Java has an own security mechanism: The database user which calls the functions and procedured as well as the owner of the objects needs appropriate privileges in order to access the mail server over the network. These privileges can be granted fine-grained using the DBMS_JAVA.GRANT_PERMISSION procedure. A typical call (to be executed with DBA privileges) is as follows:
grantee => '[dbuser]',
permission_type => 'SYS:java.net.SocketPermission',
permission_name => '[mailserver name or "*" for the whole network]',
permission_action => 'connect,resolve'
Permissions might be granted for the whole network, subnets or individual hostnames or IP addresses. Furthermore negative permissions (DBMS_JAVA.RESTRICT_PERMISSION) are possible: A user might be granted privileges for a network but not for a particular server. More information about the security mechanism is contained in the Oracle documentation.