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

Autonomous 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 Database configures and sets up the connection to the non-Oracle database.

The following is a prerequisite to use Oracle-managed heterogeneous connectivity with Autonomous 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.

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

On Autonomous 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.

Note:
Supplying the credential_name parameter is required.
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.

To access Google Analytics, Google BigQuery, or ServiceNow 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
`db2`	IBM Db2
`google_analytics`	Google Analytics
`google_bigquery`	Google BigQuery
`hive`	Apache Hive
`mongodb`	MongoDB
`mysql`	MySQL
`mysql_community`	MySQL Community Edition
`postgres`	PostgreSQL
`salesforce`	Salesforce
`servicenow`	ServiceNow
`snowflake`	Snowflake

Autonomous 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
`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.
`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.
`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"`.

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 when you use SELECT with Google BigQuery must be in quotes. 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 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 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 Database configures and sets up the connection to the Oracle MySQL Database Service.
Oracle-Managed Heterogeneous Connectivity Database Types and Ports
Shows the non-Oracle databases that you can connect to from Autonomous 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.
Access Heterogeneous Connectivity Information and Samples
Oracle Autonomous Database provides heterogeneous connectivity information for database links to non-Oracle databases.

Parent topic: Create Database Links to Non-Oracle Databases

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

Autonomous 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 Database configures and sets up the connection to the Oracle MySQL Database Service.

Note:

Database links from an Autonomous Database to an Oracle MySQL Database Service that is on a private endpoint are only supported in commercial regions and US Government regions.

This feature is enabled by default in all commercial regions.

This feature is enabled by default in US Government regions for newly provisioned databases.

For existing US Government databases on a private endpoint, if you want to create database links from an Autonomous Database to a target in a US Government region, please file a Service Request at Oracle Cloud Support and request to enable the private endpoint in government regions database linking feature.

US Government regions include the following:

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 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:

On Autonomous 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.

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;
/

Use the database link to access data on the target database.
For example:
```
SELECT count(*) FROM sales@MYSQL_LINK
```

Note:

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 Database Gateway for ODBC User's Guide
CREATE_DATABASE_LINK Procedure
UPDATE_CREDENTIAL Procedure

Parent topic: Create Database Links to Non-Oracle Databases with Oracle-Managed Heterogeneous Connectivity

Oracle-Managed Heterogeneous Connectivity Database Types and Ports

Shows the non-Oracle databases that you can connect to from Autonomous 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 Hive	`hive`	443	Hive Supported Versions
Google Analytics	`google_analytics`	443	Google Analytics Supported Versions
Google BigQuery	`google_bigquery`	443	Google BigQuery 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
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

Parent topic: Create Database Links to Non-Oracle Databases with Oracle-Managed Heterogeneous Connectivity

Access Heterogeneous Connectivity Information and Samples

Oracle Autonomous 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.

Parent topic: Create Database Links to Non-Oracle Databases with Oracle-Managed Heterogeneous Connectivity

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`.

Parent topic: Access Heterogeneous Connectivity Information and Samples