my recent reads..

Atomic Accidents: A History of Nuclear Meltdowns and Disasters; From the Ozark Mountains to Fukushima
Power Sources and Supplies: World Class Designs
Red Storm Rising
Locked On
Analog Circuits Cookbook
The Teeth Of The Tiger
Sharpe's Gold
Without Remorse
Practical Oscillator Handbook
Red Rabbit

Saturday, April 11, 2009

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 is.gd 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 )
RETURN VARCHAR2;

FUNCTION encode_text(
p_text IN VARCHAR2 )
RETURN VARCHAR2;
encode_url the main wrapper around the http://is.gd 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:
45c45
< 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:
BEGIN
DBMS_OUTPUT.ENABLE;
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 http://database-geek.com/2009/03/15/ora_tweet-tweet-from-oracle-a-plsql-twitter-api/' )
THEN
dbms_output.put_line('Success!');
ELSE
dbms_output.put_line('Failure!');
END IF;
END;
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: ORA_Tweet_With_Shorturls.zip

3 comments:

LewisC said...

Paul,

awesome addition. I've updated the zip to include your changes. This is a very cool addition to the package.

Thanks!

LewisC

Unknown said...

No problem Lewis. Thanks for inspiring the fun;-)

Anonymous said...

Its really a nice blog for....architects in bangalore , architects in bangalore , interior designers in Bangalore , interior designers in Bangalore , architects in bangalore , architects in bangalore , interior designers in bangalore