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.

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

  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.

    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, Youtube, 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.

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

    youtube

    YouTube

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

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

    When the db_type is SNOWFLAKE, the optional parameters: role, schema, and warehouse are valid. These values specify a different schema, role, or warehouse value, other than the default. For example:

    gateway_params => JSON_OBJECT(
         'db_type' value 'snowflake',
         'role' value 'ADMIN',
         'schema' value 'PUBLIC',
         'warehouse' 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:

    1. Create a directory on the Autonomous Database instance:

      create or replace directory youtube_dir as 'YOUTUBE_DIR';
    2. 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');
    3. 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.

    See CREATE_DATABASE_LINK Procedure for more information.

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

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.

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

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

Youtube

youtube

443 Youtube 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.