Finally set up the bespoke Oracle Twitter using OAuth rather than using RSS/XML from
Twitterfeed.
{easy once you've spent a couple of days working out what the hell is all the fuss about OAuth}
The steps are:
1. set up an application on your Twitter account
here
2. make a note of the consumer and account keys and secrets
3. in Oracle create urlencode function:
-=-=-=-=-=-=-
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;
-=-=-=-=-=-=-=
4. update the XXXXX codes below with the relevant values from step 1 and run code within an SQL worksheet window - create a package from this, go on you know how - :
-=-=-=-=-=
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) := 'XXXX';
l_oauth_token CONSTANT VARCHAR2 (500) := 'XXXXX';
l_oauth_secret CONSTANT VARCHAR2 (500) := 'XXXXX';
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) := 'XXXXXX';
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('hello world');
l_random varchar2(15);
BEGIN
select dbil.urlencode((sysdate - to_date('01-01-1970','DD-MM-YYYY')) * (86400)) into
l_oauth_timestamp from dual;
-- RANDOM oauth_nonce
SELECT dbms_random.string('A', 15)
INTO l_random
FROM DUAL;
SELECT dbil.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;
-=-=-=-=-=
And that's it - you should be tweeting away from your Oracle Database - comment if you have any difficulties.
I am in the process now of changing the twitter feeds to contain all information rather than just the top 5.
