Home » SQL & PL/SQL » SQL & PL/SQL » ORA-29268, 400 - Bad Request trying to send Tweet using REST Api from PL/SQL using UTL_HTTP.
ORA-29268, 400 - Bad Request trying to send Tweet using REST Api from PL/SQL using UTL_HTTP. [message #682590] Fri, 30 October 2020 08:42 Go to next message
scotthillierfrontera
Messages: 5
Registered: October 2020
Junior Member
Hey guys

I'm trying to write a PL/SQL function that lets me send a tweet using REST API from Oracle PL/SQL

I've successfully tested this using Postman, and I'm trying to recreate now in Oracle PL/SQL but whatever I do I get a 400 Bad Request when I try and use the utl_http.set_header command.

In the details below, I've changed the values of the various keys, so it's not that, I'm just hiding them here.

I know its not ACL problems as I've got that set up as initially I had ACL errors which after creating ACL I now dont have.

In the Postman console, this is what I have :

POST xttps://api.twitter.com/1.1/statuses/update.json?status=Test%20Tweet
(orafaq thinks thats a link thats why Ive changed it to xttps)

Request Headers
Authorization: OAuth oauth_consumer_key="XXX",oauth_token="YYY",oauth_signature_method="HMAC-SHA1",oauth_timestamp="1604040422",oauth_nonce="uAmCX0NFeym",oauth_version="1.0",oauth_signature="KhtY25DnOvYHgqK6SG83UvMU5y4%3D"
User-Agent: PostmanRuntime/7.26.1
Accept: */*
Cache-Control: no-cache
Postman-Token: 8901fa98-f367-4219-a33e-23d38d316650
Host: api.twitter.com
Accept-Encoding: gzip, deflate, br
Connection: keep-alive
Cookie: personalization_id="v1_QQUZtSSL3H/TMxogtXMEcw=="; lang=en; guest_id=v1%3A160397589280743625
Content-Length: 0
I'm trying to write a PL/SQL function that lets me send a tweet using REST API from Oracle PL/SQL

I've successfully tested this using Postman, and I'm trying to recreate now in Oracle PL/SQL but whatever I do I get a 400 Bad Request when I try and use the utl_http.set_header command.

In the details below, I've changed the values of the various keys, so it's not that, I'm just hiding them here.

I know its not ACL problems as I've got that set up as initially I had ACL errors which after creating ACL I now dont have.

In the Postman console, this is what I have :

POST xttps://api.twitter.com/1.1/statuses/update.json?status=Test%20Tweet


Request Headers
Authorization: OAuth oauth_consumer_key="XXX",oauth_token="YYY",oauth_signature_method="HMAC-SHA1",oauth_timestamp="1604040422",oauth_nonce="uAmCX0NFeym ",oauth_version="1.0",oauth_signature="KhtY25DnOvYHgqK6SG83UvMU5y4%3D"
User-Agent: PostmanRuntime/7.26.1
Accept: */*
Cache-Control: no-cache
Postman-Token: 8901fa98-f367-4219-a33e-23d38d316650
Host: api.twitter.com
Accept-Encoding: gzip, deflate, br
Connection: keep-alive
Cookie: personalization_id="v1_QQUZtSSL3H/TMxogtXMEcw=="; lang=en; guest_id=v1%3A160397589280743625
Content-Length: 0

and that works fine.

Below is the code that I have trying to recreate the same thing from PL/SQL, that errors every time.
I've never done this before so apologies if I'm missing something obvious, but I could really do with some help! Thanks.

DECLARE
    http_req                   utl_http.req;
    http_resp                  utl_http.resp;
    l_http_method              VARCHAR2(5) := 'POST';
    l_content                  VARCHAR2(140) := utl_url.escape('SAHTestTweet');
    l_oauth_request_token_url  CONSTANT VARCHAR2(500) := 'xttps://api.twitter.com/1.1/statuses/update.json';
    l_oauth_consumer_key       CONSTANT VARCHAR2(500) := 'XXX';
    l_oauth_consumer_secret    CONSTANT VARCHAR2(500) := 'YYY';
    l_oauth_token              CONSTANT VARCHAR2(500) := 'AAA';
    l_oauth_secret             CONSTANT VARCHAR2(500) := 'BBB';
    l_oauth_nonce              VARCHAR2(500);
    l_oauth_signature_method   CONSTANT VARCHAR2(10) := utl_url.escape('HMAC-SHA1');
    l_oauth_timestamp          VARCHAR2(100);
    l_oauth_version            CONSTANT VARCHAR2(5) := utl_url.escape('1.0');
    l_oauth_base_string        VARCHAR2(2000);
    l_oauth_key                VARCHAR2(500) := l_oauth_consumer_secret
                                 || '&'
                                 || l_oauth_secret;
    l_oauth_header             VARCHAR2(2000);
    l_sig_mac                  RAW(2000);
    l_base64_sig_mac           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', 'PASSWORDHERE');

 -- Get the timestamp  
      SELECT
        utl_url.escape((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_url.escape(utl_encode.base64_encode(utl_i18n.string_to_raw(l_random, 'AL32UTF8')))
    INTO l_oauth_nonce
    FROM
        dual;

-- Build up base string

    l_oauth_base_string := l_http_method
                           || ' '
                           || utl_url.escape(l_oauth_request_token_url)
                           || '?'
                           || 'status'
                           || '='
                           || l_content
                           || '&'
                           || utl_url.escape('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);

    dbms_output.put_line(l_oauth_base_string);
    
    -- create 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'));

    dbms_output.put_line('Combined sig: ' || l_oauth_key);
    
    -- encode signature
    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);
    
    
    -- add tweet to end of URL
    l_update_send := l_oauth_request_token_url
                     || '?status='
                     || l_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');
    
    -- Create Header
    l_oauth_header := 'OAuth '
                      || 'oauth_consumer_key="'
                      || l_oauth_consumer_key
                      || '",'
                      || 'oauth_token="'
                      || l_oauth_token
                      || '",'
                      || 'oauth_signature_method="'
                      || l_oauth_signature_method
                      || '",'
                      || 'oauth_timestamp="'
                      || l_oauth_timestamp
                      || '",'
                      || 'oauth_nonce="'
                      || l_oauth_nonce
                      || '",'
                      || 'oauth_version="'
                      || l_oauth_version
                      || '",'
                      || 'oauth_signature="'
                      || utl_url.escape(l_base64_sig_mac)
                      || '"';

    dbms_output.put_line('HEADER: ' || l_oauth_header);
    
    -- Set Header Fields
    utl_http.set_header(r => http_req, name => 'Authorization', value => l_oauth_header);
(The line above is where it falls over)

utl_http.set_header(r => http_req, name => 'Accept-Encoding', value => 'gzip, deflate, br');

    utl_http.set_header(r => http_req, name => 'Connection', value => 'keep-alive');

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

    utl_http.set_transfer_timeout(to_char('60'));
    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: ' || sqlerrm);
        dbms_output.put_line(utl_http.get_detailed_sqlerrm);
END;
The DBMS output I'm getting looks right generally :

POST xttps://api.twitter.com/1.1/statuses/update.json?status=SAHTestTweet&oauth_consumer_key=XXX&oauth_nonce=53334A3365474E5A636D52765A304E3353323532536E4252533270515330523654513D3D&oauth_signature_method=HMAC-SHA1&oauth_timestamp=1604062895&oauth_token=YYY&oauth_version=1.0

Combined sig: g9nkIeE1j5VKUam1hCMXJuanMAXWuhvq8wDQugmQh9fzjnzVLQ&o3m9EgdWOLbcjN7KUACeXXdWyVyrvB8Zx1bCCgt7Z1Shi

MAC Signature (Base64-encoded): j3fvwUjuy6KaHxY487tHMMcwP3U=

BEGINNING REQUEST xttps://api.twitter.com/1.1/statuses/update.json?status=SAHTestTweet
REQUEST BEGUN URL xttps://api.twitter.com/1.1/statuses/update.json?status=SAHTestTweet

HEADER: OAuth oauth_consumer_key="XXX",oauth_token="YYY",oauth_signature_method="HMAC-SHA1",oauth_timestamp="1604062895",oauth_nonce="53334A3365474E5A636D52765A304E3353323532536E4252533270515330523654513D3D",oauth_version="1.0",oauth_signature="j3fvwUjuy6KaHxY487tHMMcwP3U="

HTTP ERROR: ORA-29268: HTTP client error 400 - Bad Request
I'm guessing it's only something small as everything seems to match the Postman console.

Any ideas anyone ?
Re: ORA-29268, 400 - Bad Request trying to send Tweet using REST Api from PL/SQL using UTL_HTTP. [message #682593 is a reply to message #682590] Fri, 30 October 2020 14:40 Go to previous messageGo to next message
BlackSwan
Messages: 26763
Registered: January 2009
Location: SoCal
Senior Member
https://lmgtfy.app/?q=send+tweet+from+pl%2Fsql
Re: ORA-29268, 400 - Bad Request trying to send Tweet using REST Api from PL/SQL using UTL_HTTP. [message #682604 is a reply to message #682593] Mon, 02 November 2020 02:06 Go to previous messageGo to next message
scotthillierfrontera
Messages: 5
Registered: October 2020
Junior Member
Did that make you feel better then?

Do you think I hadn't already exhausted that route before typing out such a full post hoping for a little help ?

Well done, go about your day feeling like a smart ass.

*slow clap*
Re: ORA-29268, 400 - Bad Request trying to send Tweet using REST Api from PL/SQL using UTL_HTTP. [message #682610 is a reply to message #682590] Mon, 02 November 2020 11:40 Go to previous messageGo to next message
John Watson
Messages: 8547
Registered: January 2010
Location: Global Village
Senior Member
Running your code (just a couple of changes to make it work on my PC) I get the same error at the same point. So I can say that the problem is not to do with your environment. Not much help, I know, but all I can do.
Re: ORA-29268, 400 - Bad Request trying to send Tweet using REST Api from PL/SQL using UTL_HTTP. [message #682619 is a reply to message #682610] Tue, 03 November 2020 05:09 Go to previous messageGo to next message
scotthillierfrontera
Messages: 5
Registered: October 2020
Junior Member
Thanks for trying John - that never would have worked because I'd altered the tokens in this example code as they have to remain hidden.

I finally got to the answer, which I'll put here in case anyone else finds themselves in the same position.

Turns out the Twitter documentation on how to build the signature is significantly incorrect (at least when calling from Oracle).

The document says your signature header should have "include entities" in it, this causes the code not to work.
Remove this from the signature string, start with consumer key.

Next, the Twitter documentation says the signing key, which is consumer secret & oauth token secret should be percent escaped either side of the &, this is wrong, these 2 parts should NOT be escaped.

Finally, the list of header fields MUST begin with Authorization, the documentation shows Accept, Connection, User-Agent, Content-Type coming before Authorization and this does not work.

This leaves my WORKING code as :

DECLARE
http_req utl_http.req;
http_resp utl_http.resp;
l_http_method VARCHAR2(5) := 'POST';
l_tweet_content VARCHAR2(140) := 'Test2' ;

l_oauth_request_token_url CONSTANT VARCHAR2(500) := '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) := l_oauth_consumer_secret||'&'||l_oauth_token_secret;
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(4000);
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('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)
;

l_sig_base_string := l_http_method
|| '&'
|| utl_url.escape (l_oauth_request_token_url,TRUE)
|| '&'
|| utl_url.escape (l_sig_parameter_string ,TRUE);

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

-- add tweet to end of URL
l_update_send := l_oauth_request_token_url
|| '?status='
|| utl_url.escape(l_tweet_content,TRUE);

http_req := utl_http.begin_request(l_update_send,
l_http_method,
utl_http.http_version_1_1);


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

-- Set Header Fields
utl_http.set_header(r => http_req, name => 'Authorization', value => l_oauth_header);
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 => 'Content-Length', value => '0');
utl_http.set_header(r => http_req, name => 'Content-Type', value => 'application/x-www-form-urlencoded');
utl_http.set_header(r => http_req, name => 'Host', value => 'api.twitter.com');
utl_http.set_header(r => http_req, name => 'Cache-Control', value => 'no-cache');

utl_http.write_text(r => http_req, data => utl_url.escape(l_tweet_content,TRUE));
http_resp := utl_http.get_response(r => http_req);

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;
Re: ORA-29268, 400 - Bad Request trying to send Tweet using REST Api from PL/SQL using UTL_HTTP. [message #682620 is a reply to message #682619] Tue, 03 November 2020 05:14 Go to previous messageGo to next message
John Watson
Messages: 8547
Registered: January 2010
Location: Global Village
Senior Member
And it works for me, too Smile
Well, as far as it can:
cache-control: no-cache, no-store, max-age=0
content-length: 62
content-type: application/json; charset=utf-8
date: Tue, 03 Nov 2020 11:12:38 GMT
server: tsa_f
set-cookie: personalization_id="v1_1Z0inHJflCVlKII/8YwxSQ=="; Max-Age=63072000; Expires=Thu, 03 Nov 2022 11:12:38 GMT; Path=/; Domain=.twitter.com; Secure; SameSite=None
set-cookie: guest_id=v1%3A160440195815225618; Max-Age=63072000; Expires=Thu, 03 Nov 2022 11:12:38 GMT; Path=/; Domain=.twitter.com; Secure; SameSite=None
strict-transport-security: max-age=631138519
x-connection-hash: 1136378f9bfb21a845cf144ff32a0cc9
x-response-time: 103
Getting content:
{"errors":[{"code":89,"message":"Invalid or expired token."}]}
No more content.

PL/SQL procedure successfully completed.
Re: ORA-29268, 400 - Bad Request trying to send Tweet using REST Api from PL/SQL using UTL_HTTP. [message #682622 is a reply to message #682620] Tue, 03 November 2020 05:32 Go to previous message
scotthillierfrontera
Messages: 5
Registered: October 2020
Junior Member
If you want to try it out, sign up for a free developer account at Twitter

https://developer.twitter.com/en/apply-for-access

to get your own set of API keys which you can plug into the code in place of mine, and then start sending tweets from Oracle.
(if you feel so inclined)
Previous Topic: Joining multiple tables with case
Next Topic: SQL query
Goto Forum:
  


Current Time: Sun May 16 13:22:10 CDT 2021