Fork me on GitHub

Project Notes

Database Tweets

Tweeting from your Oracle database with short urls

Notes

NB: I originally posted this back in 2009

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. 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 modification was to pair that idea up with ORA_Tweet.

There are a range of URL shortening services available. 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 is 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;

Sources:

Credits and References

About LCK#141 DatabaseOracle
Project Source on GitHub Return to the Project Catalog

This page is a web-friendly rendering of my project notes shared in the LittleCodingKata GitHub repository.

LittleCodingKata is my collection of programming exercises, research and code toys broadly spanning things that relate to programming and software development (languages, frameworks and tools).

These range from the trivial to the complex and serious. Many are inspired by existing work and I'll note credits and references where applicable. The focus is quite scattered, as I variously work on things new and important in the moment, or go back to revisit things from the past.

This is primarily a personal collection for my own edification and learning, but anyone who stumbles by is welcome to borrow, steal or reference the work here. And if you spot errors or issues I'd really appreciate some feedback - create an issue, send me an email or even send a pull-request.