Adding IM (Jabber/XMPP) messaging to Oracle APEX

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:

1. 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)
   
2. 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;
   

3. 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

4. Login to sqlplus as sysdba and execute:
   

   call dbms_java.grant_permission('PUBLIC', 'java.net.SocketPermission', '*', 'accept, connect,
   listen, resolve');
   call dbms_java.grant_permission('PUBLIC', 'java.net.NetPermission', '*', 'accept, connect,
   listen, resolve');

5. Connect as XMPP/XMPP and run the following script.
   
   
6. Grant privileges on the new package to APEX_PUBLIC_USER:
   

   grant execute, debug on dbms_xmpp to APEX_PUBLIC_USER;

7. Login to APEX to SQL Workshop> SQL Commands and test:
   

   select XMPP.dbms_xmpp.send_plain_text_message('talk.google.com',
   5222,
   'gmail.com',
   'my_user',
   'my_password',
   'recipient@gmail.com',
   'XMPP Test',
   'Hello from Oracle APEX') xmpp_return_code
   from dual;

And thats it! Now if your company happens to be running a XMPP/Jabber server (like we do at Oracle) or if you have a user base that has gmail accounts or any of the other services running on XMPP, this could be a great alternative to emails. This is a huge hit on my applications and I bet anyone can come with more creative ways to include Jabber with APEX. Let me know how you use it.

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/.