SomeCodingHero's Heroic Mumblings: Updating Twitter from the database, using PL/SQL and OAuth.

Sending updates to Twitter from the database was very easy when Twitter was still supporting Basic Authentication. A simple HTTP post and you were done. However, since the OAuthcalypse this is not possible anymore. Nowadays OAuth is used to authenticate with twitter, and gone are the easy days.
I've managed to update a Twitter status from Oracle 10g XE using OAuth, written completely in PL/SQL. This posting describes how it's done. I followed this guide and used bits of the code from this (cryptic) post. Follow the mentioned guide, together with the code samples to understand every step.

I'll use the Out of Band mode of authorizing, which is easier, and doesn't involve the authorizing callback.

In order to use some client to update to Twitter user's timeline, we'll have that user's permission to do so. This means that the client program must be authorized.

The client application

First the client application should be registered. This can be done here. You'll get the consumer key and consumer secret.

The basics

Here's what is it all about: for communicating with Twitter there are basically four things to do:

Create a signature base string;
Use it to create a OAuth signature;
Create an Authorization HTTP Header;
Do a HTTP POST to the specific API.

Part I: getting the request token

The code you see below does just that. Note the usage of DBMS_CRYPTO. Make sure you have execute rights on that package! Also, fill in your details where necessary, and beautify where needed :-)

For URL encoding, I used some package I found on the web. Somehow I couldn't find a existing function in the database for this.

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;

/* Get's the request token for Twitter Authorization
 * You'll need more than this source file alone! Check the blog
 * for details!
 *
 * By: SomeCodingHero's secret identity: Laurens van der Starre.
 * Blog: http://somecodinghero.blogspot.com/
 * Twiitter: somecodinghero
 * Code is GPL. I hope it's useful for you.
 */

SET SERVEROUTPUT ON

DECLARE

  l_oauth_request_token_url CONSTANT VARCHAR2 (500) := 'http://api.twitter.com/oauth/request_token';
  l_oauth_consumer_key CONSTANT VARCHAR2 (500) := 'your_consumer_key';
  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) := 'your_consumer_secret';
  l_oauth_callback CONSTANT VARCHAR2 (5) := 'oob';
  l_http_method VARCHAR2 (5) := 'POST';
  l_oauth_base_string VARCHAR2 (2000);

  l_oauth_key VARCHAR2 (500) := l_oauth_consumer_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_random varchar2(15);


BEGIN

  -- RANDOM oauth_nonce
  SELECT dbms_random.string('A', 15)
  INTO l_random
  FROM DUAL;
  
  SELECT urlencode (UTL_ENCODE.base64_encode(UTL_I18N.string_to_raw (l_random, 'AL32UTF8')))
  INTO l_oauth_nonce
  FROM DUAL;
  
  
  -- Get the timestamp
  SELECT urlencode ((SYSDATE - TO_DATE ('01-01-1970', 'DD-MM-YYYY'))  * (86400)) - 7200
  INTO l_oauth_timestamp
  FROM DUAL;


  -- Create the base string
  l_oauth_base_string := l_http_method 
                          || '&'
                          || urlencode (l_oauth_request_token_url)
                          || '&'
                          || urlencode ( 'oauth_callback'
                              || '='
                              || l_oauth_callback 
                              || '&'
                              || '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_version'
                              || '='
                              || l_oauth_version);
                              
  DBMS_OUTPUT.put_line (l_oauth_base_string);
  
  -- Create the oauth signature
  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'));
                                
  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);
  
  -- The 'update' url for using the API.
  l_update_send := l_oauth_request_token_url
                   || '?'
                   || 'oauth_callback'
                   || '='
                   || l_oauth_callback
                   || '&'
                   || 'oauth_consumer_key'
                   || '='
                   || l_oauth_consumer_key
                   || '&'
                   || 'oauth_nonce'
                   || '='
                   || l_oauth_nonce
                   || '&'
                   || 'oauth_signature'
                   || '='
                   || urlencode (l_base64_sig_mac)
                   || '&'
                   || 'oauth_signature_method'
                   || '='
                   || l_oauth_signature_method
                   || '&'
                   || 'oauth_timestamp'
                   || '='
                   || l_oauth_timestamp
                   || '&'
                   || 'oauth_version'
                   || '='
                   || l_oauth_version;
                   
    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);
   
   
    -- The Authorization header. Note the 'Autorization:' part. Leave it out, and
    -- it won't work ...
    l_oauth_header := 'Authorization: OAuth oauth_nonce="' || l_oauth_nonce || '", '
                      || 'oauth_callback="' || l_oauth_callback ||'", '
                      || 'oauth_signature_method="'|| l_oauth_signature_method || '", '
                      || 'oauth_timestamp="'|| l_oauth_timestamp || '", '
                      || 'oauth_consumer_key="'|| l_oauth_consumer_key || '", '
                      || '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 => ''); 
     
    http_resp := utl_http.get_response(r => http_req);
     
   -- Get the output. This output contains the auth_token and auth_token secret 
   -- required for sending the authorization!
   
   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;

In the response will be the oauth-token and auth-token-secret. Save these for now, because we need them to get the access token.

Note that we use the Out of Band method (see the 'oob' for oauth_callback)!

Part II: getting the access token

Point the (Twitter) user to http://api.twitter.com/oauth/authorize?oauth_token=__your_received_oauth_token___ . The Twitter user has to grant access to the consumer application. When successful, a PIN code will be displayed (because we use the Out of Band method). Note this down, because this is the oauth_verifier.

/* Get's the request token for Twitter User Authorization
 * You'll need more than this source file alone! Check the blog
 * for details!
 *
 * By: SomeCodingHero's secret identity: Laurens van der Starre.
 * Blog: http://somecodinghero.blogspot.com/
 * Twiitter: somecodinghero
 * Code is GPL. I hope it's useful for you.
 */
 
