Make External Calls Using a Customer Managed Wallet

You can use the UTL_HTTP package when your Autonomous AI Database needs to access data on the internet over HTTP/HTTPS. The UTL_HTTP package lets you make HTTP callouts directly from SQL and PL/SQL.

If you’re calling an HTTPS endpoint, you must configure an Oracle Wallet. Autonomous AI Database requires a wallet that contains the trusted root and intermediate certificates for any HTTPS endpoint your database will connect to. The UTL_HTTP package uses this wallet to establish a secure SSL/TLS connection. You can create and manage the wallet with the orapki utility.

Note: Plain HTTP (non-HTTPS) requests don’t require an Oracle Wallet.

The sections below explain how to configure and use a customer-managed wallet to make outbound HTTPS calls with the UTL_HTTP package on Autonomous AI Database.

Prerequisites to Use a Customer-Managed Wallet with External Calls

Before you start, make sure you have:

Access to the Autonomous AI Database using an account that can run PL/SQL and configure UTL_HTTP.
Access to the target HTTPS endpoint and its full certificate chain (root + intermediate certificates).
A workstation where you can run orapki to create and validate an Oracle Wallet. You need to install Oracle Database to get orapki utility. See Installing Oracle Database for details.

Prepare the Customer-Managed Wallet

In this step, you’ll create and validate the wallet on your workstation before uploading it to Autonomous AI Database.

Obtain or create a customer-managed wallet

Example using orapki:

-- Create an SSL Wallet and load the Root CERTs using orapki utility
$ORACLE_HOME/bin/orapki wallet create -wallet /u01/web/wallet -pwd ********
$ORACLE_HOME/bin/orapki wallet add -wallet /u01/web/wallet -trusted_cert -cert MyWebServer.cer -pwd ********
-- Store the credentials in the SSL Wallet using mkstore utility
$ORACLE_HOME/bin/mkstore -wrl /u01/web/wallet -createCredential secret-from-the-wallet 'example@oracle.com'
********Enter wallet password: ********

Validate the wallet

$ORACLE_HOME/bin/orapki wallet display -wallet /u01/web/wallet

You should see all imported certificates listed.

Upload the wallet

Once the customer-managed wallet is ready (including any required self-signed/root/intermediate certificates), upload the wallet files to a location in Oracle Cloud Infrastructure (OCI) Object Storage.

Use a customer-managed wallet with UTL_HTTP

This section shows how to download your wallet from Object Storage, allow network access to the HTTPS endpoint, and then make HTTPS calls using the UTL_HTTP package.

Create a credential for Object Storage access:
```
 BEGIN
 DBMS_CLOUD.CREATE_CREDENTIAL(credential_name => 'DEF_CRED_NAME',
 username => 'user1@example.com',
 password => 'password'
 );
 END;
 /
```
The values you provide for username and password depend on the Cloud Object Storage service you are using. This creates the credential you use to access the Cloud Object Storage where the customer-managed wallet resides.
Create (or reuse) a directory object for the wallet:

Use an existing directory or create a new a directory for the wallet file. For example:
```
 CREATE DIRECTORY wallet_dir AS 'directory_path_of_your_choice';
```
See Create Directory in Autonomous AI Database for information on creating directories.

Get the absolute directory path:

You’ll need the absolute directory path when calling UTL_HTTP.SET_WALLET.

 SELECT DIRECTORY_NAME, DIRECTORY_PATH FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = '<wallet_dir>';

Download the wallet file from Object Storage into the directory:

Use DBMS_CLOUD.GET_OBJECT to copy the wallet file into your directory. For example:
```
 BEGIN
 DBMS_CLOUD.GET_OBJECT(
 credential_name => 'DEF_CRED_NAME',
 object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/cwallet.sso',
 directory_name => 'WALLET_DIR');
 END;
 /
```
In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

Allow outbound network access (ACL) to the HTTPS endpoint:

You must allow the database user/schema to reach the target host over the network.

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

On Exadata Cloud@Customer deployments, you also need to setup proxy as shown below:

 BEGIN
   UTL_HTTP.SET_PROXY('www-proxy.us.oracle.com:80', 'oracle.com');
 END;
 /

Make HTTPS calls with UTL_HTTP:

Simple GET request :

 DECLARE
 l_http_req UTL_HTTP.req;
 l_http_resp UTL_HTTP.resp;
 l_response VARCHAR2(32767);

 BEGIN
    utl_http.set_wallet('file:<absolute_directory_path_from_step_3>');
    l_http_req := UTL_HTTP.BEGIN_REQUEST('https://api.example.com/status');
    l_http_resp := UTL_HTTP.GET_RESPONSE(l_http_req);
    LOOP UTL_HTTP.READ_LINE(l_http_resp, l_response, TRUE);
        DBMS_OUTPUT.PUT_LINE(l_response);
    END LOOP;
    UTL_HTTP.END_RESPONSE(l_http_resp);
 END;
 /

POST request with JSON payload:

 DECLARE
   l_req UTL_HTTP.req;
   l_resp UTL_HTTP.resp;
   l_line VARCHAR2(32767);
 BEGIN
   utl_http.set_wallet('file:<absolute_directory_path_from_step_3>');
   l_req := UTL_HTTP.BEGIN_REQUEST( url => 'https://api.example.com/data', method => 'POST' );
   UTL_HTTP.SET_HEADER(l_req, 'Content-Type', 'application/json');
   UTL_HTTP.WRITE_TEXT(l_req, '{"key":"value"}');
   l_resp := UTL_HTTP.GET_RESPONSE(l_req);
   LOOP UTL_HTTP.READ_LINE(l_resp, l_line, TRUE);
     DBMS_OUTPUT.PUT_LINE(l_line);
   END LOOP;
   UTL_HTTP.END_RESPONSE(l_resp);
  END;
  /

