Call Web Services from Autonomous AI Database

Provides details for using UTL_HTTP to submit an HTTP request on a public host.

For example, to submit an HTTP request for a public host www.example.com, create an Access Control List for the host:

BEGIN
   DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
         host => 'www.example.com',
         ace =>  xs$ace_type( privilege_list => xs$name_list('http'),
                              principal_name => 'ADMIN',
                              principal_type => xs_acl.ptype_db));
END;

Then submit the HTTP request:

SELECT UTL_HTTP.REQUEST(url => 'https://www.example.com/') FROM dual;

See PL/SQL Package Notes for Autonomous AI Database for information on restrictions for UTL_HTTP on Autonomous AI Database.

Describes the steps to use UTL_HTTP to submit an HTTP request on a private host.

To submit a request to a target host on a private endpoint, the target host must be accessible from the source database's Oracle Cloud Infrastructure VCN. For example, you can connect to the target host when:

• Both the source database and the target host are in the same Oracle Cloud Infrastructure VCN.

• The source database and the target host are in different Oracle Cloud Infrastructure VCNs that are paired.

• The target host is an on-premises network that is connected to the source database's Oracle Cloud Infrastructure VCN using FastConnect or VPN.

You can also make UTL_HTTP calls with a customer-managed wallet when your Autonomous AI Database is on a private endpoint. See Make External Calls Using a Customer-Managed Wallet for more information.

To make a UTL_HTTP request to a target on a private endpoint:

1. Create an Access Control List for the host.

   For example:

   BEGIN
      DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
            host => 'www.example.com',
            ace => xs$ace_type( privilege_list => xs$name_list('http'),
                                principal_name => 'ADMIN',
                                principal_type => xs_acl.ptype_db),
                                private_target => TRUE);
   END;
   /

   As shown in this example, when you create an Access Control List for the host specify the private_target parameter with the value TRUE.

   Note
   
   If you set the ROUTE_OUTBOUND_CONNECTIONS database property, setting the private_target parameter to TRUE is not required in this API. See Enhanced Security for Outbound Connections with Private Endpoints for more information.

2. Submit the HTTP request:

   SELECT UTL_HTTP.REQUEST(
                   url => 'https://www.example.com/',
                   https_host => 'www.example.com') 
                FROM dual;

See PL/SQL Package Notes for Autonomous AI Database for information on restrictions for UTL_HTTP on Autonomous AI Database.

When your Autonomous AI Database instance is on a private endpoint you can use a proxy to submit HTTP requests with UTL_HTTP.

1. Set the HTTP_PROXY ACL on the proxy server.

   For example:

   BEGIN
     DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
          host =>'www-proxy-example.com',
          ace  => xs$ace_type(privilege_list => xs$name_list('HTTP_PROXY'),
                              principal_name => 'APPUSER1',
                              principal_type => xs_acl.ptype_db),
                              private_target => TRUE);
   END;
   /

   As shown in this example, when you create an Access Control List for the proxy server specify the private_target parameter with the value TRUE.

   Note
   
   If you set the ROUTE_OUTBOUND_CONNECTIONS database property, setting the private_target parameter to TRUE is not required in this API. See Enhanced Security for Outbound Connections with Private Endpoints for more information.
2. Set the HTTP ACL on the remote Web Server.

   For example:

   BEGIN
     DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
           host =>'example.com',
           ace => xs$ace_type( privilege_list => xs$name_list('HTTP'),
                               principal_name => 'APPUSER1',
                               principal_type => xs_acl.ptype_db)
   END;
   /

3. Set the wallet and the proxy for UTL_HTTP.

   For example:

   BEGIN
      UTL_HTTP.SET_WALLET('');
      UTL_HTTP.SET_PROXY('www-proxy-example:80');
   END;
   /

4. Submit an HTTP request:

   SELECT UTL_HTTP.REQUEST(    
                       url         => 'https://www.example.com/',
                       https_host  => 'www.example.com')
                FROM dual;

Describes how to pass a credential objects to UTL_HTTP.SET_CREDENTIAL procedure.

The UTL_HTTP.SET_CREDENTIAL procedure sets HTTP authentication information in the HTTP request header. The Web server needs this information to authorize the request.

The UTL_HTTP.SET_CREDENTIAL procedure enables you to pass credential objects to set HTTP authentication. Credential objects are schema objects, hence they can be accessed only by privileged users and enable you to configure schema-level privileges to access control the credentials. Passing credential objects is an appropriate and secure way to store and manage username/password/keys to be used for authentication.

The UTL_HTTP.SET_CREDENTIAL procedure is a secure and convenient alternative to UTL_HTTP.SET_AUTHENTICATION procedure.

Example

...
UTL_HTTP.SET_AUTHENTICATION (l_http_request, 'web_app_user', 'xxxxxxxxxxxx');
...

