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

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

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
   sharepoint	Microsoft SharePoint
   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. 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.
   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 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: Create a directory on the Autonomous 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.

   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

BEGIN
    DBMS_CLOUD.UPDATE_CREDENTIAL (
       credential_name => 'AWS_REDSHIFT_LINK_CRED',
       attribute => 'PASSWORD',
       value => 'password');
END;
/

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