Troubleshooting for common errors:

Certificate chain errors

Error: ORA-29024: Certificate validation failure

Root and intermediate certificates may be missing.
The target endpoint may be using a new CA - re-download full cert chain and re-create the wallet.

Wallet path errors

ORA-28759: Failure to open file

Incorrect wallet path in SET_WALLET.
Missing file: prefix.
Uploaded wallet files not present in directory.

Handshake failures

ORA-24263 / ORA-29005

TLS protocol mismatch.
Bad or expired certificates.
Endpoint enforces TLS > 1.2

Use a Customer-Managed Wallet for Scheduler Email Notifications (SMTP)

This section shows how to configure Scheduler email notifications to use an SMTP server over TLS (STARTTLS) with a customer-managed wallet.

Before you begin, make sure you’ve already prepared the customer-managed wallet (created it locally, validated it, and uploaded it to Object Storage). See Prepare the Customer-Managed Wallet for details.

To use a customer-managed wallet with the scheduler email server:

Create a credential for Object Storage access:

You can use DBMS_CLOUD.CREATE_CREDENTIAL to create a credential to access Cloud Object Storage.
```
 BEGIN
   DBMS_CLOUD.CREATE_CREDENTIAL(
     credential_name => 'DEF_CRED_NAME',
     username => 'user1@example.com',
     password => 'password'
 );
 END;
 /
```
The values you provide for username and password depend on the Cloud Object Storage service you are using. This creates the credential you use to access the Cloud Object Storage where the customer-managed wallet resides.
Create (or reuse) a directory object for the wallet:

Use an existing directory or create a new a directory for the wallet file. For example:
```
 CREATE DIRECTORY wallet_dir AS 'directory_path_of_your_choice';
```
See Create Directory in Autonomous AI Database for information on creating directories.

Get the absolute directory path:

You’ll need the absolute directory path when calling UTL_HTTP.SET_WALLET.

 SELECT DIRECTORY_NAME, DIRECTORY_PATH FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = '<wallet_dir>';

Download the wallet file from Object Storage into the directory:

Use DBMS_CLOUD.GET_OBJECT to copy the wallet file into your directory. For example:
```
 BEGIN
   DBMS_CLOUD.GET_OBJECT(
     credential_name => 'DEF_CRED_NAME',
     object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/cwallet.sso',
     directory_name => 'WALLET_DIR');
 END;
 /
```
In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

Configure Scheduler email (SMTP + STARTTLS):

Run the commands to set up the scheduler to send SMTP email for scheduler job notifications:

 EXEC DBMS_CLOUD.CREATE_CREDENTIAL('EMAIL_CRED', '<user_ocid>', '<password>');
 GRANT EXECUTE ON admin.EMAIL_CRED TO sys;
 EXEC DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(
       'EMAIL_SERVER',
       'smtp.email.us-ashburn-1.oci.oraclecloud.com:587'
 );
 EXEC DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(
         'EMAIL_SERVER_CREDENTIAL',
         'EMAIL_CRED'
 );
 EXEC DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(
         'EMAIL_SERVER_ENCRYPTION',
         'STARTTLS'
 );

The commands does the following:

Sets the Scheduler SMTP endpoint (EMAIL_SERVER)
Stores SMTP login details in EMAIL_CRED and points Scheduler to it
Enables TLS encryption using STARTTLS

See SET_SCHEDULER_ATTRIBUTE Procedure for more information.

Create a credential for the wallet password:

Create a credential to store the password for the customer-managed wallet.
```
 BEGIN
   DBMS_CLOUD.CREATE_CREDENTIAL(
     credential_name => 'WALLET_CRED',
     username        => 'any_user',
     password        => 'password'
   );
 END;
 /
```
This creates the credential you use in the next step to provide the password for the customer-managed wallet.

Tell Scheduler where the wallet is and how to unlock it:

Set the scheduler wallet directory and the wallet credential.

 BEGIN
   DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(
     'EMAIL_SERVER_WALLET_DIRECTORY',
     'WALLET_DIR'
   );

   DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(
     'EMAIL_SERVER_WALLET_CREDENTIAL',
     'ADMIN.WALLET_CRED'
   );
 END;
 /

Verify the Scheduler email configuration:

Query the DBA_SCHEDULER_GLOBAL_ATTRIBUTE view to verify the values you set in the previous steps.

 SELECT attribute_name, value
 FROM DBA_SCHEDULER_GLOBAL_ATTRIBUTE
 WHERE attribute_name LIKE 'EMAIL_SERVER%' ORDER BY 1, 2;

 ATTRIBUTE_NAME                 VALUE

 ------------------------------ -----------------------------------------------
 EMAIL_SERVER                   smtp.email.us-ashburn-1.oci.oraclecloud.com:587
 EMAIL_SERVER_CREDENTIAL        "ADMIN"."EMAIL_CRED"
 EMAIL_SERVER_ENCRYPTION        STARTTLS
 EMAIL_SERVER_WALLET_CREDENTIAL "ADMIN"."WALLET_CRED"
 EMAIL_SERVER_WALLET_DIRECTORY  "WALLET_DIR"

Reference

Useful orapki commands:

orapki wallet display -wallet <path> orapki wallet add -wallet <path>
-trusted_cert -cert <cert-file> orapki wallet create -wallet <path> -auto_login

Sample wallet directory structure:

cmw_wallet/
- ewallet.p12
- cwallet.sso