Loading Data from Other Databases

To load data from tables in another database into your Oracle Autonomous Database, on the Data Load page, select LOAD DATA and DATABASE, then click Next. Select a database link from the drop-down list. Drag one or more tables from the list of database tables and drop them in the Data Load Cart.

Each table appears as an item in the Data Load Cart. The item shows the name of the table and the number of rows in it, and the name of the table that is the target for the data load.

To remove a table from the Data Load Cart, click the Remove (trash can) icon for the item. To remove all tables from the cart, click the Remove All (trash can) icon in the Data Load Cart menu bar.

To add a remote database to the list of database links, create a database link to the remote database. For information on creating a database link, see Database Links in Oracle® Database Database Administrator’s Guide 21c.

The databases available to you appear in the drop-down list of the database navigation pane of the Load Tables page.

You can filter the tables displayed in the navigation pane by entering a case-sensitive value in the search field at the top of the navigation tree and pressing Enter. To display all of the tables again, clear the search field and press Enter.

You can add any number of tables from the navigation pane to the Data Load Cart and load data from all of them in a single data loading job. You can set filters on the data for a table to load only the specified data.

Specify Processing Options

To specify settings for the data load job, preview the data in the source or the target, and see statistics about the data, click the Settings (pencil) icon for the item in the Data Load Cart.

In the settings pane, on the Settings tab, you can view the name and size of the file in the title of the Load Data dialog box.

The Table field specifies the name of the target table. The value in the field varies depending on the selection in the Options field. If the option is Create Table, then the default target value is the name of source table. To specify a different name for the target, enter it in the Name field. For the other options, the default value is <None>. Expand the drop-down list and select a table as the target.

In the Options field for the source, select Create Table, Insert into Table, Replace Data, Drop Table and Create New Table, or Merge into Table. Point to the question mark icon to see a brief description of the selected option.

If you select Create Table, then in the Name field accept the default name, which is the name of the source table, or enter a different name.

If you select one of the other options, then expand the drop-down list of the Name field and select a table as the target.

You can set filters to load only the specified data from the source table. To set an initial filter condition on a table, do the following:

1. Click the Edit Source Filter icon.
2. In the Edit Source Filter dialog box, click Add Filter Condition.
3. In the Add Filter Condition dialog box, select a source column and an operator and specify a value.
4. Click Save.

To add another filter condition, click the Edit Source Filter icon and repeat the steps for adding a filter. The filter then has both filter conditions and the AND operator. You can change the AND to an OR by clicking the Edit Source Filter icon and manually replacing the AND with OR in the Filter field.

To remove a filter, in the Edit Source Filter dialog box, delete the value in the Filter field of the Edit Source Filter dialog box.

Bulk Edit Settings

You can use the Bulk edit settings to update all the columns at once from the mapping table. Use it to apply changes to the selection currently displayed in the results pane. You can search for the values of the column you want to edit in the search field and click the magnifier icon. The mapping table will display the results of the search. Select the Bulk Edit setting to update the column. The Bulk Edit setting allows you to:

• Update values of all the fields in a group.
• Find and replace, Add Prefix and Add suffix to target column name.
• Include the column(s) for loading data to the target table.
• Exclude the column(s) for loading data to the target table.

Searching the Column

The Bulk Edit setting updates the columns returned by the search field. The search box besides the Bulk Edit setting icon filters the list of columns you wish to update in a bulk. As soon as you start typing in the search field, the tool returns the field values which contains the letters you type. You can remove the filter by deleting all the content from the search box and clicking the magnifier icon that appears next to the search box.

The Bulk Edit setting enables you to update the values of the following columns for all the searches returned by the search field:

• Data Type
• Target Column name
• Include Columns for loading
• Exclude columns for loading

Consider changing the Data Type of first and third row from VARCHAR to NUMBER in the mapping table.
Description of the illustration bulk-edit1.png

• Type VARCHAR in the search field and click on the magnifier icon besides the search field.
  Description of the illustration bulk-edit2.png

• The search will return the rows having VARCHAR as the Data Type. Select the Bulk Edit icon besides the magnifier icon.
  Description of the illustration bulk-edit3.png

• Selecting the Bulk Edit icon opens the Bulk Edit Mapping dialog box.
  Description of the illustration bulk-edit4.png

• Click Data Type and select NUMBER from the Data Type drop-down. Click OK.
  Description of the illustration bulk-edit5.png

• The Data Type of the selected two rows changes from VARCHAR to NUMBER.
  Description of the illustration bulk-edit6.png

• Clear the content from the search field and click the magnifier icon to view the bulk edit updates in the Mapping table.
  Description of the illustration bulk-edit7.png

Specify Mappings

If you select the Create Table or the Drop Table and Create New Table option, then in the Mapping section either accept the default values for the target columns or specify different values. For the target column, enter a name for the column.

For the Insert into Table or Replace Data options, select a target column from the drop-down list of existing columns.

For the Merge into Table option, for each source column, select a target column form the drop-down list. You must specify at least one column as a key column. To specify a column as a key column, select the Merge Key check box for the column. Merge keys are one or more columns that uniquely identify each row in the table. Merge keys must not contain any null values. For loading tables with primary keys, this option automatically enables the selection of primary key columns as the merge keys.

Preview Source Data

To view the data in the source table, in the settings pane select the Source Table tab. The source preview displays the data in the table.

View Statistics

To view statistics about the source table, in the settings pane select the Source Statistics tab. It may take a moment for the statistics to appear. The statistics include the size of the table, the number of rows and columns, the column names, data types, number of distinct values, and other information. Below the details about the columns is a bar graph that displays the top unique values for the selected column.

Preview Target Data

For all options except Create Table, to view the existing data in the target table, in the settings pane select the Target Table tab. The target preview displays the data in the table before you run the data load job.

Run the Data Load Job

When you have added all of the source tables for the job and specified the settings for each table, to run the job click the Start icon in the Data Load Cart menu bar. In the Run Data Load Job dialog box, click Run. To stop the data load job, click the Stop icon.

At the top of the page, the Status shows the number of items for which the load has completed over the number of items in the job, and the total time elapsed for the job. When the data load job completes, the Load Tables page displays the results of the job.

To view information about an item in the job, click the Settings icon in the item. The settings pane has the same Settings, Source Table, Source StatisticsTarget Table, SQL and Errors tabs as the settings pane before running the job, except that the target preview now contains the data loaded by the data load job. To close the settings pane, click Close.

To view a log of the load operation, click the Log icon. You can save the log, clear it, or refresh it. Click OK to dismiss the log.

The list of tables on the Data Load / Explore page contains any new tables created. The target tables for the Insert into Table, Replace Data, Drop Table and Create New Table, and Merge into Table options contain the loaded data.

Click the Explore Catalog button on the Local files page to view the new or updated table on the Catalog page.

Fixing a data load job

After your data load job, you might see errors that you want to correct, or upon inspection, realize that you wanted to name a column differently. In such cases, click the Reload Cart option to reload cards from your recent cart and edit them as you did before your first attempt. Click the pencil icon to make any changes to the data load job (i.e., change a column name).

Click Done to return to the Database Actions page.