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 Certificate Validation 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.