Hi,
I am new to hitting Restful APIs from Oracle.
I have this huge Xml (> 4000 characters) which I need to post to a remote restful api endpoint. Please let me know how to accomplish this.
Below is my sample code that I am playing with right now.


create or replace
procedure publish_message
(
v_messageRequest in XmlType --Input Param for Large Xml
) is
req utl_http.req;
res utl_http.resp;
url varchar2(4000) := 'my endpoint Url';
name varchar2(4000);
buffer varchar2(4000);
content XmlType := v_messageRequest;

begin
req := utl_http.begin_request(url, 'POST',' HTTP/1.1');
utl_http.set_header(req, 'user-agent', 'mozilla/4.0');
utl_http.set_header(req, 'content-type', 'application/xml');
utl_http.set_header(req, 'Authorization', 'Basic d124553rpwYXNzd29yZA=='); --It Uses Basic Authorization
utl_http.set_header(req, 'Content-Length', length(content.getStringVal())); --Getting Length of Xml being passed

SYS.utl_http.write_raw(req, contentgetStringVal());
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 publish_message;

The issue is, when I am trying to execute this Proc:
begin
publish_message(myhugeXml);
end;
/

I get this below error:
Error report:
ORA-29273: HTTP request failed
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_HTTP", line 368
ORA-06512: at "SYS.UTL_HTTP", line 1118
ORA-06512: at "FANWMLM16.PUBLISH_MESSAGE", line 13
ORA-06512: at line 58
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.

Regarding the error above: ORA-24247: network access denied by access control list (ACL)
I have given all the access to my current user using the snippets below:

begin
dbms_network_acl_admin.create_acl (
acl => 'http_permissions.xml', -- or any other name
description => 'HTTP Access',
principal => 'CurrentUser', -- the user name trying to access the network resource
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
end;
/
commit;

begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'http_permissions.xml',
principal => 'CurrentUser',
is_grant => true,
privilege => 'connect');
end;
/
commit;

Still that doesn't resolve the error either.
Please guide me if I am doing anything wrong here.

SQL> conn / as sysdba
Connected.

SQL> grant connect to demo identified by demo;

Grant succeeded.

SQL> grant execute on utl_http to demo;

Grant succeeded.

SQL> declare
  2    l_req   utl_http.req;
  3    l_resp  utl_http.resp;
  4  begin
  5    l_req  := utl_http.begin_request('http://www.oracle.com');
  6    l_resp := utl_http.get_response(l_req);
  7    utl_http.end_response(l_resp);
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_HTTP", line 368
ORA-06512: at "SYS.UTL_HTTP", line 1118
ORA-06512: at line 5


SQL> conn / as sysdba
Connected.

SQL> begin
  2    dbms_network_acl_admin.append_host_ace (
  3      host       => 'www.oracle.com',
  4      lower_port => 80,
  5      upper_port => 80,
  6      ace        => xs$ace_type(privilege_list => xs$name_list('http'),
  7                                principal_name => 'demo',
  8                                principal_type => xs_acl.ptype_db));
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> conn demo/demo
Connected.

SQL> declare
  2    l_req   utl_http.req;
  3    l_resp  utl_http.resp;
  4  begin
  5    l_req  := utl_http.begin_request('http://www.oracle.com');
  6    l_resp := utl_http.get_response(l_req);
  7    utl_http.end_response(l_resp);
  8  end;
  9  /

PL/SQL procedure successfully completed.