OraTweet - Tweeting on the Enterprise

Update: You can now download the OraTweet package at http://oratweet.com !

How It All Began:
Although I didn't come up with the whole micro-blogging concept, I did realize the potential it had in the enterprise when I first stumbled upon Twitter. Instead of just writing on this blog, a forum, or in Twitter something like, "Man, someone should write an enterprise twitter," I went to work using Oracle APEX which is an amazing rapid web application development tool that allowed me to focus on the real issue: create a micro-blogging messaging system. Since APEX sits right on top of our great flagship (Oracle RDBMS + mod_plsql = FTW!) it allowed me to use my pl/sql ninja skills.

It took me literally one weekend to have the bare bone architecture up and running.
As I posted here before I used a loaded JAVA/PLSQL XMPP library based on smack api to support IM messaging right from the Oracle JVM and created OraTweetBot as an IM listener. Hooking OraTweet to our internal IM system works flawlessly since we are using open source XMPP protocols. User adoption is also a plus since users can use their existing IM clients to interact with OraTweet. OraTweet also has the option of sending and getting updates from Twitter with PL/SQL, thus giving the user the option to post to the world or just internally.

From the beginning I got great feedback from our own Web 2.0 AppsLab adoption team and got some great pointers on corporate adoption. Since this is a side project I let OraTweet sit for a few weeks without much advertisement while I was doing improvements (and my actual work!). But Meg Bear and the Fusion Talent Management Products team stumbled over OraTweet while looking for a solution to bridge the communication gap within their global team. They contacted me, and we worked together on some improvements and launched a pilot.
At this point Carl Backstrom from the APEX team offered to help me. Now thanks to him we have a working API, and we have plans to deploy OraTweet globally once it is ready. Having this API lets OraTweet work as a global messaging system allowing third parties and platforms such as OpenSocial interact with OraTweet users.

After one week of alpha/beta life and not much internal advertisement OraTweeters numbers went from a dozen to the hundreds and is still growing, which shows that there is a hidden demand for internal communication tools like this. I like the fact that OraTweet flattens enterprise hierarchies and opens communication between all levels.

Now the best part is that I'm planning to release OraTweet for free as an APEX package once all the beta wrinkles are out. It works great next to the other free packages such as ARIA, which is our internal employee directory.

What I envision as I release OraTweet is seeing companies, universities, and organizations running their own OraTweet instance, allowing them to keep their information private yet strengthening their own internal communities. It should be the same way we do email and instant messaging: We manage our own information, which allows us to broadcast messages safely in our own microcosm.

PS Screenshots coming soon!

Update 1 - Screenshot 1

Update 2 - Latest Screenshot

Get TinyURL from PL/SQL - APEX

Here is an example of how to use a PL/SQL function to convert an URL into a TinyURL using their api. This is very simple stuff yet it can save a lot of space!

create or replace FUNCTION get_tinyURL(t_url IN VARCHAR2)
RETURN VARCHAR
IS
http_req utl_http.req;
http_resp utl_http.resp;
res_value VARCHAR2(4000);
BEGIN
--utl_http.set_proxy('http://www.yourproxy.com:80'); --If you need to specify a proxy use this.
http_req := utl_http.begin_request('http://tinyurl.com/api-create.php?url=' || t_url, 'GET');
http_resp := utl_http.get_response(http_req);
utl_http.read_text(http_resp,res_value);
utl_http.end_response(http_resp);

return res_value;

END get_tinyURL;

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.

Post Updates to Twitter from JAVA

Here is some sample java code you can use to post updates to Twitter from java. This is just a simple POST method, if you are looking for a full Twitter library head to Twitter Development Talk. I implemented this code on the OraTweetBot which I will make available soon.

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.net.InetSocketAddress;
import java.net.SocketAddress;
import java.net.URL;
import java.net.URLConnection;
import java.net.URLEncoder;

