How to authorize a Oauth from PL/SQL

How to authorize a Oauth from PL/SQL

/* Formatted on 2010/08/05 19:19 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE almagesto.update_1daylater
–(t_user IN VARCHAR2, t_pass IN VARCHAR2, t_update IN VARCHAR2)
AS
oauth_request_token_url CONSTANT VARCHAR2 (500) :=
‘http://request_token_url’;
oauth_consumer_key CONSTANT VARCHAR2 (500) := urlencode
(‘yourconsumerkey’);
oauth_nonce VARCHAR2 (500);
oauth_signature_method CONSTANT VARCHAR2 (10) := urlencode (‘HMAC-SHA1’);
oauth_timestamp VARCHAR2 (100);
oauth_version CONSTANT VARCHAR2 (5) := urlencode (‘1.0’);
oauth_consumer_secret CONSTANT VARCHAR2 (500) := ‘yourconsumersecret’;
oauth_callback CONSTANT VARCHAR2 (5) := ‘oob’;
http_method VARCHAR2 (5) := ‘GET’;
oauth_base_string VARCHAR2 (2000);
— la puta key tiene que ser encoded
oauth_key VARCHAR2 (500) := urlencode
(oauth_consumer_secret) || ‘&’;
l_sig_mac RAW (2000);
l_base64_sig_mac VARCHAR2 (100);
http_req UTL_HTTP.req;
http_resp UTL_HTTP.resp;
h_name VARCHAR2 (255);
h_value VARCHAR2 (1023);
t_update_send VARCHAR2 (4000);
res_value VARCHAR2 (32767);
show_header NUMBER := 0;
–0 False, 1 True
show_xml NUMBER := 1;
–0 False, 1 True
BEGIN
SELECT urlencode (oauth_nonce_seq.NEXTVAL)
INTO oauth_nonce
FROM DUAL;

SELECT urlencode ((SYSDATE – TO_DATE (’01-01-1970′, ‘DD-MM-YYYY’))
* (86400)) – 7200
INTO oauth_timestamp
FROM DUAL;

oauth_base_string :=
http_method
|| ‘&’
|| urlencode (oauth_request_token_url)
|| ‘&’
|| urlencode ( ‘oauth_callback’
|| ‘=’
|| oauth_callback
|| ‘&’
|| ‘oauth_consumer_key’
|| ‘=’
|| oauth_consumer_key
|| ‘&’
|| ‘oauth_nonce’
|| ‘=’
|| oauth_nonce
|| ‘&’
|| ‘oauth_signature_method’
|| ‘=’
|| oauth_signature_method
|| ‘&’
|| ‘oauth_timestamp’
|| ‘=’
|| oauth_timestamp
|| ‘&’
|| ‘oauth_version’
|| ‘=’
|| oauth_version);
–DBMS_OUTPUT.put_line (oauth_base_string);
l_sig_mac :=
DBMS_CRYPTO.mac (UTL_I18N.string_to_raw (oauth_base_string, ‘AL32UTF8’)
,DBMS_CRYPTO.hmac_sh1
,UTL_I18N.string_to_raw (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);
t_update_send :=
oauth_request_token_url
|| ‘?’
|| ‘oauth_callback’
|| ‘=’
|| oauth_callback
|| ‘&’
|| ‘oauth_consumer_key’
|| ‘=’
|| oauth_consumer_key
|| ‘&’
|| ‘oauth_nonce’
|| ‘=’
|| oauth_nonce
|| ‘&’
|| ‘oauth_signature’
|| ‘=’
|| urlencode (l_base64_sig_mac)
|| ‘&’
|| ‘oauth_signature_method’
|| ‘=’
|| oauth_signature_method
|| ‘&’
|| ‘oauth_timestamp’
|| ‘=’
|| oauth_timestamp
|| ‘&’
|| ‘oauth_version’
|| ‘=’
|| oauth_version;
http_req := UTL_HTTP.begin_request (t_update_send, http_method,
UTL_HTTP.http_version_1_1);
–DBMS_OUTPUT.put_line (t_update_send);
UTL_HTTP.set_response_error_check (TRUE);
UTL_HTTP.set_detailed_excp_support (TRUE);
— utl_http.set_header (r => http_req, NAME =>
‘oauth_consumer_key’, VALUE => oauth_consumer_key);
— utl_http.set_header (r => http_req, NAME => ‘oauth_nonce’,
VALUE => oauth_nonce);
— utl_http.set_header (r => http_req, NAME =>
‘oauth_signature_method’, VALUE => oauth_signature_method);
— utl_http.set_header (r => http_req, NAME => ‘oauth_timestamp’,
VALUE => oauth_timestamp);
— utl_http.set_header (r => http_req, NAME => ‘oauth_version’,
VALUE => oauth_version);

    • Prateek
    • May 6, 2011
    Reply

    Hi,
    It was great to find only this tutorials which explains how to use oracle PL/SQL for 2 legged google Oauth.
    I ran into a problem which has been continuously giving me a 401 – Unauthorized Error when even I execute the above.

    After a bit of googling on OAuth (2 legged) I found that the ‘oauth_base_string’ required an extra parameter xoauth_requestor_id=prateek@example.com which I added but still the Error 401 – Unauthorized persists.

    Please help
    Thanks

Leave a Reply

One thought on “How to authorize a Oauth from PL/SQL

  1. Hi,
    It was great to find only this tutorials which explains how to use oracle PL/SQL for 2 legged google Oauth.
    I ran into a problem which has been continuously giving me a 401 – Unauthorized Error when even I execute the above.

    After a bit of googling on OAuth (2 legged) I found that the ‘oauth_base_string’ required an extra parameter xoauth_requestor_id=prateek@example.com which I added but still the Error 401 – Unauthorized persists.

    Please help
    Thanks

Leave a Reply