OraTweetBot, an XMPP/Jabber listener for Twitter

OraTweetBot is an XMPP/Jabber bot built with Java that will listen for tweets and post them to Twitter or to a database. This is part of my pet project OraTweet which is built with Oracle APEX and will soon make it available through a distributable APEX package.

If you are tired of Twitter xmpp hook not working, you can use OraTweetBot to listen for your tweets and post them to Twitter.

If you want to go further you can hook the bot to post to your database, then your database can trigger the post to Twitter (see Post Updates to Twitter from APEX (PL/SQL)). The big advantage of this is that you can actually map twitter users to a users table on the db and send on their behalf.

Right now I'm only releasing the distributable form, if you want to get the source let me know on the comments. If there is a demmand I can create a sourceforge or google code project to host it.

Instructions:

1.-Download here OraTweetBot (unzip)
2.- Modify file conf.properties with your values.
3.- If you are planning to send tweet to a database create a Stored Procedure called INSERT_TWEET:

   create or replace
   PROCEDURE INSERT_TWEET( email IN VARCHAR2, tweet IN VARCHAR2, tresource IN VARCHAR2, createdon IN TIMESTAMP    )
   AS
   BEGIN
     INSERT INTO TWEET (EMAIL,TWEET,TRESOURCE,CREATEDON) VALUES (email,tweet, tresource, createdon );
   COMMIT;
   END INSERT_TWEET;

4.- Run it from command line
$java -jar OraTweetBot.jar
or to run in the background
$nuhup java -jar OraTweetBot.jar &
Run OraTweetBot preferably on an "always on" machine.

You can login with the same XMPP (gmail) account from different clients, so you can set up OraTweetBot with the same account you want to post tweets with.

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