public class Main {
   public static void main(String[] args) {
       SendTwitter(args[0]);
   }
       public static void SendTwitter(String msg ) {
       try {
           int proxyon = 1;
           String proxyurl = "www-yourproxy.com";
           int proxyport = 80;
           String twitteruser = "username";
           String twitterpass = "password";
           SocketAddress addr = null;
           java.net.Proxy proxy = null;
           URLConnection connection = null;
           String credentials = twitteruser + ":" + twitterpass;
         
           if (proxyon == 1){
                 addr = new InetSocketAddress(proxyurl, proxyport);
                 proxy = new java.net.Proxy(java.net.Proxy.Type.HTTP, addr);
           }
         
           String encodecredentials = new sun.misc.BASE64Encoder().encode (credentials.getBytes());
         
           URL url = new URL("http://twitter.com/statuses/update.xml");
           String encodedData = URLEncoder.encode(msg, "UTF-8");

           if (proxyon == 1){
               connection = url.openConnection(proxy);
           }
           else{
               connection = url.openConnection();
           }
         
           connection.setRequestProperty( "Authorization", "Basic " + encodecredentials);
           connection.setDoOutput(true);

           OutputStreamWriter out = new OutputStreamWriter(connection.getOutputStream());
           out.write("status=" + encodedData);
           out.close();

           BufferedReader in = new BufferedReader(new InputStreamReader(connection.getInputStream()));          
           String decodedString;
           while ((decodedString = in.readLine()) != null) {
               System.out.println(decodedString);
           }
           in.close();

           } catch (Exception e) {
               System.out.println(e.toString());
           }
   }

}

Getting friends timeline from Twitter to Oracle APEX (PL/SQL)

Now that you have posted your Twitter updates from APEX, you might want to read your friends time line. The procedure is very similar to posting of updates, except this time you want to receive the XML and process it for display inside a SQL query report. The returning XML will show the 20 most recent statuses posted in the last 24 hours from the authenticating user and that user's friends.

create or replace
FUNCTION get_twitter_timeline(t_user IN VARCHAR2,   t_pass IN VARCHAR2)
RETURN XMLTYPE
IS
http_req utl_http.req;
http_resp utl_http.resp;
t_update_send VARCHAR2(200);
res_value VARCHAR2(4000);
XML_RETURN CLOB;
BEGIN
t_update_send := '--head';
--utl_http.set_proxy('http://www-yourproxy.com:80'); --If you need to specify a proxy use this.
http_req := utl_http.begin_request('http://twitter.com/statuses/friends_timeline.xml',   'POST',   utl_http.http_version_1_1);
utl_http.set_response_error_check(TRUE);
utl_http.set_detailed_excp_support(TRUE);
utl_http.set_body_charset(http_req,   'UTF-8');
utl_http.set_header(http_req,   'User-Agent',   'Mozilla/4.0');
utl_http.set_header(http_req,   'Content-Type',   'application/x-www-form-urlencoded');
utl_http.set_header(http_req,   'Content-Length',   to_char(LENGTH(t_update_send)));
utl_http.set_transfer_timeout(to_char('60'));
utl_http.set_authentication(http_req,   t_user,   t_pass,   'Basic');
utl_http.write_text(http_req,   t_update_send);
http_resp := utl_http.get_response(http_req);
BEGIN
WHILE 1 = 1
LOOP
 utl_http.read_line(http_resp,   res_value,   TRUE);
 XML_RETURN := XML_RETURN || res_value;
END LOOP;
EXCEPTION
WHEN utl_http.end_of_body THEN
NULL;
END;
utl_http.end_response(http_resp);
return XMLTYPE(XML_RETURN);
EXCEPTION
WHEN others THEN
XML_RETURN := sqlerrm;
RAISE;
END get_twitter_timeline;

Usage:

