Oracle Invoke REST Service from pl/sql

Question

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;

Are you sure that stacktrace is being generated by your stored procedure? Your procedure TEST_REST_CALL3 doesn't appear anywhere in it. Also, is it possible to make an HTTP request to your REST API from the database server using a different language (e.g. Python, Perl, Java, etc.) or a tool such as curl? — Luke Woodward
– Luke Woodward, Commented Feb 2, 2017 at 22:33
We just used curl n it looks like the port is not open, even though they said it was, so i need to get with network administrators again tomorrow, i will update you then, thanks — PurpleRugz
– PurpleRugz, Commented Feb 2, 2017 at 22:49
*Action: Use get_detailed_sqlerrm to check the detailed error message. — Jeffrey Kemp
– Jeffrey Kemp, Commented Feb 6, 2017 at 7:24

Morteza Fakoorrad · Accepted Answer · 2019-03-07 12:42:53Z

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

Collectives™ on Stack Overflow

Oracle Invoke REST Service from pl/sql

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related