Ora_Tweet – Tweet From Oracle, A PL/SQL Twitter API

Get the latest source from the ORA Tweet project page at sourceforge.

I had some free time this last Saturday night (after the family was all in bed) and I wanted to do a little mini-project. I don’t get to do enough PL/SQL anymore so I like to look for utilities and smaller fun things to code. While I was thinking about it, I was also following along on twitter to the posts that the people I follow had recently made.

I decided to put together a twitter procedure that would let me make posts from the database. When I first started coding it, it was going to just be a one off, fun thing with no real use. By the time I was finished though, I realized I do have a use case for it. I sometime run long running processes. Currently, I have the database email me a status when it’s finished. Now I will have it tweet to me. If there is an error, I would still have it email the details but the status message can be a short and sweet “it succeeded”" or “it failed” message.

ora_tweet

I set up a special twitter account (rather than using my own oracle_ace account) because I don’t think the people who follow me really want to see when my processes are finished. If you are concerned about others seeing your messages, you can protect your account so that only you can see the tweets. You can then follow that account and the messages as they come across. I have a twitter client on my blackberry, currently TwitterBerry, so I am always in touch with twitter.

This is the first version and all it does right now is allow you to send a status update. I may change it in the future to allow it to get status updates or direct messages. I’m not sure that a database package needs to get the public time line or friend/follower information. It would be easy enough to add those things though.

A caveat on its use: internet access from within a corporate firewall, especially from a database server, is an iffy proposition at best. About 50% of the places I have been over years don’t allow it at all. Some do allow it via a proxy (this code could easily be modified to handle a proxy). It seems to me that the larger a company, the less likely it is to allow internet access from a database server. Your mileage may vary, use at your own risk, yada yada yada.

The Code

On to the code. Twitter provides an API to make calls to the service. They recently added a more robust access method called OAuth. I am not using that method. I am using basic authentication. That’s all I believe a utility like this needs.

To send a status update, you send in your twitter user name and password. These are the same as what you would use to login at the twitter.com site. You also send in a status. Whatever you want, up to 140 characters. If you send in a string greater than 140 characters, I truncate it (even though the API would do it for me).

The spec:

CREATE OR REPLACE PACKAGE ora_tweet
AS

  FUNCTION tweet
    (
      p_user IN VARCHAR2,
      p_pwd IN VARCHAR2,
      p_string IN VARCHAR2 )
    RETURN BOOLEAN;

END ora_tweet;
/

You can see that it is a very basic package at this point. If I add new API calls, I will just add them to this package and keep it all together.

Sample Call

