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 are prerequisites to use Oracle-managed heterogeneous connectivity with Autonomous Database:

  • The target database must be accessible from the public internet on the port number supported for the specified database type. See Oracle-Managed Heterogeneous Connectivity Database Types and Ports for the list of supported non-Oracle database types and ports.

  • The target database must be configured to allow incoming SSL/TLS connections.

Note:

Oracle-managed heterogeneous connectivity supports connections to Oracle MySQL Database Service on private endpoints. When you connect to MySQL on a private endpoint, the connections 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:

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

  2. 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 hostname for Snowflake is your account identifier. To find your Snowflake account identifier, see Account Identifier Formats by Cloud Platform and Region.

    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

    HIVE

    Apache Hive

    MONGODB

    MongoDB

    MYSQL

    MySQL

    POSTGRES

    PostgreSQL

    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.

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

    For example:

    SELECT count(*) FROM sales@AWSREDSHIFT_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:

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:

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

  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:

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

MongoDB

MONGODB

27017

MongoDB Supported Versions

MySQL

MYSQL

3306

MySQL Supported Versions

PostgreSQL

POSTGRES

5432

PostgreSQL Supported Versions

Snowflake

SNOWFLAKE

443

Snowflake Supported Versions

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.

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.