Create Database Links to Non-Oracle AI Databases with Oracle-Managed Heterogeneous Connectivity

Autonomous AI Database support for Oracle-managed heterogeneous connectivity makes it easy to create database links to non-Oracle databases.

When you use database links with Oracle-managed heterogeneous connectivity, Autonomous AI Database configures and sets up the connection to the non-Oracle database.

Note: With Oracle-managed heterogeneous connectivity the support is for query-only connections on the remote database. That is, updates are not supported when using Oracle-managed heterogeneous connectivity.

The following are the prerequisites for using Oracle-managed heterogeneous connectivity with Autonomous AI Database:

When the target database is on a public endpoint, database must be configured to allow incoming SSL/TLS connections with CA signed certificates.

Oracle-managed heterogeneous connectivity supports connections to target database services on private endpoints (for example you can connect to Oracle MySQL Database Service when the service is on a private endpoint). When you connect to a non-oracle database with Oracle-managed heterogeneous connectivity on a private endpoint, the connection uses TCP protocol and it does not require SSL/TLS to be configured on the target database.

See Create Database Links to Oracle MySQL on a Private Endpoint with Oracle-Managed Heterogeneous Connectivity for more information.
When the target database is on private endpoint and configured to enforce SSL based connections, use the gateway_params parameter called enable_ssl and set it to TRUE. For example:
```
gateway_params => JSON_OBJECT(
     'db_type' value 'snowflake',
     'enable_ssl' value true)
```
See CREATE_DATABASE_LINK Procedure for more information.
When the target database is on private endpoint and configured to enforce SSL based connections, use the gateway_params parameter called enable_ssl and set it to true.

See CREATE_DATABASE_LINK Procedure for more information.

To create database links to a non-Oracle database using Oracle-managed heterogeneous connectivity, do the following:

On Autonomous AI Database create credentials to access the target database. The username and password you specify with DBMS_CLOUD.CREATE_CREDENTIAL are the credentials for the target database used within the database link.

For example:
```
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'AWS_REDSHIFT_LINK_CRED',
    username => 'nick',
    password => 'password'
  );
END;
/
```
This operation stores the credentials in the database in an encrypted format. You can use any name for the credential name. Supplying the credential_name parameter is required.

To access Google Analytics, Google BigQuery, Google Drive, Youtube, ServiceNow, or Microsoft SharePoint with OAuth2, the credential must include the params parameter with the value gcp_oauth2.

For example:
```
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'SERVICENOW_OAUTH',
    params => JSON_OBJECT(
                 'gcp_oauth2' value JSON_OBJECT(
                        'client_id' value '*CLIENT_ID*',
                        'client_secret' value '*CLIENT_SECRET*',
                        'refresh_token' value '*Refresh_Token*')));
END;
/
```
See CREATE_CREDENTIAL Procedure for more information.

Create the database link to the target database using DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.

For example, to create a database link to AWS Redshift:

BEGIN
     DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name => 'AWSREDSHIFT_LINK',
          hostname => '*example.com*',
          port => 5439,
          service_name => '*example_service_name*',
          credential_name => 'AWS_REDSHIFT_LINK_CRED',
          gateway_params => JSON_OBJECT('db_type'  value 'awsredshift'),
          ssl_server_cert_dn => NULL);
END;
/

The service_name is the database name of the non-Oracle database.

The gateway_params db_type value that you supply must be one of the supported values:

db_type Value	Database Type
`awsredshift`	Amazon Redshift
`azure`	Microsoft SQL Server Azure SQL Azure Synapse Analytics
`box`	Box
`cassandra`	Apache Cassandra
`databricks`	Databricks
`db2`	IBM Db2
`google_analytics`	Google Analytics
`google_bigquery`	Google BigQuery
`google_drive`	Google Drive
`hive`	Apache Hive
`mongodb`	MongoDB
`mysql`	MySQL The `VECTOR` data type is not supported for `db_type=mysql`. See The VECTOR Type for more information.
`mysql_community`	MySQL Community Edition
`postgres`	PostgreSQL
`salesforce`	Salesforce
`servicenow`	ServiceNow
`sharepoint`	Microsoft SharePoint
`snowflake`	Snowflake
`youtube`	YouTube

Autonomous AI Database automatically configures and handles the secure connection to a target database and your connections are end-to-end encrypted. Oracle-managed heterogeneous connectivity is preconfigured with a wallet that contains most of the common trusted root and intermediate SSL certificates. Thus, NULL must be provided as the value for the ssl_server_cert_dn parameter.

