5.1 Using Data Workshop
Use Data Workshop to load and unload data to the database.
- About the Load Data and Unload Data Wizards
Use the Load Data and Unload Data wizards to load and unload data to and from the database. - About Importing, Exporting, Loading, and Unloading Data
You have several options when copying data between Oracle databases or between an Oracle database and external files. Data copying is accomplished by exporting and importing data, and by unloading and loading data. - Import/Export/Load/Unload Options
The Oracle Database and Oracle Application Express provide several powerful options for importing, exporting, loading, and unloading data. - Accessing the Load Data/Unload Data Page
Access the Load Data and Unload Data page by clicking Utilities and then Data Workshop. - Loading Data
Use the Load Data Wizard to copy and paste or upload data. - Unloading Data
Use the Unload Data page to export the contents of a table to a text file or XML document. - Loading Data From the Cloud (Autonomous Data Warehouse Only)
Load data from the cloud using Object Store References. (This function is only available for Autonomous Database users.)
Parent topic: Using Oracle Application Express Utilities
5.1.1 About the Load Data and Unload Data Wizards
Use the Load Data and Unload Data wizards to load and unload data to and from the database.
These step-by-step wizards include the following features:
-
Drag and drop delimited files (CSV, tab-delimited), XLSX files (Excel workbooks), JSON or XML files to load data into a new table.
-
Copy and paste delimited data (CSV, tab-delimited), or select a sample data set to load data into a new table.
-
Data load parser detects almost everything automatically so there is no need to manually pick data types, or provide format masks or decimal characters.
-
Data Loading runs in the background which offers a number of advantages. If a larger file is being uploaded, the dialog can be dismissed. If bad rows cannot be inserted into the target table, the whole load is not lost. All failed rows are saved in error table and can be post-processed manually.
- With Oracle Database 12c or later, you can choose whether the Primary Key column
will be generated using the Identity Column feature or whether a globally unique
identifier (
SYS_GUID
) shall be used. The difference is values and space requirements. Identity Column values are like sequences in that they start with 1 and increase.SYS_GUID
values are globally unique, hard to predict, but require more space than the compact Identity Column numbers. -
The main dialog displays up to 10 columns and 5 rows in the preview. You can configure load settings such as column headers, file encoding, column delimiter / enclosed by (for CSV or TXT), and Select Sheet (for XLSX with multiple worksheets).
-
You can view a full preview and change more settings by clicking the Configure button. The Configure dialog displays a maximum of 100 columns and 100 rows. Users can configure the same setting as settings in the main dialog plus maximum Rows to Sample and columns to load/unload.
-
Settings changes will change the preview both in main and configure dialogs. They also affect data load result.
Note:
If file size is greater than (>) or equal (=) to 2097152 bytes and you select to sample All Rows, the following confirmation message displays:Sampling all rows can take a while for larger files. Do you really want to sample the whole file?
-
Select table owner, enter table name and error table name.
Note:
By default, all table names are converted to upper case. If data load succeeds without any error, the error table is dropped automatically. -
The wizards load and unload table data only. They do not load or unload other kinds of schema objects.
-
You can load and unload to and from your own schema only. This is also true for users with administrator privileges.
-
You can load or unload only a single table at a time.
Supported unload formats include:
-
Text such as comma-delimited or tab-delimited data in dialog page.
-
XML documents
See Also:
Parent topic: Using Data Workshop
5.1.2 About Importing, Exporting, Loading, and Unloading Data
You have several options when copying data between Oracle databases or between an Oracle database and external files. Data copying is accomplished by exporting and importing data, and by unloading and loading data.
The following table defines these terms.
Term | Definition |
---|---|
Exporting |
Copying database data to external files for import into another Oracle database only. The files are in a proprietary binary format. |
Importing |
Copying data into the database from external files that were created by exporting from another Oracle database. |
Unloading |
Copying database data to external text files for consumption by another Oracle database or another application (such as a spreadsheet application). The text files are in an industry-standard format such as tab-delimited or comma-delimited ( |
Loading |
Copying data into the database from external text files that are in either a standard delimited format or in any of the formats that are supported by the Oracle SQL*Loader utility. |
You can export data from any Oracle Database edition (Express Edition, Standard Edition, and Enterprise Edition) into any other edition.
Parent topic: Using Data Workshop
5.1.3 Import/Export/Load/Unload Options
The Oracle Database and Oracle Application Express provide several powerful options for importing, exporting, loading, and unloading data.
Table 5-1 provides a summary of these options.
Table 5-1 Summary of Oracle Application Express Import/Export Options
Feature or Utility | Description |
---|---|
Load Data /Unload Data wizards in Oracle Application Express |
|
SQL*Loader utility |
|
Data Pump Export and Data Pump Import utilities |
|
Export and Import utilities |
|
Table 5-2 provides several load, unload, import, and export scenarios and suggests the appropriate option to use for each.
Table 5-2 Import/Export Scenarios and Recommended Options
Import/Export Scenario | Recommended Option |
---|---|
You have fewer than 10 tables to load, the data is in spreadsheets or tab-delimited or comma-delimited text files, and there are no complex data types (such as objects or multivalued fields). |
Load Data /Unload Data wizards in Oracle Application Express |
You have to load data that is not delimited. The records are fixed length, and field definitions depend on column positions. |
SQL*Loader |
You have tab-delimited text data to load, and there are more than 10 tables. |
SQL*Loader |
You have text data to load, and you want to load only records that meet certain selection criteria (for example, only records for employees in department number 3001). |
SQL*Loader |
You want to import or export an entire schema from or to another Oracle database. |
Data Pump Export and Data Pump Import |
You want to import or export data from or to another Oracle database. The data contains XMLType data and contains no |
Import ( |
See Also:
"Oracle Database Utilities" for more information on Data Pump, the Import and Export utilities, and SQL*Loader
Parent topic: Using Data Workshop
5.1.4 Accessing the Load Data/Unload Data Page
Access the Load Data and Unload Data page by clicking Utilities and then Data Workshop.
To access the Load Data/Unload Data page:
Parent topic: Using Data Workshop
5.1.5 Loading Data
Use the Load Data Wizard to copy and paste or upload data.
- About Loading Data
Load data into the Oracle Application Express by copying and pasting CSV data, selecting a sample data set, or uploading a file. - Copying and Pasting CSV data or Selecting a Sample Data Set
You can copy and paste CSV data, or select a sample data set sample into the Load Data Wizard. - Loading an XML Document
Load an XML document using the Load Data Wizard. - Loading a JSON File
Load a JSON file using Load Data Wizard. - Loading a XLSX File
Load a XLSX file using Load Data Wizard.
See Also:
Parent topic: Using Data Workshop
5.1.5.1 About Loading Data
Load data into the Oracle Application Express by copying and pasting CSV data, selecting a sample data set, or uploading a file.
Key capabilities when loading data into the Oracle Application Express include:
-
Copy and paste CSV data, or select a sample data set.
-
Upload a file (supports CSV, XLSX, TXT, XML or JSON files).
Note:
Configure load settings such as column headers, file encoding, column delimiter / enclosed by (for CSV or TXT), and Select Sheet (for XLSX with multiple worksheets).
-
View full preview and change more settings by clicking Configure button. Configure dialog displays maximum of 100 columns and 100 rows. You can configure same setting as settings in the main dialog plus maximum Rows to Sample and columns to load/unload.
-
In Configure dialog, under Columns to Load tab, you can enter case insensitive value that will be treated as
NULL
. For example,"tbd"
,"n/a"
,"unknown"
,"-"
. -
Post loading, if any rows fail, up to 10 rejected rows display. To view more rows which have error, click Open in Object Browser button. You can optionally click Drop Table and Reload button to try the data load again.
-
Settings change will change preview both in main and configure dialogs. They also affect data load result.
-
Select table owner, enter table name and error table name
Note:
By default, all table names are converted to upper case. If data load succeeds without any error, the error table is dropped automatically. -
Post loading, a View Table button and Continue to Create Application Wizard button display if there are no failed rows. The load is successful if ta table is created and displayed.
Parent topic: Loading Data
5.1.5.2 Copying and Pasting CSV data or Selecting a Sample Data Set
You can copy and paste CSV data, or select a sample data set sample into the Load Data Wizard.
To load data by copying and pasting CSV data or selecting a sample data set:
Parent topic: Loading Data
5.1.5.3 Loading an XML Document
Load an XML document using the Load Data Wizard.
XML files can also be uploaded to Oracle Application Express. However, Oracle Application Express supports only "flat" XML structures which can easily be mapped to a table and columns. XML structures with multiple levels of nesting cannot be loaded by the Data Loading wizard.
To load an XML document:
Parent topic: Loading Data
5.1.5.4 Loading a JSON File
Load a JSON file using Load Data Wizard.
Loading JSON files works similar to loading XML files.
Note:
Only flat structures are supported - deeply nested JSON structures cannot be loaded.
To load a JSON file:
Parent topic: Loading Data
5.1.5.5 Loading a XLSX File
Load a XLSX file using Load Data Wizard.
Loading XLSX files is similar to loading CSV files. There is no need to detect or to choose delimiter or enclosing characters.
To load a XLSX file:
Parent topic: Loading Data
5.1.6 Unloading Data
Use the Unload Data page to export the contents of a table to a text file or XML document.
- Unloading a Text File
Use the Unload Data wizard to export the contents of a table to a text file. - Unloading to an XML Document
Use the Unload Data Wizard to export the contents of a table to an XML document adhering to the Canonical XML specification.
See Also:
Parent topic: Using Data Workshop
5.1.6.1 Unloading a Text File
Use the Unload Data wizard to export the contents of a table to a text file.
For example, you could export an entire table to a comma-delimited file (.csv).
To unload a table to a text file:
Parent topic: Unloading Data
5.1.6.2 Unloading to an XML Document
Use the Unload Data Wizard to export the contents of a table to an XML document adhering to the Canonical XML specification.
To unload a table to an XML document:
Parent topic: Unloading Data