Requesting oauth token via Oracle

[allwyn1]

Hi

I am trying to request Oauth token from Oracle SQL,   Making an REST API call to authenticate and login into Dev instance and get the success response and should be able to retrieve the access token.

when I do that from Oracle I am getting an error message:

SQL> /

{"error":"server_error","error_description":"access_denied"}   when it trace it down it displays error code as 401 from Service now and I wanted to find out is there any parameter I need to set to authenticate external connection.

Thanks

Allwyn

[allwyn1]

This is another test which shows the request was made to Servicenow Server and access error as unauthorized.

Also I am not running weblogic,   just Oracle 12c   with this I was able to communicate with Salesforce.com as well and get the json response back.   So could you see in the Developer instance why it is unauthorized request?

Thanks

Allwyn

SQL> ed

Wrote file afiedt.buf

  1   DECLARE

  2       req     UTL_HTTP.REQ;

  3       resp   UTL_HTTP.RESP;

  4       name   VARCHAR2(256);

  5       value VARCHAR2(1024);

  6   BEGIN

  7       req := UTL_HTTP.BEGIN_REQUEST('https://dev13384.service-now.com/oauth_tok

en.do?grant_type=password&client_id=ee6f9fd3b282020026234d61ed998aaf&client_secr

et=xxxxx<&username=admin&password=xxxxx);

  8       UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');

  9       resp := UTL_HTTP.GET_RESPONSE(req);

10       DBMS_OUTPUT.PUT_LINE('HTTP response status code: ' || resp.status_code);

11       DBMS_OUTPUT.PUT_LINE('HTTP response reason phrase: ' || resp.reason_phras

e);

12       FOR i IN 1..UTL_HTTP.GET_HEADER_COUNT(resp) LOOP

13           UTL_HTTP.GET_HEADER(resp, i, name, value);

14           DBMS_OUTPUT.PUT_LINE(name || ': ' || value);

15       END LOOP;

16       UTL_HTTP.END_RESPONSE(resp);

17* END;

18   /

HTTP response status code: 401

HTTP response reason phrase: Unauthorized

Content-Type: application/json;charset=utf-8

Content-Length: 60

Date: Fri, 04 Sep 2015 23:44:03 GMT

Server: ServiceNow

Connection: close

PL/SQL procedure successfully completed.

[coryseering]

Hi Allwyn,

This is the error:

WARNING *** WARNING *** OAuthTokenProcessor caught a OAuthRequestProblemException with message OAuthProblemException{error='invalid_request', description='Method not set to POST.', uri='null', state='null', scope='null', redirectUri='null', responseStatus=0, parameters={}}

You need to move all of the request params into the POST data. Try something like:

apex_web_service.make_rest_request ( p_url => 'https://dev13384.service-now.com/oauth_token.do'

, p_http_method => 'POST'

,p_body => 'grant_type=password&client_id=ee6f9fd3b282020026234d61ed998aaf&client_secret=xxxxxx&username=admin&password=xxxxxx'

, p_wallet_path     => 'file:c:\Oracle1\Middleware\Oracle_Home\wallet'

, p_wallet_pwd       =>   'xxxxx'   );

If that doesn't fix it, make sure the request is definitely using the POST verb when it gets sent. Oracle's documentation indicates you can set this on a per-message basis through some sort of configuration screen:

Set HTTP Verb

Thanks

Cory

[allwyn1]

Initially I had this plsql block and I was striping it down:   now I executed with POST itself exactly as you described:

SQL>   DECLARE

  2       v_token           VARCHAR2(1000);

  3       lv_ws_retval VARCHAR2(1000);

  4       jtoken json;

  5         l_json json;

  6

  7   BEGIN

  8       lv_ws_retval := apex_web_service.make_rest_request ( p_url => 'https://de

v13384.service-now.com/oauth_token.do?grant_type=password&client_id=ee6f9fd3b282

020026234d61ed998aaf&client_secret=xxxxx&username=admin&password=xxxxxx

' , p_http_method => 'POST' , p_body => ' '

  9     , p_wallet_path     => 'file:c:\Oracle1\Middleware\Oracle_Home\wallet'

10               , p_wallet_pwd       =>   'welcome1'   );

11       dbms_output.put_line(dbms_lob.substr(lv_ws_retval, 32000, 1));

12       jtoken             := json(lv_ws_retval);

13       v_token           := json_ext.get_string(jtoken,'access_token');

14           dbms_output.put_line(v_token);

15

16   END;

17   /

{"error":"server_error","error_description":"access_denied"}

PL/SQL procedure successfully completed.

The response is still the same access denied from servicenow

[coryseering]

Hi Allwyn,

You're making progress!

WARNING *** WARNING *** OAuthTokenProcessor caught a OAuthRequestProblemException with message OAuthProblemException{error='invalid_request', description='Bad request content type. Expecting: application/x-www-form-urlencoded', uri='null', state='null', scope='null', redirectUri='null', responseStatus=0, parameters={}}

Set another HTTP header for the Content-Type, and the value "application/x-www-form-urlencoded".

[allwyn1]

Thank you So much !!!! ,   Since I was not getting the correct error message you saved my day.

Not sure why it get spit out the actual error message itself?   then it would have been lot of easier.  

Thanks again for your help.

Allwyn

I was able to successfully parse the Json access token as well:

13	dbms_output.put_line(lv_ws_retval);
14	jtoken	:= json(lv_ws_retval);
15	v_token	:= json_ext.get_string(jtoken,'access_token');
16	dbms_output.put_line(v_token);

17

18   END;

19   /

{"scope":"useraccount","token_type":"Bearer","expires_in":1799,"refresh_token":"

akqhziJyFWref4CKBoMa8daULO8sugJ5N4BsomXfd9cfR-wux9-8ynkrNsWbl2w1UoO5z-PZ-gviDrKH

HFZT9w","access_token":"mmNpIRnIebYaGtHWfp_kKfNwgGzj4TeIozRg9jcL5b3lgS502tIhqxjC

IhJsNhCMPWeAw0usS788QBO86Y5Y7Q"}

mmNpIRnIebYaGtHWfp_kKfNwgGzj4TeIozRg9jcL5b3lgS502tIhqxjCIhJsNhCMPWeAw0usS788QBO8

6Y5Y7Q

PL/SQL procedure successfully completed.