To ensure security when using database links with Oracle-managed heterogeneous connectivity, the connection port is restricted and must have SSL/TLS enabled. You specify the target database port with the port parameter.

See Oracle-Managed Heterogeneous Connectivity Database Types and Ports for the list of supported non-Oracle database types.

The HETEROGENEOUS_CONNECTIVITY_INFO view provides information on supported Oracle Heterogeneous Connectivity types and shows a PL/SQL code sample for each supported type. See Access Heterogeneous Connectivity Information and Samples for more information.

When you specify the gateway_params parameter, for some db_type values, additional gateway_params parameters are supported:

`db_type` Value	Supported Gateway Parameters with Specified db_type
`azure`	When the `db_type` value is `azure` there are two optional parameters, `auth_method` and `domain` to support NTLM/AD authentication. When NTLMv2 is configured, set `auth_method=10` and set `domain` to the Windows domain value. To use an Azure Active Directory password, set `auth_method=13`. Do not use `auth_method=13` to access an Oracle on-premises Database. See Microsoft SQL Server Authentication Method for more information.
`google_analytics`	When the `db_type` value is `google_analytics` the `hostname` is not used and you can provide value such as `example.com`. For `db_type` `google_analytics`, the credential must be an OAuth type credential using the `params` parameter with `gcp_oauth2` values specified (`client_id`, `client_secret`, and `refresh_token`). See CREATE_CREDENTIAL Procedure for more information.
`google_bigquery`	When `db_type` is `google_bigquery`, the parameter `project` is valid. This parameter specifies the project name on `google_bigquery` and is required. When the `db_type` value is `google_bigquery` the `hostname` is not used and you can provide value such as `example.com`. For `db_type` `google_bigquery`, the credential must be an OAuth type credential using the `params` parameter with `gcp_oauth2` values specified (`client_id`, `client_secret`, and `refresh_token`). See CREATE_CREDENTIAL Procedure for more information.
`google_drive`	For `db_type` `google_drive`, the credential you supply with `DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK` must be an OAuth type credential using the `params` parameter with `gcp_oauth2` values specified (`client_id`, `client_secret`, and `refresh_token`). To connect to Google Drive and get data you must supply the gateway parameters `directory_name` and `file_name`. These parameters specify a model file (auto REST file) that maps the JSON response to the relational model. The model file specifies the endpoints, table mapping, and HTTP response code for processing the JSON response. See Model file syntax and Example Model file for more information. When you use `gateway_params` parameter with `db_type` `google_drive` you must supply the `gateway_params` parameter `db_type` with the value `'google_drive'` and the `directory_name` and `file_name` parameters, along with OAuth type credentials: The `directory_name` parameter specifies the directory with the REST config file. You could create this directory as follows: `create or replace directory google_drive_dir as 'GOOGLE_DRIVE_DIR';` Obtain and download the auto REST file to the specified directory. For example: `exec DBMS_CLOUD.get_object('google_drive_dir_cred', 'https://objectstorage.<...>/google_drive.rest','GOOGLE_DRIVE_DIR');` Set the `file_name` value to the name of the auto REST file you downloaded, "`google_drive.rest`". Then you can use the REST config file with OAuth 2.0.
`hive`	When `db_type` is `hive`, the parameter `http_path` is valid. This parameter specifies the HttpPath value, if required, to connect to the Hive instance.
`salesforce`	When you use `gateway_params` parameter with `db_type` `salesforce`, you must supply the `security_token` option. The security token is a case-sensitive alphanumeric code. See Reset Your Security Token for more information. When you use `gateway_params` parameter with `db_type` `salesforce`, you must supply the correct `hostname` parameter. Salesforce provides two forms of URLs for the Salesforce service account: `xxxx.develop.lightning.force.com` form of URL `xxxxmy.salesforce.com` form of URL as shown under the View profile tab. Oracle-Managed Heterogeneous Connectivity only supports the `xxxxmy.salesforce.com` form of URL for the `hostname` parameter value.
`servicenow`	To connect to ServiceNow and get data you must supply the gateway parameters `directory_name` and `file_name`. These parameters specify a model file (REST config file) that maps the JSON response to the relational model. The model file specifies the endpoints, table mapping, and HTTP response code for processing the JSON response. See Model file syntax and Example Model file for more information. When you use `gateway_params` parameter with `db_type` `servicenow` there are two supported options: Basic Authentication: you must supply the `gateway_params` parameter `db_type` with the value `'servicenow'`, and supply the `directory_name` and `file_name` parameters along with username/password type credentials. OAuth 2.0 Authentication: you must supply the `gateway_params` parameter `db_type` with the value `'servicenow'`, and the `directory_name`, `file_name`, and `token_uri` parameters, along with OAuth type credentials. For OAuth 2.0 authentication with `db_type` `servicenow`, the credential must be an OAuth type credential using the `params` parameter with `gcp_oauth2` values specified specified (`client_id`, `client_secret`, and `refresh_token`). See CREATE_CREDENTIAL Procedure for more information. The `directory_name` parameter specifies the directory with the ServiceNow REST config file. You could create this directory as follows: `create or replace directory servicenow_dir as 'SERVICENOW_DIR';` Obtain and download the ServiceNow REST config file to the specified directory. For example: `exec DBMS_CLOUD.get_object('servicenow_dir_cred', 'https://objectstorage.<...>/servicenow.rest','SERVICENOW_DIR');` Set the `file_name` value to the name of the REST config file you downloaded, "`servicenow.rest`". Then you can use the ServiceNow REST config file with either basic authentication or OAuth2.0. See HETEROGENEOUS_CONNECTIVITY_INFO View for samples.
`sharepoint`	When you use `gateway_params` parameter with `db_type` `sharepoint`, also specify values for `auth_uri`, `scope`, `service_url`, and `token_uri`. For `db_type` `sharepoint`, the credential you supply with `DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK` must be an OAuth type credential using the `params` parameter with `gcp_oauth2` values specified (`client_id`, `client_secret`, and `refresh_token`). See CREATE_CREDENTIAL Procedure for more information. Generate the `refresh_token` for tenant_id`.sharepoint.com/.default` offline_access. See the following for more information: Determine the scope and OAuth 2.0 authentication. Set values for `gateway_params` for `db_type` `sharepoint`: `auth_uri`: Set the `auth_uri` from Azure. See Obtain application client information and endpoints for more information. `scope`: Set the scope. See Connection option descriptions for more information. `service_url`: Set the `service_url`. See Service URL for more information. `token_uri`: Get the `token_uri` from Azure. See Obtain application client information and endpoints for more information.
`snowflake`	When you use `gateway_params` parameter with `db_type` `snowflake`, use the Snowflake account identifier as the `hostname` parameter. In this case, the driver adds `snowflakecomupting.com`, so you do not pass this part of the hostname explicitly. To find your Snowflake account identifier, see Account Identifier Formats by Cloud Platform and Region. For example: for the Snowflake account: `https://example-marketing_test_account.snowflakecomputing.com` Set the `hostname` value to `"example-marketing_test_account"`. When the `db_type` is `SNOWFLAKE`, the optional parameters: `role`, `schema`, and `lakehouse` are valid. These values specify a different schema, role, or lakehouse value, other than the default. For example: `gateway_params => JSON_OBJECT( 'db_type' value 'snowflake', 'role' value 'ADMIN', 'schema' value 'PUBLIC', 'lakehouse' value 'TEST' )`
`youtube`	To connect to YouTube and get data, you must supply the parameters `directory_name` and `file_name`. These parameters specify a model file (REST config file) that maps the JSON response to the relational model. The model file specifies the endpoints, table mapping, and HTTP response code for processing the JSON response. See Model file syntax and Example Model file for more information. For `db_type` `youtube`, the credential must be an OAuth type credential using the `params` parameter with `gcp_oauth2` values specified (`client_id`, `client_secret`, and `refresh_token`). See CREATE_CREDENTIAL Procedure for more information. The `directory_name` parameter specifies the directory with the youtube REST config file. For example, to create this directory and add the required contents: Create a directory on the Autonomous AI Database instance: `create or replace directory youtube_dir as 'YOUTUBE_DIR';` Obtain and download the auto rest REST config file to the specified directory. For example: `exec DBMS_CLOUD.get_object('youtube_dir_cred', 'https://objectstorage.<...>/youtube.rest','YOUTUBE_DIR');` Set the `file_name` value to the name of the REST config file you downloaded, `'youtube.rest'`. Then you can use the REST config file with OAuth2.0. See HETEROGENEOUS_CONNECTIVITY_INFO View for samples.
`box`	To connect to Box and get data, you must supply the parameters `directory_name` and `file_name`. These parameters specify a model file (REST config file) that maps the JSON response to the relational model. The model file specifies the endpoints, table mapping, and HTTP response code for processing the JSON response. See Model file syntax and Example Model file for more information. For `db_type` `box`, the credential must be an OAuth type credential using the `params` parameter with `gcp_oauth2` values specified (`client_id`, `client_secret`, and `refresh_token`). See CREATE_CREDENTIAL Procedure for more information. The `directory_name` parameter specifies the directory with the box REST config file. For example, to create this directory and add the required contents: Create a directory on the Autonomous AI Database instance: `create or replace directory box_dir as 'BOX_DIR';` Obtain and download the auto rest REST config file to the specified directory. For example: `exec DBMS_CLOUD.get_object('box_dir_cred', 'https://objectstorage.<...>/box.rest','BOX_DIR');` Set the `file_name` value to the name of the REST config file you downloaded, `'box.rest'`. Then you can use the REST config file with OAuth2.0. See HETEROGENEOUS_CONNECTIVITY_INFO View for samples.
`databricks`	To connect to Databricks and get data, you must supply the parameters `directory_name` and `file_name`. These parameters specify a model file (REST config file) that maps the JSON response to the relational model. The model file specifies the endpoints, table mapping, and HTTP response code for processing the JSON response. See Model file syntax and Example Model file for more information. For `db_type` `box`, the credential must be an OAuth type credential using the `params` parameter with `gcp_oauth2` values specified (`client_id`, `client_secret`, and `refresh_token`). See CREATE_CREDENTIAL Procedure for more information. The `directory_name` parameter specifies the directory with the box REST config file. For example, to create this directory and add the required contents: Create a directory on the Autonomous AI Database instance: `create or replace directory databricks_dir as 'BOX_DIR';` Obtain and download the auto rest REST config file to the specified directory. For example: `exec DBMS_CLOUD.get_object('databricks_dir_cred', 'https://objectstorage.<...>/databricks.rest','DATABRICKS_DIR');` Set the `file_name` value to the name of the REST config file you downloaded, `'databricks.rest'`. Then you can use the REST config file with OAuth2.0. See HETEROGENEOUS_CONNECTIVITY_INFO View for samples.

