Tweeting directly from Oracle

Finally set up the bespoke Oracle Twitter using OAuth rather than using RSS/XML from Twitterfeed.

{easy once you've spent a couple of days working out what the hell is all the fuss about OAuth}

The steps are:

1. set up an application on your Twitter account here

2. make a note of the consumer and account keys and secrets

3. in Oracle create urlencode function:
-=-=-=-=-=-=-

create or replace function urlencode( p_str in varchar2 ) return varchar2
as
    /* FROM: http://www.orafaq.com/forum/t/48047/0/
     * POSTED BY USER: http://www.orafaq.com/forum/u/45693/0/
     * KUDOS!
     */
    l_tmp   varchar2(6000);
    l_bad   varchar2(100) default ' >%}\~];?@&<#{|^[`/:=$+''"';
    l_char  char(1);
begin
    for i in 1 .. nvl(length(p_str),0) loop
        l_char :=  substr(p_str,i,1);
        if ( instr( l_bad, l_char ) > 0 )
        then
            l_tmp := l_tmp || '%' ||
                            to_char( ascii(l_char), 'fmXX' );
        else
            l_tmp := l_tmp || l_char;
        end if;
    end loop;

    return l_tmp;
end;
-=-=-=-=-=-=-=





4. update the XXXXX codes below with the relevant values from step 1 and run code within an SQL worksheet window - create a package from this, go on you know how - :

-=-=-=-=-=


SET SERVEROUTPUT ON

DECLARE

  l_oauth_request_token_url CONSTANT VARCHAR2 (500) := 'http://api.twitter.com/1/statuses/update.xml';
  l_oauth_consumer_key CONSTANT VARCHAR2 (500) := 'XXXX';
  l_oauth_token  CONSTANT VARCHAR2 (500) := 'XXXXX';
  l_oauth_secret CONSTANT VARCHAR2 (500) := 'XXXXX';
  l_oauth_nonce VARCHAR2 (500);
  l_oauth_signature_method CONSTANT VARCHAR2 (10) := urlencode ('HMAC-SHA1');
  l_oauth_timestamp VARCHAR2 (100);
  l_oauth_version CONSTANT VARCHAR2 (5) := urlencode ('1.0');
  l_oauth_consumer_secret CONSTANT VARCHAR2 (500) := 'XXXXXX';

  l_http_method VARCHAR2 (5) := 'POST';
  l_oauth_base_string VARCHAR2 (2000);

  l_oauth_key VARCHAR2 (500) := l_oauth_consumer_secret || '&' || l_oauth_secret ;

  l_sig_mac RAW (2000);
  l_base64_sig_mac VARCHAR2 (100);

  http_req UTL_HTTP.req;
  http_resp UTL_HTTP.resp;

  l_update_send VARCHAR2(2000);
  l_oauth_header  VARCHAR2(2000);

  l_line  VARCHAR2(1024);

  resp_name  VARCHAR2(256);
  resp_value VARCHAR2(1024);

  l_content varchar2(140) := urlencode('hello world');

  l_random varchar2(15);

BEGIN

select dbil.urlencode((sysdate - to_date('01-01-1970','DD-MM-YYYY')) * (86400)) into
l_oauth_timestamp from dual;

  -- RANDOM oauth_nonce
  SELECT dbms_random.string('A', 15)
  INTO l_random
  FROM DUAL;

  SELECT dbil.urlencode (UTL_ENCODE.base64_encode(UTL_I18N.string_to_raw (l_random, 'AL32UTF8')))
  INTO l_oauth_nonce
  FROM DUAL;



  l_oauth_base_string := l_http_method
                          || '&'
                          || urlencode (l_oauth_request_token_url)
                          || '&'
                          || urlencode ( 'oauth_consumer_key'
                              || '='
                              || l_oauth_consumer_key
                              || '&'
                              || 'oauth_nonce'
                              || '='
                              || l_oauth_nonce
                              || '&'
                              || 'oauth_signature_method'
                              || '='
                              || l_oauth_signature_method
                              || '&'
                              || 'oauth_timestamp'
                              || '='
                              || l_oauth_timestamp
                              || '&'
                              || 'oauth_token'
                              || '='
                              || l_oauth_token
                              || '&'
                              || 'oauth_version'
                              || '='
                              || l_oauth_version
                              || '&'
                              || 'status'
                              || '='
                              || l_content);
                           
  DBMS_OUTPUT.put_line (l_oauth_base_string);

  l_sig_mac := dbms_crypto.mac (  utl_i18n.string_to_raw (l_oauth_base_string, 'AL32UTF8')
                                , DBMS_CRYPTO.hmac_sh1
                                , UTL_I18N.string_to_raw (l_oauth_key, 'AL32UTF8'));
                             
  DBMS_OUTPUT.put_line ('Combined sig: ' || l_oauth_key);
                             
  l_base64_sig_mac := UTL_RAW.cast_to_varchar2 (UTL_ENCODE.base64_encode (l_sig_mac));

  DBMS_OUTPUT.put_line ('MAC Signature (Base64-encoded): ' ||  l_base64_sig_mac);

  l_update_send := l_oauth_request_token_url || '?status=' || l_content;
                 
    http_req := UTL_HTTP.begin_request (  l_update_send
                                        , l_http_method
                                        , UTL_HTTP.http_version_1_1);
                                     
   DBMS_OUTPUT.put_line ('UPDATE URL ' || l_update_send);
 
   UTL_HTTP.set_response_error_check (TRUE);
   UTL_HTTP.set_detailed_excp_support (TRUE);
 


    l_oauth_header := 'OAuth oauth_nonce="' || l_oauth_nonce || '", '
                      || 'oauth_signature_method="'|| l_oauth_signature_method || '", '
                      || 'oauth_timestamp="'|| l_oauth_timestamp || '", '
                      || 'oauth_consumer_key="'|| l_oauth_consumer_key || '", '
                      || 'oauth_token="' || l_oauth_token || '", '
                      || 'oauth_signature="' || urlencode (l_base64_sig_mac) || '", '
                      || 'oauth_version="' || l_oauth_version || '"';
                   
    utl_http.set_header ( r => http_req,
                          NAME => 'Authorization', VALUE => l_oauth_header);
                       
    DBMS_OUTPUT.put_line  ('HEADER: ' || l_oauth_header);                      
                       
    utl_http.write_text(  r => http_req, DATA => l_content);
   
    http_resp := utl_http.get_response(r => http_req);
   
   DBMS_OUTPUT.put_line('GETTING RESPONSE HEADERS! ');
 
   FOR i IN 1..utl_http.get_header_count(http_resp) LOOP
    utl_http.get_header(http_resp, i, resp_name, resp_value);
    dbms_output.put_line(resp_name || ': ' || resp_value);
   END LOOP;
 
  DBMS_OUTPUT.put_line('Getting content:');
  BEGIN
      LOOP
        utl_http.read_line(http_resp, resp_value, TRUE);
        dbms_output.put_line(resp_value);
      END LOOP;
   
      EXCEPTION
      WHEN utl_http.end_of_body THEN
        DBMS_OUTPUT.put_line('No more content.');
  END;

   utl_http.end_response(r => http_resp);


  EXCEPTION
    when others then
      DBMS_OUTPUT.put_line('HTTP ERROR: ' || utl_http.get_detailed_sqlerrm);

END;



-=-=-=-=-=

And that's it - you should be tweeting away from your Oracle Database - comment if you have any difficulties.

I am in the process now of changing the twitter feeds to contain all information rather than just the top 5.

Candlestick/Markov/Trace

Just back from hols and will be documenting here the new Candlestick/Markov/Trace analysis that I have been working on - isnt that what holidays are for ?

But as a quick indicator, I am up to my eighth straight 11.5%; just had TOM, WSG, SEA and BRT.

AHT and new system

AHT did rather well yesterday, I was out at 11.5%, but its still going up.

New system delivered and setup - to the right is the new system screen shot of processor cores under calc mode - oasis is parallel processing

Currently into SEA, KMG, SIE, SLE.

AHT

Today's AHT success coefficients are 1900/221 which are pretty good