Thursday 7 February 2013

Send tweets from Oracle with OAuth

Twitter is a great real time social platform. Timelines and hashtags are a great way to communicate to an audience of subscribers relevant information.

There are lots of websites and applications which use twitter to provide content to their users. There are programming languages, Java, PHP, Python which have build Twitter API libraries to quickly send and receive content from Twitter but I haven't come accross a fully fledged library in PL/SQL, which would enable you to send a Tweet from PL/SQL using Twitter's latest oAuth security protocol. 

There are blogs out there which show you how to send a tweet from Oracle, but not many using the new oAuth security protocol.

The only blog post I have seen so far which uses PL/SQL and oAuth to send a tweet from Oracle is of Some coding hero's heroic mumblings an Oracle consultant. 

I have spend time reviewing his code with the comments made on his post which he has wrote in 2010 and managed to trim it down to use only one of his PL/SQL procedures.  The procedure below sends the 140 characters tweet  for you using oAuth. To be able to do this, as Some Coding Hero says, you will have to create a Twitter Application for your twitter handle. Actually you don't create an application you just sign up and obtain 4 security codes! Once you register your application with Twitter,  you are given 4 oAuth security Codes as follows:

With the above 4 oAuth security codes you can use Somecodingheros last block of code only to send a tweet from Oracle - I used Oracle Apex - like this:

new Twitter API 1.1 update and the use of Oracle Wallet is added to the script in green below on  21-JUN-2013

-- Twitter API 1.1 update
  l_oauth_request_token_url CONSTANT VARCHAR2 (500) := '';   
  l_oauth_consumer_key CONSTANT VARCHAR2 (500) := 'xxxxxxxxx';  
  l_oauth_token  CONSTANT VARCHAR2 (500) := 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxx';  
  l_oauth_secret CONSTANT VARCHAR2 (500) := 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';  
  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) := 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';  
  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);  
-- put the tweet in the urlencode function below 
  l_content varchar2(140) := urlencode('@somecodinghero thank you');  
  l_random varchar2(25);  

 -- Oracle Wallet
    utl_http.set_wallet('file:/home/app/oracle/product/11.2.0/dbhome_1/owm/wallets/oracle', 'putyourwalletpasswordhere');

 -- Get the timestamp  
  SELECT urlencode ((SYSDATE - TO_DATE ('01-01-1970', 'DD-MM-YYYY'))  * (86400)) 
  INTO l_oauth_timestamp  
  -- RANDOM oauth_nonce  
  SELECT dbms_random.string('A',25)  
  INTO l_random  
  SELECT urlencode (UTL_ENCODE.base64_encode(UTL_I18N.string_to_raw (l_random, 'AL32UTF8')))  
  INTO l_oauth_nonce  
  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);  
   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:');  
        utl_http.read_line(http_resp, resp_value, TRUE);  
      END LOOP;  
      WHEN utl_http.end_of_body THEN  
        DBMS_OUTPUT.put_line('No more content.');  
   utl_http.end_response(r => http_resp);  
    when others then  
      DBMS_OUTPUT.put_line('HTTP ERROR: ' || utl_http.get_detailed_sqlerrm);  

Thank you Somecodinghero!


Anonymous said...

Hello Kubilay,
thank for this post, but i get error HTTP ERROR: ORA-29024: Zertifikatvalidierung nicht erfolgreich
certificate validation failed

What is to do to resolve this error ?
I have imported the twitter certificate into my wallet !


Kubilay said...

Hi Frank

What do you mean with Twitter certificate? I believe you use the Oracle wallet to do HTTPS call to twitter and not for a twitter certificate. Did you manage yet? Cheers. Kubilay

Anonymous said...

Hello Kubilay, do you mean that as client we need to authentify ourself by a certificate ?!

Anonymous said...

For my own, I have trouble with bad request HTTP 400 :-(

Unknown said...

Renew it, had same problem.
P.S. Also there is or very soon will appear one fully functional oracle login example, can do everything based on it.

Jack !! said...

Hi Kubilay,
Thanks for your effort to put the code up on this blog. I am an Oracle Apex developer, and I tried connecting to Twitter's OAuth using web service reference but it failed. I then found and tried your code, and I am a step ahead with some results. I am now getting error as "HTTP ERROR: ORA-29268: HTTP client error 401 - Unauthorized" at the end.
Please suggest.

Kubilay said...

Hi Jack

Make sure you have Oracle wallet configured in your Apex workspace.

Run the whole proc in SQL window and check the DBMS_OUTPUT


Anonymous said...

Hi Kubilay,

Thanks for the scripts, i have implemented this and everything seems allright except when i send a tweet i get a 401 unauthorized error.
Wich means that the oath_signature is not correct, you have any idea how i can verify it?

Kubilay said...

+Maurice van der Sluis do you have the Oracle Wallet set up? Run it in SQL Developer, give better error control and use DBMS_OUTPUT.

Ishwar Mittal said...

Hi Kubilay

I have setup everything as you described but at the end we are getting 401 unauthorized error, please help.