Oracle APEX_WEB_SERVICE MAKE_REST REQUEST raise ORA-29273 and ORA-24247

Question

I'm working on Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production and I need to develop a store procedure that access an API, I have to retrive the endpoint

https://api.my.host:8443/rest/ec/617643

I have set the oracle Wallet and added the certificate like this:

orapki wallet create -wallet /home/oracle/walletapi -pwd walletapi2022 -auto_login
orapki wallet add -wallet /home/oracle/walletapi -trusted_cert -cert /tmp/api.my.host.cer -pwd walletapi2022

I have set the ACE

DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'api.my.host'
    ,lower_port => 8443
    ,upper_port => 8443
    ,ace => XS$ACE_TYPE(
                        privilege_list => XS$NAME_LIST('http')
                        ,principal_name => 'MYUSER'
                        ,principal_type => XS_ACL.ptype_db
                    )
);
DBMS_NETWORK_ACL_ADMIN.APPEND_WALLET_ACE (
    wallet_path    => 'file:/home/oracle/walletapi'
    ,ace            => XS$ACE_TYPE(
                        privilege_list => XS$NAME_LIST('use_client_certificates', 'use_passwords')
                        ,principal_name => 'MYUSER'
                        ,principal_type => XS_ACL.ptype_db
                    ));

Documentarion

In my store proc try this

... 
l_clob := APEX_WEB_SERVICE.make_rest_request(
               p_url         => 'https://api.my.host:8443/rest/ec/617643'
               ,p_http_method => 'GET'
               ,p_wallet_path => 'file:/home/oracle/walletapi'
               ,p_wallet_pwd  => 'walletapi2022'
);
...

Documentation

and this error is raised

ORA-29273: HTTP request failed
ORA-06512: at "APEX_210200.WWV_FLOW_WEB_SERVICES", line 1182
ORA-06512: at "APEX_210200.WWV_FLOW_WEB_SERVICES", line 782
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1127
ORA-06512: at "APEX_210200.WWV_FLOW_WEB_SERVICES", line 756
ORA-06512: at "APEX_210200.WWV_FLOW_WEB_SERVICES", line 1023
ORA-06512: at "APEX_210200.WWV_FLOW_WEB_SERVICES", line 1371
ORA-06512: at "APEX_210200.WWV_FLOW_WEBSERVICES_API", line 626
ORA-06512: at line 6

Answer 1

I answer my question for the benfits of others.

Applying the solution with CREATE_ACL and ASSIGN_ACL change only the value of ACL column in DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES views so I remove ACLs and PRIVs and restart.

Reviewing this question I notice that this error is for "APEX_210222" that is one of the schemas created during Apex installation. I try

DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'api.my.host'
    ,lower_port => 8443
    ,upper_port => 8443
    ,ace => XS$ACE_TYPE(
                    privilege_list => XS$NAME_LIST('http')
                    ,principal_name => 'APEX_210222'
                    ,principal_type => XS_ACL.ptype_db
                )

and the web_service_request is now working correctly.