CODEWITHSUNDEEP
oracle-databaserestplsql
PurpleRugz
PurpleRugz

Reputation: 117

Oracle Invoke REST Service from pl/sql

I need help with this code, I am getting an error saying following.

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 line 5
29273. 00000 -  "HTTP request failed"
*Cause:    The UTL_HTTP package failed to execute the HTTP request.
*Action:   Use get_detailed_sqlerrm to check the detailed error message.
           Fix the error and retry the HTTP request.

I contacted network team and they see bidirectional traffic on that port being done, so I am not sure what else is/could be wrong? any ideas?

create or replace
procedure Test_Rest_Call3

 is
  req utl_http.req;
  res utl_http.resp;
  url varchar2(4000) := 'http://ipaddresshere:9099/api/batchProcess/1';

  name varchar2(4000);
  buffer varchar2(4000); 
  content varchar2(4000) := '';

begin
  req := utl_http.begin_request(url, 'DELETE',' HTTP/1.1');

  utl_http.set_header(req, 'user-agent', 'mozilla/4.0'); 
  utl_http.set_header(req, 'content-type', 'application/json'); 
  utl_http.set_header(req, 'Content-Length', length(content));

  utl_http.write_text(req, content);

  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 Test_Rest_Call3;

Upvotes: 2

Views: 12857

Answers (1)

Morteza Fakoorrad
Morteza Fakoorrad

Reputation: 31

Have you checked if DBA granted to execute utl_http? Just try to run it to be sure:

select utl_http.request('http://ipaddresshere:9099/api/batchProcess/1') from dual;

If you get error then ask DBA to give the permission accordingly:

grant execute on utl_http to your_oracle_user_name
grant execute on dbms_lock to user_name



BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'local_sx_acl_file.xml', 
    description  => 'A test of the ACL functionality',
    principal    => 'put your user_name',
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);
end;

begin
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'local_sx_acl_file.xml',
    host        => 'localhost', 
    lower_port  => 9002,
    upper_port  => NULL);    
end;

I hope it will help.

Cheers,

Morteza Fakoorrad

Upvotes: 3

Related Questions