See CREATE_DATABASE_LINK Procedure for more information.

Use the database link to access data on the target database.

For example:
```
SELECT count(*) FROM sales@AWSREDSHIFT_LINK
```
The table name you specify in a SELECT statement must be properly quoted as an identifier, this applies to Google BigQuery, MySQL, and PostgreSQL. For example:
```
SELECT count(*) FROM "sales"@GOOGLE_BIGQUERY_LINK
```
Note:

For the credentials you create in Step 1, the target database credentials, if the password of the target user changes you can update the credential that contains the target user’s credentials as follows:
```
BEGIN
  DBMS_CLOUD.UPDATE_CREDENTIAL (
    credential_name => 'AWS_REDSHIFT_LINK_CRED',
    attribute => 'PASSWORD',
    value => 'password');
END;
/
```
Where password is the new password.

After this operation, the existing database links that use this credential continue to work without having to drop and recreate the database links.
For additional information, see:
- Supported SQL Syntax and Functions in Oracle AI Database Gateway for ODBC User's Guide
- CREATE_DATABASE_LINK Procedure
- UPDATE_CREDENTIAL Procedure

Create Database Links to Oracle MySQL on a Private Endpoint with Oracle-Managed Heterogeneous Connectivity

Autonomous AI Database support for Oracle-managed heterogeneous connectivity makes it easy to create database links to Oracle MySQL Database Service on a private endpoint. When you use database links with Oracle-managed heterogeneous connectivity, Autonomous AI Database configures and sets up the connection to the Oracle MySQL Database Service.