SELECT EXTRACTVALUE(VALUE(twitter), '/status/user/name')  AS NAME
, EXTRACTVALUE(VALUE(twitter), '/status/text') AS TEXT
, EXTRACTVALUE(VALUE(twitter), '/status/created_at') AS CREATED_AT
, EXTRACTVALUE(VALUE(twitter), '/status/source') AS SOURCE
, EXTRACTVALUE(VALUE(twitter), '/status/user/screen_name') AS SCREEN_NAME
, EXTRACTVALUE(VALUE(twitter), '/status/user/profile_image_url') AS PROFILE_IMAGE_URL
FROM
TABLE(XMLSEQUENCE(EXTRACT(get_twitter_timeline('twitter_user','twitter_password'), '/statuses/*'))) twitter

Note that according to Twitter API the public timeline is cached for 60 seconds so requesting it more often than that is a waste of resources

Happy Posting :)

Post Updates to Twitter from Oracle APEX (PL/SQL)

I started a pet project called OraTweet. It is an Oracle internal "Twitter" with some key advantages such as the ability to follow groups and IM (XMPP/Jabber) integration. Aside from having our own internal "tweets" a user can direct a message @oratweet and the application will post it to the generic OraTweet Twitter account. Also the user will be able to store its own credentials and post to its own account (ie @noelportugal). I started playing with the Twitter API and first start sending updates with a combination of a Java Class to execute a shell Curl command. I soon realized that was not the best way to do it, so I went to work and got the following Procedure. (note that this is the quick and dirty version, feel free to extend it and make it more foolproof)

CREATE OR REPLACE PROCEDURE update_twitter(t_user IN VARCHAR2,   t_pass IN VARCHAR2,   t_update IN VARCHAR2) AS
http_req utl_http.req;
http_resp utl_http.resp;
h_name VARCHAR2(255);
h_value VARCHAR2(1023);
t_update_send VARCHAR2(4000);
res_value VARCHAR2(32767);
show_header NUMBER := 0;--0 False, 1 True
show_xml NUMBER := 1;--0 False, 1 True
BEGIN
t_update_send := 'status=' || SUBSTR(t_update,   1,   140) || '';
--utl_http.set_proxy('http://www,yourpoxy.com:80'); --If you need to specify a proxy un comment this line.
http_req := utl_http.begin_request('http://twitter.com/statuses/update.xml',   'POST',   utl_http.http_version_1_1);
utl_http.set_response_error_check(TRUE);
utl_http.set_detailed_excp_support(TRUE);
utl_http.set_body_charset(http_req,   'UTF-8');
utl_http.set_header(http_req,   'User-Agent',   'Mozilla/4.0');
utl_http.set_header(http_req,   'Content-Type',   'application/x-www-form-urlencoded');
utl_http.set_header(http_req,   'Content-Length',   to_char(LENGTH(t_update_send)));
utl_http.set_transfer_timeout(to_char('60'));
utl_http.set_authentication(http_req,   t_user,   t_pass,   'Basic');
utl_http.write_text(http_req,   t_update_send);
http_resp := utl_http.get_response(http_req);

DBMS_OUTPUT.PUT_LINE('status code: ' || http_resp.status_code);
DBMS_OUTPUT.PUT_LINE('reason phrase: ' || http_resp.reason_phrase);

IF show_header = 1 THEN
FOR i IN 1 .. utl_http.get_header_count(http_resp)
LOOP
utl_http.get_header(http_resp,   i,   h_name,   h_value);
DBMS_OUTPUT.PUT_LINE(h_name || ': ' || h_value);
END LOOP;
END IF;

IF show_xml = 1 THEN
BEGIN
WHILE 1 = 1
LOOP
utl_http.read_line(http_resp,   res_value,   TRUE);
DBMS_OUTPUT.PUT_LINE(res_value);
END LOOP;

EXCEPTION
WHEN utl_http.end_of_body THEN
NULL;
END;
END IF;

utl_http.end_response(http_resp);

EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
RAISE;

END update_twitter;

Usage:

begin
UPDATE_TWITTER('my_twitter_acct','my_twitter_pass','Hello World from Oracle PL/SQL and APEX http://apextoday.blogspot.com');
end;

My following post will include on how to create an XMPP/Jabber listener with Java and how I hooked it up to my application.

***Update I just blogged about Getting friends timeline from Twitter to APEX (PL/SQL)

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