Using Twitter from pl/sql as an alerting tool

There are many ways to send an error-messages from the database or application to the database- or application administrator. Write the alert to a log table is the most common way. Also commonly used is sending an email alert with the error-message.  Twitter may well serve as an alternative to sending an alert. It is advisable, special for this usage, to create a new twitter account. Administrators can then become followers of this account and then kept informed by their iPhone (or other cool gadget), even when they are sitting in the sun on a pavement. Who would not want to sit on a pavement?

First we have to make a new twitter account, lets say  ‘myappalerts’, password ‘coldbeer’.
Afterwards create a datebase procedure, something like this one:

procedure tweet(p_user     in   varchar2
               ,p_password in   varchar2
               ,p_message  in   varchar2
               ,p_debug    in   boolean := true)  is

  l_host        constant varchar2(20) := 'api.twitter.com';
  l_protocol    constant varchar2(20) := 'http://';              

  l_request      utl_http.req;
  l_response     utl_http.resp;
  l_tweet_url    varchar2(255);
  l_content      varchar2(255);
  l_message      varchar2(140);
  l_line         varchar2(1024);

begin
  l_message   := substr(p_message,1,140); -- the tweet may contain max 140 char
  l_content   := 'status='||utl_url.escape(l_message);

  -- twitter update url (http://apiwiki.twitter.com/Twitter-REST-API-Method%3A-statuses%C2%A0update)
  l_tweet_url := l_protocol||l_host||'/1/statuses/update.xml'; 

  -- build the request statement
  l_request   := utl_http.begin_request(url => l_tweet_url
                                       ,method => 'POST');
  -- compose the header, pretend we 'r using a mozilla browser with a html-form
  utl_http.set_header(r => l_request
                     ,name  => 'User-Agent'    ,value => 'Mozilla/4.0');
  utl_http.set_header(r => l_request
                     ,name  => 'Content-Type'  ,value => 'application/x-www-form-urlencoded');
  utl_http.set_header(r => l_request
                     ,name  => 'Content-Length',value => length(l_content));
  -- authenticate the user
  utl_http.set_authentication(r => l_request
                     ,username  => p_user
                     ,password  => p_password);
  -- write the content
  utl_http.write_text(r => l_request
                     ,data => l_content); 

  -- get the response
  begin
  l_response := utl_http.get_response(r => l_request);
      if p_debug then
        begin
          loop
            utl_http.read_line(r => l_response
                              ,data => l_line
                              ,remove_crlf => true);
            dbms_output.put_line(l_line);
          end loop;
        exception
          when utl_http.end_of_body then
            -- no more data
            null;
        end;
      end if;
    -- end the reponse
    utl_http.end_response(r => l_response);
  end;  -- of the response

exception
  when others then
    utl_http.end_response(r => l_response);
    dbms_output.put_line('request failed: ' ||utl_http.get_detailed_sqlerrm);
    raise;
end tweet;

I think there are enough comment-lines in this source  to understand the code. Just one exception, one line 18 I’ am using the function utl_url.escape.

l_content   := 'status='||utl_url.escape(l_message);

This function returns a string with illegal characters (and optionally reserved characters) escaped using the %2-digit-hex-code format.

Now we can test the procedure (you don’t want this alert sitting on a sidewalk cafe!):

begin
  tweet('myappalerts','coldbeer','ORA-00600 [723][51202][1][51200][][]');
end;

On an Oralce 11g database you could get an (error)message like this:

Error report:
ORA-20101: ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-24247: network access denied by access control list (ACL)

For more details on this, and the solution!, Oracle 11g: Access Control List and ORA-24247
More information about using the Twitter API,  Twitter API Documentation

Ratings:

VN:F [1.9.13_1145]

please wait...

Rating: 4.5/5 (2 votes cast)

Tags: pl/sql, twitter

• 

 Print This PDF Version