The following are prerequisites to use Oracle-managed heterogeneous connectivity with Oracle MySQL Database Service on a private endpoint:

Create a DNS name using private DNS Zone pointing to private IP of your Oracle MySQL Database Service in your VCN. See Private DNS.
Create an Autonomous AI Database with a Private Endpoint on same subnet.
Configure the VCN for the Oracle MySQL Database Service to allow incoming connections on port 3306.

To create database links to a Oracle MySQL Database Service on a private endpoint using Oracle-managed heterogeneous connectivity, do the following:
1. On Autonomous AI Database create credentials to access the Oracle MySQL Database Service. The username and password you specify with DBMS_CLOUD.CREATE_CREDENTIAL are the credentials for the Oracle MySQL Database Service used within the database link.
  
  Note: Supplying the credential_name parameter is required.
  
  For example:
```
BEGIN
   DBMS_CLOUD.CREATE_CREDENTIAL(
     credential_name => 'MYSQL_LINK_CRED',
     username => 'NICK',
     password => 'password'
   );
END;
/
```
  This operation stores the credentials in the database in an encrypted format. You can use any name for the credential name.
2. Create the database link to the Oracle MySQL Database Service using DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.
  
  For example, to create a database link:
```
BEGIN
     DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name => 'MYSQL_LINK',
          hostname => '*mysql.example.com*',
          port => '3306',
          service_name => '*mysql.example_service_name*',
          ssl_server_cert_dn => NULL,
          credential_name => 'MYSQL_LINK_CRED',
          private_target => TRUE,
          gateway_params => JSON_OBJECT('db_type' value 'mysql'));
END;
/
```
3. Use the database link to access data on the target database.
  
  For example:
