Load Data from Oracle and Non-Oracle Databases using Database Links

This section of the document describes how to create database links and then transfer data from various non-Oracle databases into your Autonomous Database using Data Studio.

To load data from Oracle and Non-Oracle Databases using Database Links, you must first create a Database Link.

Run the following steps to load data from Oracle databases, or non-Oracle databases into Autonomous Database using Database Links:

Create Credentials and Database Links to access the Autonomous Database.

You will use the DBMS_CLOUD.CREATE_CREDENTIAL procedure to create credentials where the username and password values you specify are the credentials for the target database.

The DBMS_CLOUD.CREATE_CREDENTIAL procedure stores the credentials in an encrypted format.

After you create credentials, you will create database links to load data in Data Studio from non-Oracle Databases using the Data Load tool.

Following are the sample codes you will run to create credentials and then use the same credentials to create the database links:

Create Database Links from MySQL:

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'MYSQLPE_CRED',
    username => 'admin',
    password => <enter password here> );

  DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
    db_link_name => 'MYSQLPE_DBLINK',
    hostname => 'scottmysql.scottprivate.com',
    port => '3306',
    service_name => 'scott',
    ssl_server_cert_dn => NULL,
    credential_name => 'MYSQLPE_CRED',
    private_target => TRUE,
    gateway_params => JSON_OBJECT('db_type' value 'MYSQL'));
END;
/

Create Database Links from Microsoft Azure:

BEGIN 
  DBMS_CLOUD.CREATE_CREDENTIAL( 
    credential_name => 'AZURE_CRED', 
    username => 'oracle', 
    password => <enter password here> );

  DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
    db_link_name => 'AZURE_DBLINK',
    hostname => 'scott-server.database.windows.net',
    port => '1433',
    service_name => 'scottdb',
    ssl_server_cert_dn => NULL,
    credential_name => 'AZURE_CRED',
    gateway_params => JSON_OBJECT('db_type' value 'AZURE'));
END;
/

Create Database Links from Snowflake:

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'SNOWFLAKE_CRED',
    username => 'SCOTT',
    password => <enter password here> ); 

  DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
    db_link_name => 'SNOWFLAKE_DBLINK',
    hostname => 'https://abcdefg-hij12345.snowflakecomputing.com',
    port => '443',
    service_name => 'SCOTTDB',
    credential_name => 'SNOWFLAKE_CRED',
    gateway_params => JSON_OBJECT('db_type' value 'SNOWFLAKE'),
    directory_name => NULL,
    ssl_server_cert_dn => NULL);
END;
/

Create Database Links from Oracle Cloud Object Storage using Native OCI Credentials

BEGIN
  DBMS_CLOUD.GET_OBJECT(
    object_uri => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/abcdefgh12ij/b/scott-bucket/o/cwallet.sso',
    credential_name => 'OCI_NATIVE_CRED',
    directory_name => 'REG_WALLET_DIR');

  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'ADBS_CRED',
    username => 'ADMIN',
    password => <enter password here> );

  DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
    db_link_name => 'ADBS_DBLINK',
    hostname => 'adb.us-ashburn-1.oraclecloud.com',
    port => '1522',
    service_name => 'ab1cdefgh2i3jkl_scottadbs_high.adb.oraclecloud.com',
    credential_name => 'ADBS_CRED',
    directory_name => 'REG_WALLET_DIR');
END;
/

For more details on the parameters and their values, see the Create_Database_Link procedure.

Note:

You require ADMIN privileges to run the DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK procedure.
You can use a vault secret credential for the target database credential in a database link. See Use Vault Secret Credentials for more information.

1. Use the Data Load tool to LOAD data from Oracle and non-Oracle Databases:
  You can use the database link you created to load access data on Data Studio. After you create the database links, you can view and use the list of database links you created in the Data Load tool and access data from Oracle and non-Oracle databases.
  - Click Data Load under the Data Studio suite of tools and select the Load Data card.
  - Click the Database tab and select the database link from the list of available database links you created. In this example, we will use AZURE_DBLINK.
    
    Description of the illustration azure-dblink.png
    
    Note:
    You cannot view the database link you create from MySQL since it resides within a Virtual Cloud Network (VCN), and the Autonomous Database would need to be either located in or connected to the Private Subnet within that VCN.
  - Drag the sample table from the database and drop it in the data load cart to load the data in your autonomous database.
    
    Description of the illustration drag-drop-sample.png
  - Click Start to start the loading process.
  - After the table is loaded in the tool, you can view the result in the Data Load Dashboard under the Table and View Loads section.
    
    Description of the illustration sample-table-load.png
2. Use the Data Load tool to LINK data from Oracle and non-Oracle Databases:
  Alternatively, you can use the Data Load to link data that creates a view of the data by directly accessing cloud databases via the Database Link.
  - From the Data Load menu under Data Studio, select Link Data.
  - Click the Database tab. In this example, we will use the ADBS_DBLINK link.
  - Drag the customer table from the database and drop it in the data link cart to link the data in your autonomous database.
    
    Description of the illustration drag-drop-customer.png
  - Click Start to start linking the data in Data Studio.
  - After the data is linked, you will view the results of the data link job in the Data Load dashboard under the Table and View Loads section.
    
    Description of the illustration customer-table-load.png

You have successfully loaded the data from Oracle and non-Oracle databases using Database Links in Data Studio.