To make a call, you would do something like this (you would need to enter your username and password where it says twitter_user and twitter_password:

SET SERVEROUTPUT ON

BEGIN

  IF ora_tweet.tweet
    (
      p_user => 'twitter_username',
      p_pwd => 'twitter_password',
      p_string => 'ora_tweet v1.0 is complete!' )
  THEN
    dbms_output.put_line('Success!');
  ELSE
    dbms_output.put_line('Failure!');
  END IF;

END;
/

You don’t need to have serveroutput on. I display the results from the call for debugging purposes. Once you have tested and get it to your satisfaction, you can get rid of the set serverout call.

With serveroutput on, the results look like this:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
      <?xml version="1.0" encoding="UTF-8"?>
   <status>
   <created_at>Sun Mar 15 13:53:15 +0000 2009</created_at>
   <id>1331361038</id>
   <text>ora_tweet v1.0 is complete!</text>
   <source>web</source>
   <truncated>false</truncated>
   <in_reply_to_status_id></in_reply_to_status_id>
   <in_reply_to_user_id></in_reply_to_user_id>
   <favorited>false</favorited>
   <in_reply_to_screen_name></in_reply_to_screen_name>
   <user>
   <id>24484454</id>
   <name>lewis cunningham</name>
   <screen_name>ora_tweet</screen_name>
   <location></location>
   <description></description>
   <profile_image_url>

http://static.twitter.com/images/default_profile_normal.png

   </profile_image_url>
   <url></url>
   <protected>false</protected>
   <followers_count>1</followers_count>
   </user>
   </status>
   Success!
      PL/SQL procedure successfully completed.

If you see a different type of message, say like an authentication error, you probably have the wrong password or spelled you username wrong. I’ve only spent about an hour on this so the exception handling is not especially robust but it suffices for my needs.

The Package Body

CREATE OR REPLACE PACKAGE BODY ora_tweet
AS
  twit_host VARCHAR2(255) := 'twitter.com';
  twit_protocol VARCHAR2(10) := 'http://';

  -- URL for status updates
  tweet_url VARCHAR2(255) := '/statuses/update.xml';

  FUNCTION tweet
    (
      p_user IN VARCHAR2,
      p_pwd IN VARCHAR2,
      p_string IN VARCHAR2 )
    RETURN BOOLEAN
  AS
    v_req   UTL_HTTP.REQ;
    v_resp  UTL_HTTP.RESP;
    v_value VARCHAR2(1024);
    v_status VARCHAR2(160);
    v_call VARCHAR2(2000);
  BEGIN

    -- Twitter update url
    v_call := twit_protocol ||
              twit_host ||
              tweet_url;

    -- encoded status tring
    v_status := utl_url.escape(
      url => 'status=' || SUBSTR(p_string,1,140));

    -- Has to be a POST for status update
    v_req := UTL_HTTP.BEGIN_REQUEST(
      url => v_call,
      method =>'POST');

    -- Pretend we're a moz browser
    UTL_HTTP.SET_HEADER(
      r => v_req,
      name => 'User-Agent',
      value => 'Mozilla/4.0');

    -- Pretend we're coming from an html form
    UTL_HTTP.SET_HEADER(
      r => v_req,
      name => 'Content-Type',
      value => 'application/x-www-form-urlencoded'); 

    -- Set the length of the input
    UTL_HTTP.SET_HEADER(
      r => v_req,
      name => 'Content-Length',
      value => length(v_status));

    -- authenticate with twitter user/pass
    UTL_HTTP.SET_AUTHENTICATION(
      r => v_req,
      username => p_user,
      password => p_pwd );

    -- Send the update
    UTL_HTTP.WRITE_TEXT(
      r => v_req,
      data => v_status );

    -- Get twitter's update
    v_resp := UTL_HTTP.GET_RESPONSE(
      r => v_req);

    -- Get the update and display it,
    -- only useful for debugging really
    LOOP
      UTL_HTTP.READ_LINE(
        r => v_resp,
        data => v_value,
        remove_crlf => TRUE);

      DBMS_OUTPUT.PUT_LINE(v_value);
    END LOOP;

    -- Close out the http call
    UTL_HTTP.END_RESPONSE(
      r => v_resp);

    RETURN TRUE;

  EXCEPTION
    -- normal exception when reading the response
    WHEN UTL_HTTP.END_OF_BODY THEN
      UTL_HTTP.END_RESPONSE(
        r => v_resp);
      RETURN TRUE;

    -- Anything else and send false
    WHEN OTHERS THEN
      UTL_HTTP.END_RESPONSE(
        r => v_resp);
      Dbms_Output.Put_Line ( 'Request_Failed: ' ||
                       Utl_Http.Get_Detailed_Sqlerrm );
      Dbms_Output.Put_Line ( 'Ora: ' || Sqlerrm );
      RETURN FALSE;

  END;

END ora_tweet;
/

That’s about it for now. If you want any additional calls to the twitter API added, let me know.

Download the source for the spec, body and sample call to ORA_TWEET.

LewisC

Technorati : , , , , ,

You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

23 Comments »

 
  • Nice. I while ago I created a store proc to do the same…post and get Twitter timeline …

    http://apextoday.blogspot.com/2008/06/post-updates-to-twitter-from-apex-plsql.html

    http://apextoday.blogspot.com/2008/06/getting-friends-timeline-from-twitter.html

    Its nice to put all in a package

  • Lewi says:

    Hi Noel,

    Nice. Looks like we were thinking along the same lines. We should get together and develop a complete twitter package for Oracle. It would be nice to include all of the functionality.

    LewisC

  • Bob Watkins says:

    Not to be a curmudgeon, because this is definitely a cool mini project, but I have to question whether all your followers need to know that your overnight run succeeded or failed. This doesn’t seem to be a good fit for Twitter, as I understand the service.

    A much better solution, and one being adopted more and more, is to use RSS. Post your statuses to an RSS feed, then subscribe to it.

    BobW

  • Lewi says:

    Bob,

    Good point. As a matter of a fact, I made the same point in the post, about half way down:

    I set up a special twitter account (rather than using my own oracle_ace account) because I don’t think the people who follow me really want to see when my processes are finished.

    The screenshot that I show is of that special account, not my oracle_ace account.

    LewisC

  • [...] Ora_Tweet – Tweet From Oracle, A PL/SQL Twitter API | Database … [...]

  • Jornica says:

    On an Oracle 11g database with not the proper ACL set, you will receive the following  error (amongst others):
    ORA-24247: network access denied by access control list (ACL)
    By defining an ACL this error will disappear:
    BEGIN
    – Note USER is owner of ORA_TWEET dbms_network_acl_admin.create_acl(acl => ‘Twitter.xml’, description => ‘Twitter message’, principal => USER, is_grant => TRUE, privilege => ‘connect’, start_date => NULL, end_date => NULL); dbms_network_acl_admin.assign_acl(acl => ‘Twitter.xml’, host => ‘twitter.com’, lower_port => 80, upper_port => 80); COMMIT;END;/
     

  • Lewi says:

    True.  I was using Oracle 10g XE.  I’ll add a note to the download about 11g.

    Thanks,

    LewisC

     

  • [...] I posted an article about ORA_Tweet, an Oracle Twitter Client. I was asked by someone reading the code why I put several variables in the BODY of the package [...]

  • [...] Cunningham at the Database Geek blog  has shared in a post I stumbled over last week entitled Ora_Tweet – Tweet From Oracle, A PL/SQL Twitter API . Simple and elegant, he has shared an idea that would let him make posts from the database to help [...]

  • [...] Twitter API Published 15 April 09 03:21 | OraBI Ist schon ein paar Tage alt dieses Posting zum Ora_Tweet beim Database Geek.Aber da ich Microblogging cool finde und das so eine klasse Umsetzung ist, will [...]

  • [...] you want to give a try, follow the original post on database-geek.com (uh… we are not related FYI) with the whole instructions. [...]

  • Chris says:

    Great post!!  This will be extremely helpful…I am running 11g, but I’ve never added an ACL as described by Jornica.  Any chance you could explain how to make that entry?Once again great work!!  Thanks a ton. 

  • Chris says:

    Alright figured it out!!  Thanks Lewi and Jornica!!<br>One more question…  How could I use the results from a query as the value for p_string.<br>For example if I had:<br>select max(data_dt) from table1<br>and I wanted my tweet to be the result….  Thanks again guys, you are great!!! 

  • LewisC says:

    You should be able to just tweet it directly:select ora_tweet.tweet( user, pwd, max(data_dt))  from table1
    I haven’t tried but I don’t see why that wouldn’t work.Ack. it returns a boolean.  That’ll give an error, I believe (can’t try at the moment).Change the function (or add another one) that returns varchar2 instead of boolean.  It can just be a wrapper function for the one that is already there.  That’ll work.  I’ll add that to my to do list for the tool.Thanks,
    LewisC
     
     

  • Chris says:

    Thanks for the quick reply.  I updated the package and the package body to return varchar2 and tried running the query you suggested.  I got a series of errors ORA-29273, 06512, 29261, blahh blahh blahh…  Complete Noob on this stuff, so I might have to wait for your next iteration. ;)  Thanks again, love the site!!

  • WJFuoco says:

    This is great!  I’m a PL/SQL programmer and an avid Twitter user and was looking for some information on the utl_http package and came across your post.  This is just fantastic, I love how you connected them!  I’ll be following you as soon as I click Submit!

  • Barry Cooper says:

    Hi Lewis – late to the party as i only stumbled upon this post last night.A facinating article. As an Oracle Apps person this has excellent possibilities with some of the lengthier ERP processes (mainly payroll).Now, could you then get it to process a response if you replied @tweet for example?Regards Barry

  • Steve Lewonka says:

    Nice. Added Ora_Tweet in as a notication method in EM Grid Control. Works like a champ. I like Twitter as an easy method I can go and retrieve statuses, rather than always having everyting pushed to my mobile.

  • LewisC says:

    Barry, working on that.  Just been short of time lately.Steve, awesome idea!  I wish I thought of that.  I may have to blog about it.  ;-) LewisC

  • bodydetox says:

    Twitter is very addictive. I like Twitter more than blogging. the messages are short and straight to the point.

  • [...] I’ve heard of plants tweeting, but until I saw Lewis Cunningham’s post announcing ORA_Tweet, I hadn’t even thought of getting Oracle Database onto [...]

  • Caramoan says:

    Twitter in some ways is much better than blogging. I love to Twitter my everyday activities on my friends and relatives.
    ***