I am at a loss. We are trying to set up a procedure to pull in some JSON data from an external API.
It's calling a HTTPS, so we have the wallet all set up. We can make the call to the site, but we need to call a specific port. When I add the port to the URL, I get the following error:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-29259: end-of-input reached
ORA-06512: at line 9
This is my code (edited for security):
set serverout on
declare
l_host_name varchar2(100) := 'api.somesite.com';
l_port varchar2(100) := '9400';
l_req utl_http.req;
l_result utl_http.resp;
l_data varchar2(32767);
begin
utl_http.set_wallet('file:/u01/app/oracle/admin/webtest2/wallet', '********');
l_req := UTL_HTTP.begin_request (url => 'https://'
|| l_host_name
|| ':'
|| l_port
|| '/lpapi/v1.0/views?api_key=********&client_key=**************',
method => 'GET',
http_version => 'HTTP/1.1'
);
l_result := utl_http.get_response(l_req);
begin
loop
utl_http.read_text(l_result, l_data, 1000);
dbms_output.put_line (l_data);
end loop;
exception
when utl_http.end_of_body then
utl_http.end_response(l_result);
end;
end;
If I remove the PORT, the procedure executes, but the returning result is the error page from the site. So I have to include the :9400 in my URL call. But when I do, I get the ORA-29273 error.
I'm at a loss as to what to check next.
Any suggestions are appreciated.