APEX

Hello. I have been trying to make an API call to a target endpoint's test https URL on my VM. This is the code utilizing APEX_WEB_SERVICES:

DECLARE
  l_response       CLOB;
  l_request_body   CLOB;
  l_payment_url    VARCHAR2(32767);
  l_transaction_id VARCHAR2(32767);
BEGIN
  l_request_body := '{"Registration": {' ||
                    '"Currency": "AED",' ||
                    '"ReturnPath": "https://apex.oracle.com/pls/apex/.../../.../",' ||
                    '"TransactionHint": "CPT:Y;VCC:Y;",' ||
                    '"OrderID": "7210055701315195",' ||
                    '"Store": "0000",' ||
                    '"Terminal": "0000",' ||
                    '"Channel": "Web",' ||
                    '"Amount": "2.00",' ||
                    '"Customer": "Demo Merchant",' ||
                    '"OrderName": "Paybill",' ||
                    '"UserName": "Demo_fY9c",' ||
                    '"Password": "Comtrust@20182018"' ||
                    '}}';

  APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).NAME := 'Content-Type';
  APEX_WEB_SERVICE.G_REQUEST_HEADERS(1).VALUE := 'application/json';
  APEX_WEB_SERVICE.G_REQUEST_HEADERS(2).NAME := 'Accept';
  APEX_WEB_SERVICE.G_REQUEST_HEADERS(2).VALUE := 'application/json';

  l_response := APEX_WEB_SERVICE.MAKE_REST_REQUEST(
    p_url         => 'https://demo-ipg.ctdev.comtrust.ae:2443',
    p_http_method => 'POST',
    p_body        => l_request_body
  );

apex_json.parse(l_response);

--l_payment_url := apex_json.get_varchar2(p_path => 'Transaction.PaymentPortal');
l_transaction_id := apex_json.get_varchar2(p_path => 'Transaction.TransactionID');

dbms_output.put_line(l_transaction_id);

END;

The above code works perfectly in apex.oracle.com but not in my own server. The error is an ORA-29276: transfer timeout error. I tried other publicly available endpoints for testing, example below.

DECLARE
    l_response CLOB;
    l_json apex_json.t_values;
    l_joke_setup VARCHAR2(4000);
    l_joke_punchline VARCHAR2(4000);
BEGIN
    l_response := apex_web_service.make_rest_request(
        p_url         => 'https://official-joke-api.appspot.com/random_joke',
        p_http_method => 'GET'
    );

    apex_json.parse(l_json, l_response);

    l_joke_setup := apex_json.get_varchar2(p_values => l_json, p_path => 'setup');
    l_joke_punchline := apex_json.get_varchar2(p_values => l_json, p_path => 'punchline');

    dbms_output.put_line('Joke Setup: ' || l_joke_setup);
    dbms_output.put_line('Joke Punchline: ' || l_joke_punchline);
END;

This worked in the apex.oracle.com environment and initially worked in my Instance as well, but triggers a Failure to open file error now after I had taken other actions. I assume it is due to the usage of a wallet but I will get to that in a moment.

I added an ACE entry for my hostname and the (lower and upper) port 2443 with connect privileges. This is confirmed in the DBA_NETWORK_ACLS view and the related DBA_NETWORK_ACL_PRIVILEGES view. The error persisted.

I then created a wallet using the Oracle Wallet Manager. I downloaded the root certificates, intermediate and site and created a wallet with it. I got a Certification Validation Failure error yet again but resolved it by removing the site certificate. I was getting a Failure to Open File error for the wallet as well but resolved it by setting the Wallet Path in sqlnet and running an explicit command. I am assuming this is why it causes a Certification Validation Error now for all hostnames other than the one I provided the server certificates for in my wallet?

The transfer timeout error persisted. Since I had configured the wallet and ACE entry and other steps correctly to my knowledge, I wanted to test if it's a network/firewall issue. I ran a telnet command connecting to the specific port of the hostname. It worked. I also ran a curl command, the output of which is provided below.

C:\Users\abc>curl -v https://demo-ipg.ctdev.comtrust.ae:2443
* Host demo-ipg.ctdev.comtrust.ae:2443 was resolved.
* IPv6: (none)
* IPv4: 195.229.84.28
*   Trying 195.229.84.28:2443...
* Connected to demo-ipg.ctdev.comtrust.ae (195.229.84.28) port 2443
* schannel: disabled automatic use of client certificate
* ALPN: curl offers http/1.1
* ALPN: server accepted http/1.1
* using HTTP/1.x
> GET / HTTP/1.1
> Host: demo-ipg.ctdev.comtrust.ae:2443
> User-Agent: curl/8.9.1
> Accept: */*
>
* schannel: remote party requests renegotiation
* schannel: renegotiating SSL/TLS connection
* schannel: SSL/TLS connection renegotiated
* Request completely sent off
< HTTP/1.1 200 OK
< Cache-Control: private
< Server: Microsoft-IIS/10.0
< Access-Control-Allow-Origin: https://demo-ipg.ctdev.comtrust.ae
< Access-Control-Allow-Headers: Content-Type
< Date: Mon, 10 Feb 2025 08:09:16 GMT
< Connection: close
< Content-Length: 0
<
* shutting down connection #0
* schannel: shutting down SSL/TLS connection with demo-ipg.ctdev.comtrust.ae port 2443
* schannel: server closed abruptly (missing close_notify)

I checked my Windows Firewall. I added ports 2443 in my inbound and outbound rules for safety. I did it for my Instance's Ingress and Egress rules (port 2443 and the target endpoint's IP address) as well although I don't think that factors in here. I am also able to connect to the endpoint port as seen by the curl output.

When I run a UTL_HTTP.REQUEST for my hostname without the port I get a response of some kind at least (HTML code denying access). But when I run the same command after appending :2443 to its end I get the same transfer timeout issue again.

I would greatly appreciate any and all guidance you could provide me on this matter.

PS: This is probably unrelated but in case it is a symptom of a linked issue - after I made these changes and run a SELECT on V$WALLET, I get the “Your database connection has been reset.” message. After that, when I try to make any call at all, even to the hostname configured in the wallet, I get a Certification Validation Error.

EDIT: I had tried increasing the transfer timeout while using both the UTL_HTTP function and the APEX_WEB_SERVICES.MAKE_REST_REQUEST parameters, but I get the same error message instantly while running it.

This is the ACE command I had run:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host       => 'demo-ipg.ctdev.comtrust.ae',
    lower_port => 80,
    upper_port => 2443,
    ace        => xs$ace_type(
                    privilege_list => xs$name_list('connect'),
                    principal_name => 'APEX_240200',  
                    principal_type => xs_acl.ptype_db
                  )
  );
  COMMIT;
END;																								

The wallet shows NOT AVAILABLE in V$ENCRYPTION_WALLET and triggers the disconnection error when I run V$WALLET. But I am assuming it is being picked up if it is calling the hostname without the port appended properly.