Error While making http request from Oracle DB Trigger - java.sql.SQLException: ORA-29273: HTTP request failed ORA-12541: TNS:no listener

Question

I'm using below code to make http call via Oracle DB trigger

create or replace TRIGGER TEST_TABLE_TRIGGER 
AFTER INSERT OR UPDATE OF VALUE ON TEST_TABLE 

for each row
DECLARE

  req utl_http.req;
  res utl_http.resp;
  buffer varchar2(4000); 
  url varchar2(4000) := 'http://localhost:8086/testMethod';

BEGIN
  req := utl_http.begin_request(url, 'GET',' HTTP/1.1');
  utl_http.set_header(req, 'content-type', 'application/json');
  res := utl_http.get_response(req);
  -- process the response from the HTTP call
  begin
    loop
      utl_http.read_line(res, buffer);
      dbms_output.put_line(buffer);
    end loop;
    utl_http.end_response(res);
  exception
    when utl_http.end_of_body 
    then
      utl_http.end_response(res);
  end;
END;

DBA team has granted http permission to the DB user (SQL> grant execute on UTL_HTTP to userName;) . However I'm getting below error in my application log (Springboot)

java.sql.SQLException: ORA-29273: HTTP request failed
ORA-12541: TNS:no listener
ORA-06512: at "SYS.UTL_HTTP", line 368
ORA-06512: at "SYS.UTL_HTTP", line 1118
ORA-06512: at "userName.TEST_TABLE_TRIGGER", line 9
ORA-04088: error during execution of trigger 'userName.TEST_TABLE_TRIGGER '

I tried running the application in linux server and used valid ip address instead of localhost ('http://localhost:8086/testMethod') and got the same error.

When I asked DBA team they said the database listener is up and running on ports 1525 and 1791. How/where to use database listener port to make this code work ?

Answer 1

It didn't work locally when I ran code in STS (Spring Tool Site) with http://localhost:8086/testMethod URL in DB trigger. However after I deployed the code in Linux server and updated localhost with linux server's ip address (http://{serverIP}:8086/testMethod) and it worked ! My mistake ! i was using wrong URL in the beginning. Please Let me know if you know how to make it work locally when i'm running code in STS.

Answer 2

I had the same problem it worked like a charm but after restarting the Database 19c , when i used Apex webservice or utl I get these error

• ACL
• TNS: No listener

but ACL defined after a lot of search and trying i found out the reason was "PROXY" some how database used some proxy you just need to run this before call API

utl_http.set_proxy('');