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.
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:
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 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.
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:
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 Database Gateway for ODBC User's Guide
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.
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 |
|
5439 |
Amazon Redshift Supported Versions |
Azure SQL Microsoft SQL Server Azure Synapse Analytics |
|
1433 |
|
Apache Hive |
hive |
443 |
Hive Supported Versions |
Google Analytics |
|
443 |
|
Google BigQuery |
|
443 |
|
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 |
|
MySQL |
|
3306 |
MySQL Supported Versions |
MySQL Community Edition |
|
3306 |
|
PostgreSQL |
postgres |
5432 |
PostgreSQL Supported Versions |
Salesforce |
|
19937 |
Salesforce Supported Versions |
ServiceNow |
|
443 |
ServiceNow Supported Versions |
Snowflake |
|
443 |
Snowflake Supported Versions |
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
TheHETEROGENEOUS_CONNECTIVITY_INFO
view lists connectivity information and examples for connecting with PL/SQL using database links and 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
|
REQUIRED_PORT |
NUMBER |
Supported port values for the database type. |
DESCRIPTION |
CLOB |
Specifies a description for the
|
OPTIONAL_PARAMETERS |
VARCHAR2(1024) |
Specifies the valid optional parameters for the
|
SAMPLE_USAGE |
CLOB |
Shows sample PL/SQL usage for the |