DECLARE

  l_oauth_request_token_url CONSTANT VARCHAR2 (500) := 'http://api.twitter.com/oauth/access_token';
  l_oauth_consumer_key CONSTANT VARCHAR2 (500) := 'consumer_key';
  l_oauth_token  CONSTANT VARCHAR2 (500) := 'oauth_token';
  l_oauth_secret CONSTANT VARCHAR2 (500) := 'oauth_secret';
  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) := 'consumer_secret';
  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_oob_pin VARCHAR(10) := 'PIN NR';
  
  l_random varchar2(15);


BEGIN

  -- RANDOM oauth_nonce
  SELECT dbms_random.string('A', 15)
  INTO l_random
  FROM DUAL;
  
  SELECT urlencode (UTL_ENCODE.base64_encode(UTL_I18N.string_to_raw (l_random, 'AL32UTF8')))
  INTO l_oauth_nonce
  FROM DUAL;
  
  
  SELECT urlencode ((SYSDATE - TO_DATE ('01-01-1970', 'DD-MM-YYYY'))  * (86400)) - 7200
  INTO l_oauth_timestamp
  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_verifier'
                              || '='
                              || l_oob_pin
                              || '&'
                              || 'oauth_version'
                              || '='
                              || l_oauth_version);
                              
  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_oauth_secret);
                                
  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
                   || '?'
                   || '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_verifier'
                   || '='
                   || l_oob_pin
                   || '&'
                   || 'oauth_version'
                   || '='
                   || l_oauth_version;
                   
    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 := 'Authorization: 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_verifier="' || l_oob_pin || '", ' 
                      || '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 => ''); 
     
    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;

(See that the code is pretty much the same). You've now received your access token. Save these. These will be used for Twitter status updates.

Part III: doing a status update

Now we have the actual access token. This token is used for doing things on behalf of the Twitter user.

Sending a tweet for example (deja-vu code :-) ):

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) := 'consumer_key';
  l_oauth_token  CONSTANT VARCHAR2 (500) := 'ACCESS TOKEN';
  l_oauth_secret CONSTANT VARCHAR2 (500) := 'ACCESS TOKEN SECRET';
  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) := 'CONSUMER SECRET';

  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('@somecodinghero You rule!! :-)');

  l_random varchar2(15);

BEGIN

  -- RANDOM oauth_nonce
  SELECT dbms_random.string('A', 15)
  INTO l_random
  FROM DUAL;
  
  SELECT 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;

Note the Authorization header: The 'Authorization:' is gone. When it's present, it doesn't work somehow.

Wrapping up

The code samples above should help you using Twitter form within an Oracle database. I tested it with Oracle 10g XE.

If you get a HTTP 401, please check your oauth_nonce: that one is needs to be unique, and not too small (yes, very vague ...).

11 comments:

RobacleSeptember 9, 2010 at 4:35 PM
Laurens, great job! Thanks. I found the first cryptic part of the source as well, I'm glad you managed to complete it!
goryunovFebruary 15, 2011 at 3:13 AM
Thanks for the post!
One question - what should be put instead of
consumer_key and consumer_secret.
How to generate those strings? Should be application somehow registered
Javier Hernández LemuzApril 6, 2011 at 7:56 AM
Il try this one, may be you will like this one. http://mexico-desarrolla.blogspot.com/2011/04/updating-twitter-from-database-using.html
AnonymousApril 26, 2011 at 4:24 AM
Not sure 10g XE has it, but there is a package called UTL_URL with a function called ESCAPE. Seems to do the same as your urlencode.
MiguelSeptember 16, 2011 at 5:36 PM
This comment has been removed by the author.
Ralph LRFebruary 17, 2012 at 10:21 AM
After spending a few hours trying to get this to work, I now have.

The do_tweet block is missing the line that gets the server timestamp.
All of the examples getting the timestamp are incorrect, there is no need for the -7200 ... if you have that in there you get a 401 Unauthorized...
OthelloAugust 21, 2012 at 9:59 AM
Great code. Any one got it working with gooogle apis? (more specifically google maps and reverse geocoding)
Brendan TierneyJanuary 17, 2013 at 10:18 PM
I've tried the code but I get the 401 Unauthorized error message. Any ideas or suggestions on what to try/change.
I've included the timestamp code in the last code block and removed the -7200
KubilayFebruary 8, 2013 at 12:20 AM
This is great code! Thank you somecodinghero! I have managed to cut down the parts of obtaining the oAuth token. I used only somecodinghero's last block of code, the comment about -7200, provided the 4 security tokens, changed the size oauth_nonce in that block and it worked. Here is somecodingheros single block of code in my blog. http://kubilaykara.blogspot.co.uk/2013/02/send-tweets-from-oracle-with-oauth.html

Many thanks somecodinghero! You rock!
MGCAugust 17, 2015 at 7:06 AM
I think I've figured out the timestamp issue.

According to http://oauth.net/core/1.0a/#nonce, the timestamp is the number of seconds since 1/1/1970 GMT. So I think that the sysdate in your logic needs to also be in GMT. The "- 7200" in your code is essentially hard coding your timezone (GMT+2?). I suspect this is why the code works for some people and not others.

So you could change the timestamp logic with this instead:

-- Get the timestamp
SELECT urlencode ((cast((systimestamp AT TIME ZONE 'GMT') as date) - TO_DATE ('01-01-1970', 'DD-MM-YYYY')) * (86400))
INTO l_oauth_timestamp
FROM DUAL;

Thanks for the great code!

Mike

SomeCodingHero's Heroic Mumblings

About Me

Sunday, September 5, 2010

Updating Twitter from the database, using PL/SQL and OAuth.