Create Database Links to Non-Oracle Databases with Customer-Managed Heterogeneous Connectivity

Use DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK to create database links from an Autonomous Database to an Oracle Database Gateway to access Non-Oracle databases.

An Oracle Database Gateway is a gateway that is designed for accessing a specific non-Oracle system. Using an Oracle Database Gateway, you can access data anywhere in a distributed database system without knowing either the location of the data or how it is stored. Using database links on Autonomous Database with Oracle Database Gateway supports heterogeneous environments and eliminates the need to customize your applications to access data from non-Oracle systems.

To use database links with Autonomous Database, the target gateway must be configured to use TCP/IP with SSL (TCPS) authentication. See Configuring Secure Sockets Layer Authentication for more information.

To ensure security when using database links with an Oracle Database Gateway, the database link port is restricted to the range 1521-1525, or port 2484. You specify the target gateway port when you create a database link with DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.

Note:

Before you create database links to a target gateway, do the following:

Configure the Oracle Database Gateway to access a non-Oracle database.
Configure Oracle Net Listener to handle incoming requests on the Oracle Database Gateway.
Create a self signed wallet on the Oracle Database Gateway.

For more information on Oracle Database Gateway, see Oracle Database Gateways in Oracle Database Heterogeneous Connectivity User's Guide. Also see the Installation and Configuration Guide and the Gateway User's Guide for the database you want to connect to. For example, for Oracle Database Gateway for SQL Server see:

Installing and Configuring Oracle Database Gateway for SQL Server
Introduction to the Oracle Database Gateway for SQL Server in Oracle Database Gateway for SQL Server User's Guide.
Configure Oracle Net for the Gateway

To create database links to a target gateway, do the following:

Copy the target gateway self signed wallet, for example, cwallet.sso, containing the certificates for the Oracle Database Gateway to Object Store.

Note:
The wallet file, along with the Database user ID and password provide access to data available through the target gateway. Store wallet files in a secure location. Share wallet files only with authorized users.
Create credentials to access the Object Store where you store the cwallet.sso. See CREATE_CREDENTIAL Procedure for information about the username and password parameters for different object storage services.
Create a directory on Autonomous Database for the wallet file cwallet.sso.
For example:
```
CREATE DIRECTORY dblink_wallet_dir AS 'directory_path_of_your_choice';
```
See Create Directory in Autonomous Database for information on creating directories.
Use DBMS_CLOUD.GET_OBJECT to upload the target gateway self signed wallet to the directory you created in the previous step, DBLINK_WALLET_DIR.
For example:
```
BEGIN 
     DBMS_CLOUD.GET_OBJECT(
        credential_name => 'DEF_CRED_NAME',
        object_uri => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/cwallet.sso',
        directory_name => 'DBLINK_WALLET_DIR'); 
END;
/
```
In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

Note:
The credential_name you use in this step is the credentials for the Object Store. In the next step you create the credentials to access the target gateway.
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, (where the target database is accessed through the Oracle Database Gateway).

Note:
Supplying the credential_name parameter is required.
For example:
```
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DB_LINK_CRED',
    username => 'NICK',
    password => 'password'
  );
END;
/
```
The characters in the username parameter must be all uppercase letters.

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 target gateway using DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.

For example:

BEGIN
     DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name => 'SALESLINK', 
          hostname => 'example.com', 
          port => '1522',
          service_name => 'example_service_name',
          ssl_server_cert_dn => 'ssl_server_cert_dn',
          credential_name => 'DB_LINK_CRED',
          directory_name => 'DBLINK_WALLET_DIR',
          gateway_link => TRUE);
END;
/

Users other than ADMIN require privileges to run DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.

Use the database link you created to access data on the target gateway.
For example:
```
SELECT * FROM employees@SALESLINK;
```

Note:

For the credentials you create in Step 5, the Oracle Database Gateway 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 => 'DB_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.

See the following for an example that shows you how to create a database link to an Oracle Database Gateway to access a Microsoft SQL Server database:

How to Access Non-Oracle Databases from Autonomous Database using Oracle Database Gateway

For additional information, see: