Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Transfer timeout error while attempting to call a non-standard port of target API endpoint.

ak10Feb 10 2025 — edited Feb 10 2025

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.

Comments

chonewell Nov 12 2024

My Oracle Cloud tenant, cloud account, and secure email have no issues. Why haven't I received my password reset email for Oracle Cloud? This is very strange, and our attempts have not been able to solve the problem. May I ask who I should turn to for help?

L. Fernigrini Nov 12 2024

If your account is a paid one, open a Support ticket.

If it is a Free Tier then you will have to rely on help from the community. Most probable cause that you did not receive the password reset email is that your account has been stolen and the email has been changed.

chonewell Nov 13 2024

Thank you for your reply!
But when I chatted with the online customer service, they told me that my Oracle Cloud tenant, account, and email were all fine. So, there shouldn't be a problem of theft.
I have a free account, but who can I contact on the forum? I can only post, but no one on the forum can view my account permissions, right. I am currently trying to reset MFA, I don't know if it works.
It's quite ridiculous that I have a free account and can't enjoy any services, but how can I become a paid user if I can't log in to my account.

1 - 3

Post Details

Added on Feb 10 2025
6 comments
220 views