```
SELECT count(*) FROM sales@MYSQL_LINK
```
  Note:
  
  For the credentials you create in Step 1, the target database credentials, if the password of the target user changes you can update the credential that contains the target user’s credentials as follows:
```
BEGIN
  DBMS_CLOUD.UPDATE_CREDENTIAL (
    credential_name => 'MYSQL_LINK_CRED',
    attribute => 'PASSWORD',
    value => 'password');
END;
/
```
  Where password is the new password.
  
  After this operation, the existing database links that use this credential continue to work without having to drop and recreate the database links.
  For additional information, see:
  - Supported SQL Syntax and Functions in Oracle AI Database Gateway for ODBC User's Guide
  - CREATE_DATABASE_LINK Procedure
  - UPDATE_CREDENTIAL Procedure

Oracle-Managed Heterogeneous Connectivity Database Types and Ports

Shows the non-Oracle databases that you can connect to from Autonomous AI Database with Oracle-managed heterogeneous connectivity, and lists the supported port value for each database type. Also provides a link where you can see the supported database versions for each database type.

Note: Oracle uses Progress DataDirect connectors. The Database Support column provides links to the Progress website where you can find the supported database versions for each database type.

Database Type	db_type Value	Required Port	Database Support
Amazon Redshift	`awsredshift`	5439	Amazon Redshift Supported Versions
Azure SQL Microsoft SQL Server Azure Synapse Analytics	`azure`	1433	Azure SQL Supported Versions Azure Synapse Analytics Supported Versions
Apache Cassandra	`cassandra`	9042	Cassandra Supported Versions
Apache Hive	`hive`	443	Hive Supported Versions
Google Analytics	`google_analytics`	443	Google Analytics Supported Versions
Google BigQuery	`google_bigquery`	443	Google BigQuery Supported Versions
Google Drive	`google_drive`	443	Google Drive Supported Versions
Apache Hive	`hive`	443	Hive Supported Versions
IBM Db2 11.5.6 or greater IBM Db2 11.5.5 or less	`db2`	25000 50000	IBM Db2 Supported Versions
Microsoft SharePoint	`sharepoint`	443	Microsoft SharePoint Supported Versions
MongoDB	`mongodb`	27017	MongoDB Supported Versions
MySQL	`mysql`	3306	MySQL Supported Versions
MySQL Community Edition	`mysql_community`	3306
PostgreSQL	`postgres`	5432	PostgreSQL Supported Versions
Salesforce	`salesforce`	19937	Salesforce Supported Versions
ServiceNow	`servicenow`	443	ServiceNow Supported Versions
Snowflake	`snowflake`	443	Snowflake Supported Versions
Youtube	`youtube`	443	Youtube Supported Versions
Box	`box`	443	VirtualBox Supported Versions
Databricks	`databricks`	443	Databricks Runtime release notes versions and compatibility

Access Heterogeneous Connectivity Information and Samples

Oracle Autonomous AI Database provides heterogeneous connectivity information for database links to non-Oracle databases.

The HETEROGENEOUS_CONNECTIVITY_INFO view provides information on supported Oracle Heterogeneous Connectivity types and shows a PL/SQL code sample for each supported type.

For example:

SELECT * FROM HETEROGENEOUS_CONNECTIVITY_INFO WHERE DATABASE_TYPE = 'hive';

HETEROGENEOUS_CONNECTIVITY_INFO View

The HETEROGENEOUS_CONNECTIVITY_INFO view lists connectivity information and examples for connecting with PL/SQL using database links and Oracle Managed Heterogeneous Connectivity.

Column	Datatype	Description
`DATABASE_TYPE`	`VARCHAR2(32)`	Database type value used with `gateway_params` parameter.
`REQUIRED_PORT`	`NUMBER`	Supported port values for the database type.
`DESCRIPTION`	`CLOB`	Specifies a description for the `DATABASE_TYPE`.
`OPTIONAL_PARAMETERS`	`VARCHAR2(1024)`	Specifies the valid optional parameters for the `DATABASE_TYPE`.
`SAMPLE_USAGE`	`CLOB`	Shows sample PL/SQL usage for the `DATABASE_TYPE`.