0

I'm trying to do a REST/HTTP request from PL/SQL in OCI.

I am stumped when trying UTL_HTTP ( but successful using APEX_WEB_SERVICE package).

Has anyone had success using UTL_HTTP on OCI AutonomousDB?

BEGIN
    UTL_HTTP.SET_WALLET('');
    http_request := UTL_HTTP.begin_request(UTL_URL.Escape([url]), 'GET');
    http_response := UTL_HTTP.get_response(http_request);
    UTL_HTTP.read_text(http_response, return_text);
    DBMS_OUTPUT.put_line (return_text);
END;

Error report – ORA-01031: insufficient privileges ORA-06512: at "SYS.UTL_HTTP", line 136 ORA-06512: at "SYS.UTL_HTTP", line 1410
ORA-06512: at line 7
01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to perform a database operation without the necessary privileges.
*Action: Ask your database administrator or designated security administrator to grant you the necessary privileges

I have setup ACL as follows so http privilege is granted:

BEGIN
   DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
         host => '[domain]',
        lower_port => 443,
        upper_port => 443,
        ace =>  xs$ace_type(privilege_list => xs$name_list('http'),
                             principal_name => '[name]',
                             start_date => SYSTIMESTAMP,
                             principal_type => xs_acl.ptype_db));
END;
/
ctuzla
  • 33
  • 1
  • 6
dam0
  • 31
  • 5
  • from the [doc](https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/appendix-database-pl-sql-packages-restrictions.html#GUID-829A7D07-1EA4-4F59-AA60-F780FABAFDEC) it must be https, so are you trying http or https? – gsalem Jun 16 '21 at 10:11
  • Yes, it's a https - requiring SSL certificate. So I 'SET_Wallet' - expecting the Amazon root certificate to authenticate automatically from the cached certs? – dam0 Jun 17 '21 at 07:14
  • ..as per [Oracle Wallet configuration cannot be altered. All arguments for SET_WALLET API are ignored.](https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/appendix-database-pl-sql-packages-restrictions.html#GUID-829A7D07-1EA4-4F59-AA60-F780FABAFDEC) – dam0 Jun 17 '21 at 07:21

3 Answers3

2

UTL_HTTP is unsupported on OCI Autonomous DB -

Oracle Database Features That Are Not Supported

The following Oracle Database features, options and packs are not supported in Autonomous Database.

UTL_SMTP, UTL_HTTP, and UTL_TCP PL/SQL packages

Now I know..

dam0
  • 31
  • 5
1

try using

privilege_list => xs$name_list('connect', 'resolve')

Docs says:

Security Model This package is an invoker's rights package and the invoking user will need the connect privilege granted in the access control list assigned to the remote network host to which he wants to connect, as well as the use-client-certificates or the use-passwords privilege to authenticate himself with the remote Web server using the credentials stored in an Oracle wallet.

You also need 'resolve' to resolve the domain name.

MrE
  • 19,584
  • 12
  • 87
  • 105
0

Saying "UTL_HTTP is unsupported on OCI Autonomous DB" is misleading. First of all, you should clarify what flavor of ADB you are using. The doc link you referenced is for Autonomous Database on Dedicated Exadata Infrastructure (ADB-D). Autonomous Database on Shared Exadata Infrastructure (ADB-S) supports UTL_HTTP as already mentioned in the ADB-S doc.

ctuzla
  • 33
  • 1
  • 6