One of my APEX application relies heavily on email notifications for work flow approval and task assignment notification. A few months ago it occurred to me that it would be really nice to implement IM notification and let users decide how they like to be notified. So I went to work and found a great java open source XMPP/Jabber client called Smack. My first implementation was just a simple class that called smack functions loaded on the DB by loadjava. But I recently found a project started by Adrien Sales that wraps more of the Smack functions such as rosters. To add IM functionality to your application follow these steps:
- Download Oracle XMPP/Jabber Instant Messenger for Oracle 11G.
If you are using Oracle 10g download from here (I had to recompile using an older smack version and JDK1_4)
- Login to sqlplus as sysdba and create account XMPP to load java classes and package.
create user XMPP identified by XMPP
default tablespace your_tablespace
temporary tablespace your_temp_tablespace
quota unlimited on users;
GRANT "CONNECT" TO XMPP;
GRANT "DBA" TO XMPP;
- From the shell go to the dist folder and excute
loadjava -u XMPP/XMPP -resolve lib/smack.jar
loadjava -u XMPP/XMPP -resolve lib/smackx.jar
loadjava -u XMPP/XMPP -resolve OracleXMPP.jar
- Login to sqlplus as sysdba and execute:
call dbms_java.grant_permission('PUBLIC', 'java.net.SocketPermission', '*', 'accept, connect,
call dbms_java.grant_permission('PUBLIC', 'java.net.NetPermission', '*', 'accept, connect,
- Connect as XMPP/XMPP and run the following script.
- Grant privileges on the new package to APEX_PUBLIC_USER:
grant execute, debug on dbms_xmpp to APEX_PUBLIC_USER;
- Login to APEX to SQL Workshop> SQL Commands and test:
'Hello from Oracle APEX') xmpp_return_code
I am also planning to post soon how I'm running my own Jabber server and how I can integrate it with my APEX application. Stay tuned!
For more information on Jabber please visit http://www.jabber.org/.