Calling an Endpoint with Untrusted Certificate from Autonomous Database PL/SQL Program via API Gateway

Oracle Autonomous Database is a cloud Oracle service offered in two infrastructure modes (shared and dedicated) and two types of workloads (OLTP or DSS).

Since the user does not have access to the underlying infrastructure, several features are restricted, for instance, you can’t manage wallets to add remote endpoint untrusted certificates: the database comes with a list of certificates from the well-known certification authorities, but you can’t add additional ones in a wallet, instead.

That’s said, we are proposing here a workaround to solve the limitation.

What happens if we call the untrusted endpoint?

In short, you receive the “Certificate validation failure” message error:

Error starting at line : 1 in command - declare ax varchar2(32000); begin ax := apex_web_service.make_rest_request( p_url => 'https://130.61.195.1/greet', p_http_method => 'GET'); dbms_output.put_line(ax); end; Error report - ORA-29273: HTTP request failed ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 1036 ORA-29024: Certificate validation failure ORA-06512: at "SYS.UTL_HTTP", line 639 ORA-06512: at "SYS.UTL_HTTP", line 1415 ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 934 ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 1566 ORA-06512: at "APEX_190100.WWV_FLOW_WEBSERVICES_API", line 408 ORA-06512: at line 4 29273. 00000 - "HTTP request failed" *Cause: The UTL_HTTP package failed to execute the HTTP request. *Action: Use get_detailed_sqlerrm to check the detailed error message. Fix the error and retry the HTTP request.

OCI API Gateway

API Gateway is a cloud native API management solution for publishing API’s. When you publish an API, Oracle creates a public endpoint with a valid certificate included.

In addition, when creating an API public endpoint, you can set the “DISABLE SSL VERIFICATION” for the backend API so, thanks to it, we can resolve the limitation.

The thing is very simple. First, create an API, then a deployment with the endpoint that the API gateway will route the call to, as follows:

In a few seconds you’ll have the API published and ready to be tested via the API endpoint:

So far, sogood. Let’s make now the call from a PL block in the database and see what happens:

declare
   ax varchar2(32000);
begin
   ax := apex_web_service.make_rest_request(
   p_url => 'https://nc26ok...xu6ay.apigateway.eu-frankfurt-1.oci.customer-oci.com/pana/greet',
p_http_method => 'GET');
   dbms_output.put_line(ax);
end;

Great, now the PL/SQL block works fine!

That’s all folks, hope it helps! 🙂