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.

Comments are closed.