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.
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.
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;
/
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_paramsdb_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.
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.
When the db_type value is
google_analytics the hostname is not used
and you can provide value such as example.com.
For db_typegoogle_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_typegoogle_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_typesalesforce, you must supply the
security_token option. The security token is a
case-sensitive alphanumeric code.
When you use gateway_params parameter with
db_typesalesforce, 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_typeservicenow 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_typeservicenow, 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:
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_typesharepoint, also specify values for
auth_uri, scope,
service_url, and token_uri.
For db_typesharepoint, 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_typesharepoint:
When you use gateway_params parameter with
db_typesnowflake, 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.
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_typeyoutube, 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:
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.
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.
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:
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.
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
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.
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.
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.
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.