utl_http GET request to REST API fails with ORA-29273: HTTP request failed-CodePudding

I am trying to make a GET request to the International Space station REST API as a test from PLSQL, but it is giving me a ORA-29273: HTTP request failed error.

My code is this:

declare
l_url            varchar2(32767) := 'https://api.wheretheiss.at/v1/satellites/25544';
l_http_request   utl_http.req;
l_http_response  utl_http.resp;
l_text           varchar2(32767);
begin
  -- Make a HTTP request and get the response.
  l_http_request := utl_http.begin_request(l_url);
  -- Use basic authentication if required.
  --IF p_username IS NOT NULL and p_password IS NOT NULL THEN
  --  UTL_HTTP.set_authentication(l_http_request, p_username, p_password);
  --END IF;
  l_http_response := utl_http.get_response(l_http_request);
  -- Loop through the response.
  begin
    loop
      utl_http.read_text(l_http_response, l_text, 32766);
      dbms_output.put_line(l_text);
    end loop;
  exception
    when utl_http.end_of_body then
      utl_http.end_response(l_http_response);
  end;
exception
  when others then
    utl_http.end_response(l_http_response);
    raise;
end;

And the error is this:

ORA-29273: HTTP request failed
ORA-06512: at line 27
ORA-29259: end-of-input reached
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1148
ORA-06512: at line 8

Anybody know what the problem is or how to correctly make a GET request to a REST API endpoint url?

CodePudding user response：

Hi did you check to check to see if you have allowed this API host through ACL. That may be your problem. https://ittutorial.org/oracle-acl-the-access-control-list-acl-and-how-to-create-and-grant-an-acl-in-oracle-database/

CodePudding user response：

The ORA-29259: end-of-input reached error usually indicates that UTL_HTTP failed to negotiate the security for an HTTPS connection. You can find several similar questions on here - sometimes it means that your Oracle version doesn't support the cipher suite that the HTTPS server requires, but in your case I think it might mean that you haven't set up a wallet yet.

There are several steps involved to do HTTPS/SSL connections from Oracle database.

You need to create an Oracle wallet with the site certificates for the HTTPS site you're trying to reach.

You also need to set up ACLs, which the link above mentions.