Based on a Presentation By Lewis Cunningham, Oracle ACE at JP Morgan Chase
Teach your database to “tweet” its status updates to a private account.
In my search for fun and exciting things to do with an Oracle database—call them “stupid database tricks”—I often find myself, in the deepest, darkest hours of the night, happily hacking away at things that might not matter to anyone but a geek. Sometimes, though, my excursions result in tools that could be useful for the masses. Such is the case with ORA_Tweet, a PL/SQL procedure that lets you create Twitter posts from inside your database.
What could be the possible use case, you might ask? Well, as you probably do, I often initiate long-running processes. Currently, I have the database email me a status report when the process is finished. But even better, with ORA_Tweet, I can have the database tweet its status to me—a short and sweet “it succeeded”” or “it failed” message (with more details about any failure emailed to me).
It almost goes without saying, but I’ll say it anyway: As with many other things you read on OTN, use the code here at your own risk; it has not been validated by Oracle and will not be supported by Oracle.
What is Twitter?
To understand the usefulness of an interface to Twitter, you need to know exactly what Twitter is.
The short answer is: I’m not really sure. It’s sort of like IM but without the expectation that any particular person will be there to answer. It’s very public. If you start your day off with a “Hello, Mom”, everyone who “follows” you (that is, who has configured their feed to include your messages) will see it. This capability is sometimes called “micro-blogging”.
Messages on Twitter are called “tweets” and cannot be longer than 140 characters. Trying to follow a conversation that is spread over multiple tweets can be difficult.
In general, the goal of Twitter for most people is to communicate and stay in touch with people you know, would like to know, or have interests in common. You follow people much like you would “friend” them on other social networks. The people you follow show up in your stream. People talk about whatever it is that’s on their minds. Sometimes you’ll see people replying to each other and at other times, they just want to share a thought.
The Twitter API
To make calls to Twitter from a database, one needs a method for plugging into Twitter. Fortunately for us, Twitter provides an API for performing almost any interaction that you might wish for, including the ability to post a tweet.
The Twitter API uses the REST design paradigm. REST identifies a set of resources that you can interact with. REST is a fairly simple architecture once you understand the basics. In the case of Twitter, we can change our file extension and that will change the results that we get back. Twitter can return XML or JSON. For this example, I will return XML.
The Twitter API also supports Basic Authentication. Basic authentication allows a user to pass in a user name/password associated with a URL. If you’ve ever tried to navigate to a Web site and it popped up a dialog and asked for a username and password, that site was probably protected by basic authentication. (Twitter has just recently started offering OAuth authentication.) We’ll go with basic authentication for this article.
Twitter documents the status update API like so:
statuses/update
Updates the authenticating user’s status. Requires the status parameter specified below. Request must be a POST. A status update with text identical to the authenticating user’s current status will be ignored.
URL: http://twitter.com/statuses/update.format
Formats: xml, json. Returns the posted status in requested format when successful.
Method(s): POST
Parameters:
- status. Required. The text of your status update. Be sure to URL encode as necessary. Should not be more than 140 characters.
- in_reply_to_status_id. Optional. The ID of an existing status that the status to be posted is in reply to. This implicitly sets the in_reply_to_user_id attribute of the resulting status to the user ID of the message being replied to. Invalid/missing status IDs will be ignored.
Returns: status element
The return value from a status update is not particularly important. As long as you don’t get an error, then your update was probably successful. The most likely reason for an error will be an invalid user name or password.
According to the Twitter documentation, the result will look like this (but formatted for either XML or JSON):
<status>
created_at
id
text
source
truncated
in_reply_to_status_id
in_reply_to_user_id
favorited
<user>
id
name
screen_name
description
location
profile_image_url
url
protected
followers_count
The Database
I think that’s enough about the Twitter API for now. Let’s talk about the goal of our little project. I want to be able to update my status by calling a PL/SQL stored procedure. I run Oracle Database XE, 10g Release 2, and 11g databases but there is no reason this code can’t work on any Oracle data from the last decade or so. The bulk of the code will be created by making calls to the built-in package UTL_HTTP.
UTL_HTTP
UTL_HTTP is a PL/SQL implementation of an HTTP requestor (or client). With UTL_HTTP you can make calls to a Web server and return the results to your database. What this means is that the Oracle database has had the ability to access Web services for many years; it was introduced in a limited form in Oracle8.
The Oracle8 UTL_HTTP package provided only two functions: REQUEST( url, proxy_url) and REQUEST_PIECES(url, max_pieces, proxy_url ). The url is the address of the site requested. The proxy was an option proxy url (could not authenticate, only worked with already authenticated proxies). Request could only return up to 2000 bytes from a web site. Request_Pieces could return up to max_pieces (max of 32k) of 2000 byte pieces.
By contrast, the version in Oracle Database 11g has at least 50 subprograms for authentication, cookie handling, proxy authentication, redirects, error handling, and even persistent connections. For our needs, we will only use a very small subset of these procedures.
The procedures that we will use in our Twitter client are:
SET_PROXY Procedure – Sets the proxy to be used for requests of HTTP or other protocols
BEGIN_REQUEST Function – Begins a new HTTP request. UTL_HTTP establishes the network connection to the target Web server or the proxy server and sends the HTTP request line
SET_HEADER Procedure – Sets the maximum number of times UTL_HTTP follows the HTTP redirect instruction in the HTTP responses to future requests in the GET_RESPONSE function
SET_AUTHENTICATION Procedure – Sets HTTP authentication information in the HTTP request header. The Web server needs this information to authorize the request.
WRITE_TEXT Procedure – Writes some text data in the HTTP request body
GET_RESPONSE Function – Reads the HTTP response. When the function returns, the status line and the HTTP response headers have been read and processed
READ_LINE Procedure – Reads the HTTP response body in text form until the end of line is reached and returns the output in the caller-supplied buffer
END_REQUEST Procedure – Ends the HTTP request
GET_DETAILED_SQLERRM Function – Retrieves the detailed SQLERRM of the last exception raised
We will also make a call to UTL_URL.escape. The escape procedure allows us to format a string so that it can be used in a URL. URLs do not like special characters or spaces. Escape replaces those characters with information that can be used instead.
Putting It Together
Now we put the code together. The goal here is to make it as simple to use as possible but also make it easy to enhance. I am packaging up the procedure as I think it makes a lot of sense to use a package even when you will only have a single procedure. When it comes time to extend the functionality, the package will just make life easier.
In the spec, we need a Twitter username and password, a string that will be our status update and optionally, a user name and password for a proxy.
The Spec
CREATE OR REPLACE PACKAGE ora_tweet
AS
/* ORA_TWEET
Author: Lewis Cunningham
Date: Marchish, 2009
Email: lewisc@rocketmail.com
Twitter: oracle_ace
Web: http://database-geek.com
License: Free Use
Version: 1.1
*/
FUNCTION tweet
(
p_user IN VARCHAR2,
p_pwd IN VARCHAR2,
p_string IN VARCHAR2,
p_proxy_url IN VARCHAR2 DEFAULT NULL,
p_no_domains IN VARCHAR2 DEFAULT NULL )
RETURN BOOLEAN;
END ora_tweet;
/
Short and sweet. Notice that the proxy url and domain list are both optional.
The Body
I will post the body below and explain each section. The full body is listed below that.
CREATE OR REPLACE PACKAGE BODY ora_tweet
AS
/* ORA_TWEET
Author: Lewis Cunningham
Date: Marchish, 2009
Email: lewisc@rocketmail.com
Twitter: oracle_ace
Web: http://database-geek.com
License: Free Use
Version: 1.1
*/
twit_host VARCHAR2(255) := ‘twitter.com’;
twit_protocol VARCHAR2(10) := ‘http://’;
— URL for status updates
tweet_url VARCHAR2(255) := ‘/statuses/update.xml’;
This is the header of the package body. The combination of twit_protocol, twit_host, and tweet_url make up the status update url. I separated the components so that future functionality could more easily be added. At this point, these could all be put into a single variable.
FUNCTION tweet
(
p_user IN VARCHAR2,
p_pwd IN VARCHAR2,
p_string IN VARCHAR2,
p_proxy_url IN VARCHAR2 DEFAULT NULL,
p_no_domains IN VARCHAR2 DEFAULT NULL )
RETURN BOOLEAN
AS
The procedure declaration matches the spec.
v_req UTL_HTTP.REQ; — HTTP request ID
v_resp UTL_HTTP.RESP; — HTTP response ID
v_value VARCHAR2(1024); — HTTP response data
v_status VARCHAR2(160); — Status of the request
v_call VARCHAR2(2000); — The request URL
The procedure variables. These will make more sense once we move into the code.
— Twitter update url
v_call := twit_protocol ||
twit_host ||
tweet_url;
The above code creates the fully fleshed out request URL.
— encoded status tring
v_status := utl_url.escape(
url => ‘status=’ || SUBSTR(p_string,1,140))
You may have spaces or special characters in your status update. The escape function replaces those with more acceptable characters. Notice that the string include a “status=” at the beginning. When this is added to the final URL, it will look like “?status=status update text”.
— Authenticate via proxy
— Proxy string looks like ‘http://username:password@proxy.com’
— p_no_domains is a list of domains not to use the proxy for
— These settings override the defaults that are configured at the database level
IF p_proxy_url IS NOT NULL
THEN
Utl_Http.set_proxy (
proxy => p_proxy_url,
no_proxy_domains => p_no_domains
);
END IF;
This is the proxy call. The format of the URL is specified in the comments. If your proxy does not need the user name and password, you can leave that off (and leave off the @). You can add an additional :port should you require it.
— Has to be a POST for status update
v_req := UTL_HTTP.BEGIN_REQUEST(
url => v_call,
method =>’POST’);
This call begins the request but still has not sent any specific data to the request beyond that it is a POST action.
— 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));
The three calls above send information that tells the Web site that the call is coming from a Mozilla browser, from a Web form, and that the data is of a certain length. This is about the minimal amount of information that you should send a Web site.
— authenticate with twitter user/pass
UTL_HTTP.SET_AUTHENTICATION(
r => v_req,
username => p_user,
password => p_pwd );
Here we are sending the Twitter user name and password. This is how we log into the system.
— Send the update
UTL_HTTP.WRITE_TEXT(
r => v_req,
data => v_status );
Here we send the v_status variable as a variable to the URL. This is the data that Twitter is expecting.
— 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;
The code above gets the Twitter response, loops through, displays it and finally, after the loop, closes the request. We return TRUE to show that we send the request and did not get an exception on the response.
We end with the exception handler.
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;
The UTL_HTTP.END_OF_BODY exception is a normal and expected exception. In the response loop, when you hit the end of the data, Oracle raises this exception. The WHEN others to catch any unexpected exceptions.
That’s all of it. It really isn’t that much code. Oracle, or I should say PL/SQL, really makes the process easy. It’s one of the reasons I love PL/SQL so much.
Using ORA_Tweet
The first thing to do is to set up a Twitter account for your messages. You may want to setup a special Twitter account for this purpose, rather than using your primary account. I don’t think the people who follow you really want to see when your 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 view the messages as they come across.
This is the first version and all it does right now is allow you to send a status update. It could easily be updated to allow it to get status updates or direct messages. I’m not sure that a database package needs to get the public tim line or friend/follower information. It would be easy enough to add those things though.
A caveat: 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 but not all. 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 Call
To call the procedure, you would have a block something like the following:
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 unless you want to see the entire response. 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 your username wrong. I’ve only spent about an hour on this so the exception handling is not especially robust but it suffices for most needs.
You can download the entire code base, including the sample call, from here.
Lewis Cunningham is an Oracle ACE Director and currently works for JP Morgan Chase in Tampa, Fla. Lewis has been coding cool PL/SQL tools and applications since 1993. When he’s not coding, you’ll probably find him writing. Follow his database adventures at database-geek.com and his adventures in the cloud at clouddb.info. You can follow his tweets at www.twitter.com/oracle_ace.