ORA-20001: Authentication failed when calling external api in oracle apex

Question

I am currently trying to consume a external api through Oracle apex. I was able to call below api by hard coding 'Baerer' taken but, at the line no 5, it throws an error

ORA-20001: Authentication failed.
ORA-06512: at "APEX_220100.WWV_FLOW_WEB_SERVICES", line 474
ORA-06512: at "APEX_220100.WWV_FLOW_WEBSERVICES_API", line 682
ORA-06512: at "APEX_220100.WWV_FLOW_WEBSERVICES_API", line 705
ORA-06512: at line 5

My pl/sql code is

      declare
          l_bearer_token varchar2(1000);
          l_clob    clob;  
        begin
                    apex_web_service.oauth_authenticate_credential(
                        p_token_url => 'https://www.xxxportaltest.ab.cde.us/api/tokens/auth/token',
                        p_credential_static_id => 'EDU_CRED');
                     
    
                  apex_web_service.g_request_headers(1).name  := 'Authorization';
                  apex_web_service.g_request_headers(1).value := 'Bearer '||apex_web_service.oauth_get_last_token;
         
          -- call the REST API, using the module URL. The request headers are already set in the g_request_headers
          l_clob := apex_web_service.make_rest_request (
                        p_url         => 'https://www.xxxportaltest.ab.cde.us/api/requests/v1/Request/A7ACDAED-FAF1-9BAE-AA56-01B925E5156F/', 
                        p_http_method => 'GET',
                         p_wallet_path  => 'file:/u01/app/oracle/admin/orcl/wallet/',
                        p_wallet_pwd   => 'WalletPWD!');
         
          -- display the result of the REST API call              
          dbms_output.put_line('Here is the API call result -> '||l_clob);
        end;

 

I have already added certificate of 'www.xxxportaltest.ab.cde.us' in the wallet and referred in Oracle Apex admin instance setting. Further, I have already added ACL, granted privileges and assigned them.

SELECT host, acl FROM dba_host_acls;

| HOST | ACL   |
|:---- |:------:|
| xxxportaltest.ab.cde.us  | /sys/acls/hr_utl_http.xml    |

SELECT privilege, grant_type, principal, host FROM dba_host_aces;

|    PRIVILEGE    |    GRANT_TYPE    |    PRINCIPAL    |    HOST    |
|    :----     |    :------:    |     :-----:    |    -----:    |
|    RESOLVE    |    GRANT    |    PUBLIC    |    xxxportaltest.ab.cde.us    |
|    CONNECT    |    GRANT    |    PUBLIC    |    xxxportaltest.ab.cde.us    |
|    HTTP    |    GRANT    |    PUBLIC    |    xxxportaltest.ab.cde.us    |

I tried this api through POSTMAN and it works but with 'Auth URL' (https://www.xxxportaltest.ab.cde.us/api/tokens/oauth2/authorise) and with 'Authorize using browser' checkbox. But I have no idea on how to config the same in oracle apex.Can someone help me to solve this issue?

Answer 1

The OAUTH_AUTHENTICATE function only supports the OAuth Client Credentials flow. As you're working with an Authorization URL, and there is a browser redirect to a consent page, this is either the Implicit Grant or the Authorization Code flow. Both are not supported by the OAUTH_AUTHENTICATE - remember that APEX runs completely in the Oracle Database.

Try to use an authentication flow which does not require a browser redirect, which is OAuth Client Credentials, Basic Authentication or simple API keys. If such is not available, you need to implement the token challenge manually and yourself.