Lists the prerequisites to create database links from an Autonomous Database with Customer-Managed Heterogeneous Connectivity to Non-Oracle Databases that are on a Private Endpoint.

You can create database links from an Autonomous Database instance to an Oracle Database Gateway to access Non-Oracle databases that are on a private endpoint, connecting either with a wallet (TCPS) or without a wallet (TLS).

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, (where the target database is accessed through the Oracle Database Gateway).

   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.

2. 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 =>      NULL,
             gateway_link =>        TRUE,
             private_target =>      TRUE,
             gateway_params =>      NULL);
   END;
   /

   The ssl_server_cert_dn parameter is optional if the connection is created as a TCP based database link (without a wallet).

   When the directory_name is NULL, the connection is created as a TCP based database link (without a wallet).

   The private_target parameter must be set to TRUE when the target non-Oracle Database is on a private endpoint (that is, the database link accesses a hostname that needs to be resolved in a VCN DNS server). When private_target is TRUE, the hostname parameter must be a single hostname (on a private endpoint, using an IP address, a SCAN IP, or a SCAN hostname is not supported).

   When gateway_link is TRUE and gateway_params is NULL, this specifies that the database link is to a customer-managed Oracle gateway.

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

3. Use the database link you created to access data on the target gateway.

   For example:

   SELECT * FROM employees@SALESLINK;
   

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

You can create database links from an Autonomous Database instance to an Oracle Database Gateway to access Non-Oracle databases that are on a private endpoint, connecting either with a wallet (TCPS) or without a wallet (TLS).

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

   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.

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

4. 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.
5. 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).

   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.

6. 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,
             private_target =>      TRUE,
             gateway_params =>      NULL);
   END;
   /

   If directory_name is not NULL, a TCPS-based database link is created.

   The private_target parameter must be set to TRUE when the target non-Oracle Database is on a private endpoint (that is, the database link accesses a hostname that needs to be resolved in a VCN DNS server). When private_target is TRUE, the hostname parameter must be a single hostname (on a private endpoint, using an IP address, a SCAN IP, or a SCAN hostname is not supported).

   When gateway_link is TRUE and gateway_params is NULL, this specifies that the database link is to a customer-managed Oracle gateway.

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

7. Use the database link you created to access data on the target gateway.

   For example:

   SELECT * FROM employees@SALESLINK;
   

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