Authenticating Oracle Intelligent Advisor REST API from SQL

The previous articles we have published on the subject of authenticating Oracle Intelligent Advisor from Postman and SOAP UI have been very popular – and the video has been viewed a few times as well. Today’s post looks at another fairly common occurrence – especially if you are dealing in legacy applications, eBusiness Suite and others – or even if you are using a storage in SQL as part of wider processing of Oracle Intelligent Advisor flows. How can you authenticate using the REST API from SQL?

The basic principle is that you want to be able to make a call to the relevant /auth URL with the username and password that you have set up already as an API Client in your Hub, and get back the Token that you can then use to make further calls to, for example. the Batch REST API from SQL (but not only).

Setting up an Access Control List to ensure that you can access the machine name and port number is essential to making this work. See the link below for an explanation.

And learning to use commands like this one, to get more detailed information about what happened when you called (or attempted to call) the REST API from SQL using the utl_http utility library:

select utl_http.get_detailed_sqlerrm from dual;

In fact the above command might well return an error like this one.

ORA-24247: network access denied by access control list (ACL) 

Since you will be most likely using the UTL_HTTP library to make the call, this will also require you to download and save the root certificate of your Hub in an Oracle Wallet, and you must ensure that you use this wallet when you make the call to the REST API from SQL.

To obtain the certificate you will need to navigate to the front page of the relevant Hub and click the lock symbol followed by clicking “The connection is secure” and then opening the “Certificate is valid” link, select the Root Certificate and save in in Base64 CER format before importing it into an Oracle Wallet that you will reference in your SQL (this assumes you are using Chrome but other browsers are similar).

Once you have gotten into the Certificate dialogs, you can set about selecting, viewing and downloading the Root Certificate.

In the Wallet therefore you would expect to see something like this:

You can expect lots of error messages if you download the wrong certificate (you need the root certificate) or you download it in the wrong format (you need Base64) or you forget the password of your Wallet.

At it’s most basic, now that all the pieces are in place, your Procedure could look something like this

DECLARE  
    http_req         utl_http.req;
    http_resp        utl_http.resp;
    l_authresponse   utl_http.resp;
    l_authraw        VARCHAR2(32767);
    l_authreqbody    VARCHAR2(2000);
    v_url            VARCHAR2(2000) := 'YOURHUB/api/latest/auth';
--    replace with your information
        content          VARCHAR2(4000) := 'grant_type=client_credentials' ||'&'|| 'client_id=YOURAPICLIENT' ||'&'|| 'client_secret=PASSWORD';
BEGIN 
    http_req := utl_http.begin_request(v_url, 'POST');
-- replace with your Wallet containing the Base64 certificate exported from your Hub and the wallet password. Ensure that the user running the procedure 
-- has been added to the ACL for the Hub
      UTL_HTTP.set_wallet('file:C:\PATHTOWALLET', 'PASSWORDFORWALLET');
    utl_http.set_header(http_req, 'Content-Type', 'application/x-www-form-urlencoded');
    utl_http.set_header(http_req, 'Content-Length', length(content));
    utl_http.write_text(http_req, content);
    dbms_output.put_line(content);
    l_authresponse := utl_http.get_response(http_req);
    utl_http.read_text(l_authresponse, l_authraw);
    dbms_output.put_line(l_authraw);
    utl_http.end_response(l_authresponse);
    utl_http.end_request(http_req);
END;

Your console (since all we did in this case was dbms_ouptut.put_line() ) might then give you the token you need to make further calls:

The process is quite straightforward but requires quite a few hoops to be jumped through before you get there. So let’s summarize:

  1. Ensure Access Control List lets you get to the machine and port you need
  2. Make sure you have permission to use UTL_HTTP
  3. Download and save the root certificate in an Oracle Wallet
  4. Reference the wallet in your procedure
  5. Make the POST call, passing in three items : the grant type, the client id and the client secret
  6. Get the token.

Have a nice day!

Richard Napier

Author: Richard Napier

After 8 years in case management and ERP software roles, Richard Napier joined Siebel Systems in 1999 and took up the role of managing the nascent Siebel University in Southern Europe. He subsequently was Director of Business Development and Education for InFact Group (now part of Business & Decisions) for 8 years. He now runs Intelligent Advisor IT Consulting OÜ. Owner of intelligent-advisor.com, he also is Co-Founder of the Siebel Hub.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Serving Customers Worldwide
Hide picture