Tweeting from your database with short urls

There's something cheekily enjoyable about getting all manner of 'non-human' things to tweet. 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 twitter.

Saturdays are good for little projects, so I thought I would add URL shortening service today;-)

Since twitter famously limits you to 140 characters, it is conventional to use a url-shortening service to include hyperlinks in your tweet. So my little play for today was to pair that idea up with ORA_Tweet.

There are a range of URL shortening services available including snipurl and tinyurl (here's a survey of services). I've been using for a while though, since it sports the simplest GET request 'api' you could imagine, making it great for ad-hoc programmatic use.

So I add an extra package called SHORT_URL which has just two functions:
  FUNCTION encode_url(
p_url IN VARCHAR2 )

FUNCTION encode_text(
p_text IN VARCHAR2 )
encode_url the main wrapper around the call to get a short url for the one you provide.

encode_text is a more convenient function that takes a block of text, and will replace all the urls it contains with the appropriate shortened versions.

Then there's just one change to the ORA_TWEET package body:
< url => 'status=' || SUBSTR( short_url.encode_text(p_string) ,1,140));
> url => 'status=' || SUBSTR(p_string,1,140));
Now you can go wild with URLs in your database tweets:
IF ora_tweet.tweet
p_user => 'twitter_username',
p_pwd => 'twitter_password',
p_string => 'ora_tweet v1.1 is complete! Now with url shortening ... see' )
Building on Lewis' original justification for building ORA_Tweet, you could for example include links to a report page or admin screen when your long-running process sends you its "I'm done" tweet.

That's if you need justification;-)

If you are interested, the source is up on my github account now: