Oracle Cloud > utl_http fails with a ORA-29273: HTTP request failed ORA-29024: Certificate validation failure ORA-06512

Question

I am working with a temporary Oracle Cloud account. What I thought was identical functionality works on the Apex Web Service, but not with UTL_HTTP. So this snippet works and returns the issue we are looking for.

DECLARE   
   L_json_response     varchar2(32767);
BEGIN   
   apex_web_service.g_request_headers(1).name := 'Content-Type';   
   apex_web_service.g_request_headers(1).Value := 'application/json';         
   L_json_response := apex_web_service.make_rest_request ( p_url => 
   'https://mycompany.atlassian.net/rest/api/3/issue/BLABLA-23862', p_http_method => 'GET', 
    p_username => 'My.Username@mycompany', p_password => 'osBJWHhPasdffNVOQ5AA11D5'); -- Password is my Jira API Token      
   EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,'An error was encountered - 
  '||SQLCODE||' -ERROR- '||SQLERRM);
END;

I can't use the Apex web service in the end product, and we need to use UTL_HTTP. This snippet should from my understanding do the same:

DECLARE
    req utl_http.req;
    res utl_http.resp;
    url varchar2(4000) := 'https://mycompany.atlassian.net/rest/api/3/issue/BLABLA-23862';
    buffer varchar2(4000);
BEGIN
    req := utl_http.begin_request(url, 'GET');
    utl_http.set_header(req, 'Content-Type', 'application/json');
    utl_http.set_header(req, 'Authorization', 'Basic ' || utl_encode.base64_encode('my.msername@mycompany:osBJWHhPasdffNVOQ5AA11D5'));

    res := utl_http.get_response(req);
    utl_http.read_text(res, buffer);
END;

But returns:

ORA-29273: HTTP request failed
ORA-29024: Certificate validation failure
ORA-06512: at "SYS.UTL_HTTP", line 639
ORA-06512: at "SYS.UTL_HTTP", line 1415 ORA-06512...

Dai Dai · Accepted Answer · 2021-03-06T06:43:09

The key is the UTL_HTTP.SET_WALLET('');. You need to set the wallet(with empty string parameter) before initial http request. The following code snippet tested with in Oracle Cloud ATP (Autonomous Transition Processing) database:

Setup network ACL

BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl  => 'my_acl.xml',
                                    description => 'ACL for http request.',
                                    principal => 'MY_USER',
                                    is_grant  => true,
                                    privilege => 'connect');
  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'my_acl.xml',
                                       principal => 'MY_USER',
                                       is_grant  => true,
                                       privilege => 'resolve');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'my_acl.xml', host => 'www.oracle.com');
END;

Test https request

DECLARE
  l_text VARCHAR2(32767);
BEGIN
  UTL_HTTP.SET_WALLET('');
  l_text := UTL_HTTP.REQUEST('https://www.oracle.com/index.html');
  dbms_output.put_line(l_text);
END;

See official document and example (on the bottom of page):

Oracle Cloud > utl_http fails with a ORA-29273: HTTP request failed ORA-29024: Certificate validation failure ORA-06512

2 Answers