Microsoft .Net WCF web service WSDLs and OSB 10g

When integrating applications you'll probably encounter Microsoft's Windows Communication Foundation (WCF) web services. The WSDLs for these web services have the tendency to contain a lot of schema and wsdl imports. Of course this is completely normal. However, the OSB can have some problems with consuming these WSDLs. The fact of the matter is that these imports contain generated XSDs and WSDLs, for example:

The problem is in this extension. "?xsd=xsd<number>" (and in case of a WSDL "?wsdl=wsdl<number>"): when consuming a WCF WSDL in a Business Service using Workshop in 10g the service consumption fails because the imported "input.xsd?xsd=xsd0" is not recognized as an OSB resource. When the web interface is used, one is left to manually create all the references to the imported artifacts. In WCF WSDLs this can be a lot meaning a lot of work.

A workaround is not to consume this WSDL. Use the import utility. So right-click on the OSB project, choose "Oracle Service Bus - Import resources from URL" and import the WSDL. (Note that when the WSDL is on the file system, the file:// URL syntax can be used). It will now import all resources correctly.

Alternatively, upgrade to 11g. The OEPE does not seem to have this problem.

Stating the obvious: ODI JDBC connect strings

I'm probably stating the obvious here, but it caused me a so called 'D'OH! How could I forget'-moment yesterday:

I was creating Data Servers in Oracle Data Integrator and was wondering how fail over should be done. Don't be fooled by Oracle Data Integrator's Oracle JDBC thin driver connect URL example:

Of course you can specify more advanced connect strings like for example jdbc:oracle:thin:@

(DESCRIPTION=
   (ADDRESS_LIST=
     (FAILOVER=on)
     (ADDRESS=(PROTOCOL=TCP)(HOST=server1)(PORT=1521))
     (ADDRESS=(PROTOCOL=TCP)(HOST=server2)(PORT=1521))
   )
   (CONNECT_DATA =
     (SERVER=DEDICATED)
     (SERVICE_NAME=a_service)
     (FAILOVER_MODE=
       (TYPE=select)
       (METHOD=basic)
       (RETRIES=20)
       (DELAY=15)
     )
   )
 )

Classic case of RTFM (which -of course- stands for Read The Friendly Manual).

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:

1. Create a signature base string;
2. Use it to create a OAuth signature;
3. Create an Authorization HTTP Header;
4. 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 ...).