APEX

Recently, we enabled our APEX applications to perform RESTful calls to HTTPS endpoints and also https call to generate Jasper Reports. To achieve this, we needed our DBA to create and configure a wallet (and he did it) and the https calls works on his side fully.

However, I am not a SYSDBA for the database, as it is managed by other team.

Our APEX applications are hosted on a server (paas) separated from the database server. We now have two use cases where we need to make HTTPS calls:

1. To generate Jasper reports.
2. To access a web service that provides contract-related information.

So this is the problem:

====================================================================

I am experiencing intermittent issues when making REST calls through the apex_web_service.make_rest_request function in Oracle APEX and when I try to reach an https restful endpoint inside Restful data sources, as well as in SQL Developer. The execution alternates between success and failure (error: "failure to open file"), when trying to access the wallet, but only when attempting to use HTTPS.

Observed Behavior**:**

1. In SQL Developer:
   • The make_rest_request function fails intermittently. Sometimes it works, but other times it returns the "failure to open file" error.

Example of a query executed in SQL Developer:

select apex_web_service.make_rest_request(

p\_url => 'https://ttttwsp.secure.ep.tral.uniq.pi/fallcontracts/welcome.do',

p\_http\_method => 'GET',

p\_wallet\_path => 'file:/ora/app/wallet',

p\_wallet\_pwd => null

) as r from dual;

Successful tests:

After some minutes: The total same query fails:

1. In APEX:

• When I run the same function within the SQL Workshop in APEX, the problem is even more frequent.

Same thing at oracle apex side:

Successful sometimes:

Then, if I run again, it fails:

Tests made:

• Tests with HTTP (without encryption):

    Query executed in SQL Developer without HTTPS and without the wallet:
  

select apex_web_service.make_rest_request(

p\_url => 'http://www.ttttwsp.ep.tral.uniq.pi/fallcontracts/welcome.do',

p\_http\_method => 'GET',

p\_wallet\_path => null,

p\_wallet\_pwd => null

) as r from dual;

This query works without issues, without encryption.

• Log Verification:
  • When testing the calls, the NGINX logs showed that the request was received correctly.
  • However, when checking the logs of both APEX and NGINX, no significant differences or errors were found to explain the intermittent failure.

Tried with UTL_HTTP.REQUEST instead of apex_web_service.make_rest_request, but same intermittent issue

Could someone please help me? It would be really appreciated