As shown in the example above, when you invoke SET_AUTHENTICATION procedure, you must pass the username/password in clear text as part of PL/SQL formal parameters. You might need to embed the username/password into various PL/SQL automation or cron scripts. Passing clear text passwords is a compliance issue that is addressed in UTL_HTTP.SET_CREDENTIAL procedure.

See SET_AUTHENTICATION Procedure and SET_AUTHENTICATION_FROM_WALLET Procedure for more information.

UTL_HTTP.SET_CREDENTIAL Syntax

UTL_HTTP.SET_CREDENTIAL (
    r          IN OUT NOCOPY req,
    credential IN VARCHAR2,
    scheme     IN VARCHAR2 DEFAULT 'Basic',
    for_proxy  IN BOOLEAN  DEFAULT FALSE);

Example to pass a credential object in the SET_CREDENTIAL procedure:

• Create a credential object:

  BEGIN DBMS_CLOUD.CREATE_CREDENTIAL (
      credential_name => 'HTTP_CRED',
      username        => 'web_app_user',
      password        => '<password>' );
  END;

  This creates a credential object which creates a stored username/password pair.

  See CREATE_CREDENTIAL Procedure for more information.

  See Specifying Scheduler Job Credentials for more information.

• Invoke UTL_HTTP.SET_CREDENTIAL procedure:

  DECLARE
        l_http_request  UTL_HTTP.REQ;
      BEGIN 
        l_http_request := UTL_HTTP.BEGIN_REQUEST('https://www.example.com/v1/dwcsdev/NAME/dwcs_small_xt1.csv');
        UTL_HTTP.SET_CREDENTIAL (l_http_request, 'HTTP_CRED','BASIC');
        ...
  END;
  

  This example first creates a request by invoking the BEGIN_REQUEST procedure and sets HTTP authentication information in the HTTP request header by invoking the SET_CREDENTIAL procedure. The Web server needs this information to authorize the request. The value l_http_request is the HTTP request, HTTP_CRED is the credentials name and BASIC is the HTTP authentication scheme.

See UTL_HTTP for more information.

See PL/SQL Package Notes for Autonomous AI Database for information on restrictions for UTL_HTTP on Autonomous AI Database.

When you use Oracle APEX SQL Commands or Database Actions SQL worksheet to run multiple sequential SQL commands, the commands can run in different database sessions that do not save the state of a previous statement. This behavior differs from desktop SQL clients such as SQL*Plus and SQL Developer that maintain a persistent connection to the database.

Oracle APEX SQL Commands and Database Actions SQL worksheet submissions to an Autonomous AI Database instance are stateless. This means running individual SQL and PL/SQL statements may save state in database memory, for example when you submit a command to use a wallet, but the state might be cleared before you run the next statement.

View the following table for the steps to keep the database memory state between statement executions for SQL commands you submit to Autonomous AI Database.

For example, use the following code block to run a utl_http.request() command that makes use of a Customer-Managed Wallet:

SELECT utl_http.request(url => 'https://api.example.com/', wallet_path => 'file:path_to_wallet', wallet_password => 'password' ) FROM DUAL";

Compare this to running with two consecutive statements that might fail if the utl_http.set_wallet() command and the utl_http.request() statement run individually, rather than as a single code block:

EXEC utl_http.set_wallet('file:WALLET_DIR/wallet.sso', 'password');
SELECT utl_http.request('https://api.example.com/') FROM DUAL;

You can configure your Autonomous AI Database to send database identity information as custom HTTP headers when making outbound HTTP requests.

ALTER DATABASE PROPERTY SET DATABASE_IDENTITY_NETWORK_HEADERS = '[
"DATABASE_NAME",
"REGION",
"DATABASE_OCID"
]';

ALTER DATABASE PROPERTY SET DATABASE_IDENTITY_NETWORK_HEADERS = '[
"DATABASE_NAME",
"REGION",
"TENANT_OCID",
"DATABASE_OCID",
"COMPARTMENT_OCID",
"CLOUD_DOMAIN",
"CLIENT_IP_ADDRESS"
]';

ALTER DATABASE PROPERTY SET DATABASE_IDENTITY_NETWORK_HEADERS_DOMAINS = '*';

ALTER DATABASE PROPERTY SET DATABASE_IDENTITY_NETWORK_HEADERS_DOMAINS = 'objectstorage.oraclecloud.com';

ALTER DATABASE PROPERTY SET DATABASE_IDENTITY_NETWORK_HEADERS_DOMAINS = 'oraclecloud.com, example.com';

X-ADB-Source-Database=
  {"databaseName":"MYDATABASENAME",
   "region":"region_identifier",
   "tenantOcid":"tenant_ocid",
   "databaseOcid":"database_ocid",
   "compartmentOcid":"compartment_ocid",
   "clientIpAddress":"client_ip_address"}