Hey folks
Hoping someone out there might have successfully done this and have an idea of what I've got wrong.
I'm trying to call a Twitter OAuth 1.0 REST API to write a tweet.
I can successfully do this via Postman and am now trying to convert this to PL/SQL.
I've followed the information here at :
developer.twitter.com/en/docs/authentication/overview
and here :
developer.twitter.com/en/docs/authentication/oauth-1-0a/creating-a-signature
about the authentication, and I'm sure I've got it right as to what these docs say.
I'm guessing I'm missing a step on the Oracle side maybe but what I don't know.
If anyone has done something similar could they take a look and see what I might be missing :
This is the code (I've changed the tokens on here so they're not real), and the DBMS_OUTPUT it produces at the bottom. Any suggestions would be much appreciated
CODE
DECLARE
http_req utl_http.req;
http_resp utl_http.resp;
l_http_method VARCHAR2(5) := 'POST';
l_tweet_content VARCHAR2(140) := 'SAHTestTweet';
l\_oauth\_request\_token\_url CONSTANT VARCHAR2(500) := '[https://api.twitter.com/1.1/statuses/update.json';](https://api.twitter.com/1.1/statuses/update.json';)
l_oauth_consumer_key CONSTANT VARCHAR2(500) := '1112QVhZR8Mpv60B0Zzdaqzxh';
l_oauth_consumer_secret CONSTANT VARCHAR2(500) := '222kIeE1j5VKUam1hCMXJuanMAXWuhvq8wDQugmQh9fzjnzVLQ';
l_oauth_token CONSTANT VARCHAR2(500) := '3331548309900005376-bn4mDbpI00d0wGW4ikEvSR7xmuIA3d';
l_oauth_token_secret CONSTANT VARCHAR2(500) := '4449EgdWOLbcjN7KUACeXXdWyVyrvB8Zx1bCCgt7Z1Shi';
l_oauth_signature_method CONSTANT VARCHAR2(10) := 'HMAC-SHA1';
l_oauth_nonce VARCHAR2(500);
l_oauth_timestamp NUMBER;
l_oauth_version CONSTANT VARCHAR2(5) := '1.0';
l_sig_base_string VARCHAR2(2000);
l_sig_parameter_string VARCHAR2(2000);
l_signing_key VARCHAR2(500) := utl_url.escape(l_oauth_consumer_secret,TRUE)||'&'||utl_url.escape(l_oauth_token_secret,TRUE);
-- l_signing_key VARCHAR2(500) := l_oauth_consumer_secret||'&'||l_oauth_token_secret;
-- l_signing_key VARCHAR2(500) := utl_url.escape(l_oauth_consumer_secret,TRUE)||'&';
l_oauth_header VARCHAR2(2000);
l_sig_mac RAW(2000);
l_oauth_signature VARCHAR2(100);
l_update_send VARCHAR2(2000);
l_line VARCHAR2(1024);
resp_name VARCHAR2(256);
resp_value VARCHAR2(1024);
l_random VARCHAR2(25);
BEGIN
-- Oracle Wallet
utl_http.set_wallet('file:/home/app/oracle/product/11.2.0/dbhome_1/owm/wallets/oracle', 'Oracle123!Oracle123!');
-- Get the timestamp
SELECT (sysdate - TO_DATE('01-01-1970', 'DD-MM-YYYY')) *(86400)
INTO l_oauth_timestamp
FROM dual;
-- RANDOM oauth_nonce
SELECT dbms_random.string('A', 25)
INTO l_random
FROM dual;
SELECT utl_encode.base64_encode(utl_i18n.string_to_raw(l_random, 'AL32UTF8'))
INTO l_oauth_nonce
FROM dual;
-- Build up parameter string for signature calcuclation
l_sig_parameter_string :=
utl_url.escape('include_entities',TRUE)
||'='
||utl_url.escape('true',TRUE)
|| '&'
||utl_url.escape('oauth_consumer_key',TRUE)
||'='
||utl_url.escape(l_oauth_consumer_key ,TRUE)
||'&'
||utl_url.escape('oauth_nonce',TRUE)
||'='
||utl_url.escape(l_oauth_nonce,TRUE)
||'&'
||utl_url.escape('oauth_signature_method',TRUE)
||'='
||utl_url.escape(l_oauth_signature_method,TRUE)
||'&'
||utl_url.escape('oauth_timestamp',TRUE)
||'='
||utl_url.escape(TRUNC(l_oauth_timestamp),TRUE)
||'&'
||utl_url.escape('oauth_token',TRUE)
||'='
||utl_url.escape(l_oauth_token,TRUE)
||'&'
||utl_url.escape('oauth_version',TRUE)
||'='
||utl_url.escape(l_oauth_version,TRUE)
||'&'
||utl_url.escape('status',TRUE)
||'='
||utl_url.escape(l_tweet_content,TRUE)
;
dbms_output.put_line('SIG PARAMETER STRING : '||l_sig_parameter_string );
l_sig_base_string := l_http_method
|| '&'
|| utl_url.escape (l_oauth_request_token_url,TRUE)
|| '&'
|| utl_url.escape (l_sig_parameter_string ,TRUE);
dbms_output.put_line('SIGNATURE BASE STRING : '||l_sig_base_string);
-- create signature
l_sig_mac := dbms_crypto.mac(utl_i18n.string_to_raw(l_sig_base_string, 'AL32UTF8'),
dbms_crypto.hmac_sh1,
utl_i18n.string_to_raw(l_signing_key, 'AL32UTF8')
);
-- bas64 encode signature
l_oauth_signature := utl_raw.cast_to_varchar2(utl_encode.base64_encode(l_sig_mac));
dbms_output.put_line('OAUTH Signature (Base64-encoded): ' || l_oauth_signature);
-- add tweet to end of URL
l_update_send := l_oauth_request_token_url
|| '?status='
|| l_tweet_content;
dbms_output.put_line('BEGINNING REQUEST ' || l_update_send);
http_req := utl_http.begin_request(l_update_send,
l_http_method,
utl_http.http_version_1_1);
dbms_output.put_line('REQUEST BEGUN URL ' || l_update_send);
utl_http.set_response_error_check(true);
utl_http.set_detailed_excp_support(true);
utl_http.set_body_charset(http_req, 'UTF-8');
utl_http.set_transfer_timeout(to_char('60'));
-- Create Authorization Header
l_oauth_header := 'OAuth '
|| utl_url.escape('oauth_consumer_key',TRUE)
|| '="'
|| utl_url.escape(l_oauth_consumer_key,TRUE)
|| '", '
|| utl_url.escape('oauth_nonce',TRUE)
|| '="'
|| utl_url.escape(l_oauth_nonce,TRUE)
|| '", '
|| utl_url.escape('oauth_signature',TRUE)
|| '="'
|| utl_url.escape(l_oauth_signature,TRUE)
|| '", '
|| utl_url.escape('oauth_signature_method',TRUE)
|| '="'
|| utl_url.escape(l_oauth_signature_method,TRUE)
|| '", '
|| utl_url.escape('oauth_timestamp',TRUE)
|| '="'
|| utl_url.escape(TRUNC(l_oauth_timestamp),TRUE)
|| '", '
|| utl_url.escape('oauth_token',TRUE)
|| '="'
|| utl_url.escape(l_oauth_token,TRUE)
|| '", '
|| utl_url.escape('oauth_version',TRUE)
|| '="'
|| utl_url.escape(l_oauth_version,TRUE)
|| '"'
;
dbms_output.put_line('AUTHORIZATION for Header: ' || l_oauth_header);
-- Set Header Fields
utl_http.set_header(r => http_req, name => 'Accept', value => '*/*');
utl_http.set_header(r => http_req, name => 'Connection', value => 'keep-alive');
utl_http.set_header(r => http_req, name => 'User-Agent', value => 'PostmanRuntime/7.26.1');
utl_http.set_header(r => http_req, name => 'Content-Type', value => 'application/x-www-form-urlencoded');
utl_http.set_header(r => http_req, name => 'Authorization', value => l_oauth_header);
utl_http.set_header(r => http_req, name => 'Content-Length', value => '0');
utl_http.set_header(r => http_req, name => 'Host', value => 'api.twitter.com');
dbms_output.put_line('HEADERS SET. NOW WRITING CONTENT');
utl_http.write_text(r => http_req, data => l_tweet_content);
dbms_output.put_line('WILL NOW GET RESPONSE');
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: ' || sqlerrm);
dbms_output.put_line(utl_http.get_detailed_sqlerrm);
END;
DBMS OUTPUT
SIG PARAMETER STRING : include_entities=true&oauth_consumer_key=1112QVhZR8Mpv60B0Zzdaqzxh&oauth_nonce=5245315162475A4C54324A425957744F62315A495A32465756316C6A61474A6154413D3D&oauth_signature_method=HMAC-SHA1&oauth_timestamp=1604322884&oauth_token=3331548309900005376-bn4mDbpI00d0wGW4ikEvSR7xmuIA3d&oauth_version=1.0&status=SAHTestTweet
SIGNATURE BASE STRING : POST&https%3A%2F%2Fapi.twitter.com%2F1.1%2Fstatuses%2Fupdate.json&include_entities%3Dtrue%26oauth_consumer_key%3D1112QVhZR8Mpv60B0Zzdaqzxh%26oauth_nonce%3D5245315162475A4C54324A425957744F62315A495A32465756316C6A61474A6154413D3D%26oauth_signature_method%3DHMAC-SHA1%26oauth_timestamp%3D1604322884%26oauth_token%3D3331548309900005376-bn4mDbpI00d0wGW4ikEvSR7xmuIA3d%26oauth_version%3D1.0%26status%3DSAHTestTweet
OAUTH Signature (Base64-encoded): vU0M0LpAAkpNQUWXQUYGRMdKzFs=
BEGINNING REQUEST https://api.twitter.com/1.1/statuses/update.json?status=SAHTestTweet
REQUEST BEGUN URL https://api.twitter.com/1.1/statuses/update.json?status=SAHTestTweet
AUTHORIZATION for Header: OAuth oauth_consumer_key="1112QVhZR8Mpv60B0Zzdaqzxh", oauth_nonce="5245315162475A4C54324A425957744F62315A495A32465756316C6A61474A6154413D3D", oauth_signature="vU0M0LpAAkpNQUWXQUYGRMdKzFs%3D", oauth_signature_method="HMAC-SHA1", oauth_timestamp="1604322884", oauth_token="1281548309900005376-bn4mDbpI00d0wGW4ikEvSR7xmuIA3d", oauth_version="1.0"
HEADERS SET. NOW WRITING CONTENT
WILL NOW GET RESPONSE
HTTP ERROR: ORA-29268: HTTP client error 401 - Authorization Required
ORA-29268: HTTP client error 401 - Authorization Required