Creating empty doc through Oracle PL/SQL

[Dimitri Gielis]

Hello,

I'm having difficulties to replicate the example of the Google Documents as listed here in Oracle PL/SQL: http://code.google.com/apis/accounts/docs/OAuth.html#SigningOAuth

I'm using 2-legged authentication. The error I get is: Unknown Authorization Header (401)

I couldn't find any examples of PL/SQL, so maybe I'm doing something wrong?

E.g. calculating the basestring or the signature?

I've been trying to mimic other examples I could find on the internet and for some my signature corresponds, for others it doesn't. The playground is using 3-legged authentication which is a pity as that shows so much more information. I'm running out of ideas...

Does anybody has an idea what is wrong with my code in PL/SQL?

--

create or replace package body ae_google_oauth as

/***************************************************************************/

-- Helper routine to Encode the URL

function urlencode(p_str in varchar2) return varchar2

as

    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 urlencode;

-------------------------------------

procedure create_doc 

as

  c_wallet_location      constant varchar2(200)  := '...';

  c_wallet_password      constant varchar2(200)  := '...';

  http_req  UTL_HTTP.req;

  http_resp utl_http.resp;

  l_random       varchar2(15);

  l_oauth_nonce             varchar2 (500);

  l_oauth_timestamp         varchar2 (100);

  l_oauth_signature varchar2(1000);

  l_url varchar2(1000);

  resp_name      VARCHAR2(256);

  resp_value     varchar2(1024);

  i number;

  l_data varchar2(32000);

  l_oauth_base_string varchar2(4000);

    l_sig_mac raw (2000);

  l_base64_sig_mac varchar2 (100);

begin

  utl_http.set_wallet(c_wallet_location, c_wallet_password);

  

  -- 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 - (SYSDATE-1)) * (86400)) - 7200

  INTO l_oauth_timestamp

  from dual;

                              

  l_data := '<atom:entry xmlns:atom="http://www.w3.org/2005/Atom">

  <atom:category scheme="http://schemas.google.com/g/2005#kind"

                 term="http://schemas.google.com/docs/2007#document" />

  <atom:title>Company Perks</atom:title>

</atom:entry>';

  l_url := 'https://docs.google.com/feeds/default/private/full';

  -- Create the base string

  l_oauth_base_string := 'POST' 

                          || '&' || urlencode(l_url) || '&'

                          || urlencode ( 

                              'oauth_consumer_key'

                              || '='

                              || 'example.com'

                              || '&'

                              || 'oauth_nonce'

                              || '='

                              || l_oauth_nonce

                              || '&'

                              || 'oauth_signature_method'

                              || '='

                              || 'HMAC-SHA1'

                              || '&'

                              || 'oauth_timestamp'

                              || '='

                              || l_oauth_timestamp

                              || '&'

                              || 'oauth_version'

                              || '='

                              || '1.0'

                              || '&'

                              || 'xoauth_requestor_id=em...@example.com');

                              

  htp.p(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 ('...', 'AL32UTF8'));

                                

  l_oauth_signature := urlencode(UTL_RAW.cast_to_varchar2 (UTL_ENCODE.base64_encode (l_sig_mac)));

  

  htp.p ('MAC Signature (Base64-encoded): ' ||  l_oauth_signature);

 

  http_req := utl_http.begin_request (l_url  , 'POST' , utl_http.http_version_1_1);

  --utl_http.set_header ( r => http_req, name => 'Host', value =>'docs.google.com');

  utl_http.set_header ( r => http_req, name => 'Content-Type', value => 'application/atom+xml'); 

  utl_http.set_header ( r => http_req, name => 'GData-Version', value => '3.0'); 

  utl_http.set_header ( r => http_req, name => 'Authorization', value =>'OAuth ' || 'oauth_version="1.0"'||','||'oauth_nonce="'||l_oauth_nonce||'",'||'oauth_timestamp="'||l_oauth_timestamp||'",'||'oauth_consumer_key="example.com"'||','||'oauth_signature_method="HMAC-SHA1"'||','||'oauth_signature="'||l_oauth_signature||'"');

  utl_http.set_header ( r => http_req, name => 'Content-Length',value => length(l_data));

  utl_http.write_text(http_req, l_data);

  

  http_resp := utl_http.get_response(r => http_req);

  htp.p('----> Response coming now');

  FOR i IN 1..utl_http.get_header_count(http_resp)

  LOOP

    utl_http.get_header(http_resp, i, resp_name, resp_value);

    htp.p(resp_name || ': ' || resp_value);

  END LOOP;

  htp.p('Getting content:');

  BEGIN

    LOOP

      utl_http.read_line(http_resp, resp_value, TRUE);

      htp.p(resp_value);

    END LOOP;

  EXCEPTION

  WHEN utl_http.end_of_body THEN

    htp.p('No more content.');

  END;

  utl_http.end_response(r => http_resp);

EXCEPTION

WHEN OTHERS THEN

  htp.p('HTTP ERROR: ' || utl_http.get_detailed_sqlerrm);

end create_doc;

end ae_google_oauth;

--

Thanks so much,

Dimitri

[Vic Fryzel]

Hey Dimitri,

Can you print your final Authorization header that you're sending along?  It will be easier to tell from that if there's a problem.

-Vic

[Vic Fryzel]

(please remove any token bits from the header.)

-Vic