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

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:

"Import/Export/Load/Unload Options"

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 (CSV).
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.

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	Easy to use graphical interface Supports CSV, XLSX, TXT, XML or JSON files) or Copy and Paste (supports ONLY comma or tab delimited data) Loads/unloads tables only, one table at a time Access only to schema of logged-in user No data filtering on upload
SQL*Loader utility	Command-line interface, invoked with sqlldr command Bulk-loads data into the database from external files Supports numerous input formats, including delimited, fixed record, variable record, and stream Loads multiple tables simultaneously Powerful data filtering capabilities
Data Pump Export and Data Pump Import utilities	Command-line interface, invoked with expdp and impdp commands Exports and imports from one Oracle database to another (proprietary binary format) Imports/exports all schema object types Imports/exports entire database, entire schema, multiple schemas, multiple tablespaces, or multiple tables Powerful data filtering capabilities High speed
Export and Import utilities	Command-line interface, invoked with exp and imp commands Exports and imports from one Oracle database to another (proprietary binary format) Supports XMLType data Does not support the FLOAT and DOUBLE data types Capabilities similar to Data Pump; Data Pump is preferred

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 FLOAT or DOUBLE data types.	Import (imp) and Export (exp)

See Also:

"Oracle Database Utilities" for more information on Data Pump, the Import and Export utilities, and SQL*Loader

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:

1. On the Workspace home page, click the SQL Workshop icon.
2. Click Utilities.
3. Click Data Workshop.

   The Data workshop page appears.

4. Click the appropriate icon to load data and unload data.

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:

"Import/Export/Load/Unload Options"

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.

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:

1. On the Workspace home page, click the SQL Workshop icon.
2. Click Utilities.
3. Click Data Workshop.
4. Under Get Started, click Load Data.
   The Load Data Wizard appears.
5. Click Copy and Paste.
   Under Copy and Paste, you can either copy and paste CSV data or select a sample data set from the drop down menu.
6. Click Next.
7. Under Load to Table, edit the following:
   1. Table Owner - Select the database schema you would like to create and load data into.
   2. Table Name - Identify the table name you would like to create. By default, all table names are converted to upper case.
   3. Error Table Name - Identify the error table name you would like to save errors during data load.

      Note:

      By default, all table names are converted to upper case. If data load succeeds without any error, this error table is dropped automatically.
   4. Primary Keys - Select how to set the primary key (Oracle Database 12c or later). Primary keys can be set using SYS_GUID or Identity Column.
      SYS_GUID generates and returns a globally unique identifier. On most platforms, this generated identifier is based on a host identifier, a process or thread identifier of the process or thread invoking the function, and a non-repeating value (sequence of bytes). SYS_GUID consumes more space, but is unpredictable. Identity Column is compact, but can be predictable
   5. Use Column Data Types - Select to use the column types parsed from the file. If not selected, the table columns will be created as VARCHAR2(4000).
8. To view the full preview, configure data load settings, and set which columns to load, Click Configure.
   Make the appropriate changes and click Save Changes.
9. Click Load 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:

1. On the Workspace home page, click the SQL Workshop icon.
2. Click Utilities.
3. Click Data Workshop.
4. Click Load Data.
5. Under Upload a File, Click Choose File.
6. Select the appropriate XML file .

   Note:

   There are no delimiter or enclosing characters to choose for XML file load. Also the column names are derived from the XML tag names, so there is no First lines contains headers checkbox. XML files are limited to 10MB each.
7. Under Load to Table, edit the following:
   1. Table Owner - Select the database schema you would like to create and load data into.
   2. Table Name - Identify the table name you would like to create. By default, all table names are converted to upper case.
   3. Error Table Name - Identify the error table name you would like to save errors during data load.

      Note:

      By default, all table names are converted to upper case. If data load succeeds without any error, this error table is dropped automatically.
   4. Primary Keys - Select how to set the primary key (Oracle Database 12c or later). Primary keys can be set using SYS_GUID or Identity Column.
      SYS_GUID generates and returns a globally unique identifier. On most platforms, this generated identifier is based on a host identifier, a process or thread identifier of the process or thread invoking the function, and a non-repeating value (sequence of bytes). SYS_GUID consumes more space, but is unpredictable. Identity Column is compact, but can be predictable
   5. Use Column Data Types - Select to use the column types parsed from the file. If not selected, the table columns will be created as VARCHAR2(4000).
8. To view the full preview, configure data load settings, and set which columns to load, Click Configure.
   Make the appropriate changes and click Save Changes.
9. Click Load Data.

   Once Data Loading is finished, a success message displays. If some rows cannot be loaded to the target table, they will be stored to the error table and error table is displayed.

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:

1. On the Workspace home page, click the SQL Workshop icon.
2. Click Utilities.
3. Click Data Workshop.
4. Click Load Data.
5. Under Upload a File, Click Choose File.
6. Select the appropriate JSON file .

   Note:

   JSON files are limited to 20MB on 11g and 12.1 databases. For Oracle Database 12.2 or higher, there is no size limit for JSON files.

7. Under Load to Table, edit the following:
   1. Table Owner - Select the database schema you would like to create and load data into.
   2. Table Name - Identify the table name you would like to create. By default, all table names are converted to upper case.
   3. Error Table Name - Identify the error table name you would like to save errors during data load.

      Note:

      By default, all table names are converted to upper case. If data load succeeds without any error, this error table is dropped automatically.
   4. Primary Keys - Select how to set the primary key (Oracle Database 12c or later). Primary keys can be set using SYS_GUID or Identity Column.
      SYS_GUID generates and returns a globally unique identifier. On most platforms, this generated identifier is based on a host identifier, a process or thread identifier of the process or thread invoking the function, and a non-repeating value (sequence of bytes). SYS_GUID consumes more space, but is unpredictable. Identity Column is compact, but can be predictable
   5. Use Column Data Types - Select to use the column types parsed from the file. If not selected, the table columns will be created as VARCHAR2(4000).
8. To view the full preview, configure data load settings, and set which columns to load, Click Configure.
   Make the appropriate changes and click Save Changes.
9. Click Load Data.

   Once Data Loading is finished, a success message displays. If some rows cannot be loaded to the target table, they will be stored to the error table and error table is displayed.

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:

1. On the Workspace home page, click the SQL Workshop icon.
2. Click Utilities.
3. Click Data Workshop.
4. Click Load Data.
5. Under Upload a File, Click Choose File.
6. Select the appropriate XLSX file .

   Note:

   If the uploaded XLSX file contains multiple worksheets, Load Data wizard picks the first sheet by default. To load another sheet, pick it from the Select Sheet select list. The First lines contains headers checkbox works similar to CSV files. The same is true for the Configure dialog, the behavior is the same as it is for CSV files. Uploading XLSX files is limited to 20MB for each file.
7. Under Load to Table, edit the following:
   1. Table Owner - Select the database schema you would like to create and load data into.
   2. Table Name - Identify the table name you would like to create. By default, all table names are converted to upper case.
   3. Error Table Name - Identify the error table name you would like to save errors during data load.

      Note:

      By default, all table names are converted to upper case. If data load succeeds without any error, this error table is dropped automatically.
   4. Primary Keys - Select how to set the primary key (Oracle Database 12c or later). Primary keys can be set using SYS_GUID or Identity Column.
      SYS_GUID generates and returns a globally unique identifier. On most platforms, this generated identifier is based on a host identifier, a process or thread identifier of the process or thread invoking the function, and a non-repeating value (sequence of bytes). SYS_GUID consumes more space, but is unpredictable. Identity Column is compact, but can be predictable
   5. Use Column Data Types - Select to use the column types parsed from the file. If not selected, the table columns will be created as VARCHAR2(4000).
8. To view the full preview, configure data load settings, and set which columns to load, Click Configure.
   Make appropriate changes and click Save Changes.
9. Click Load Data.

   Once Data Loading is finished, a success message is displayed. If some rows cannot be loaded to the target table, they will be stored to the error table and error table is displayed.

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:

"Import/Export/Load/Unload Options"

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:

1. On the Workspace home page, click the SQL Workshop icon.
2. Click Utilities.
3. Click Data Workshop.
4. Under Get Started, click Unload Data.

   The Unload Data Wizard appears.

5. Select Text Format and click Next.

   The Unload to Text - Columns appears.

6. On Unload Text - Columns, edit the following:
   1. Schema - Select the database schema that owns the object you would like to export.
   2. Table - Select the database table you would like export to a plain text format.
   3. Columns - Highlight the columns you would like to be part of this plain text file. Only Oracle built in data types except BLOB and BFILE are supported to unload.
   4. Where Clause - Enter SQL WHERE clause to limit the rows that are selected. For example: DEPTNO = 10
   5. Click Next.
7. For Options, enter the following:
   1. Separator - Specify the type of separator used to separate the column values on each line. The default value is a comma (,). To use a tab as a column separator, enter a backslash followed by the letter "t" ().
   2. Optionally Enclosed By - Use this option to specify how to identify text strings in a column. You can specify single or double quotation marks.
   3. Include Column Names - Check this box to include the name of each column in the text file.
   4. File Format - Select DOS to have the lines in the resulting file terminated by carriage returns and line feeds. Select UNIX to have the lines in the resulting file contain terminated by line feeds.
   5. File Character Set - Select the character set to encode the export file.
8. Click Unload Data.

   The file is downloaded.

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:

1. On the Workspace home page, click the Utilities icon.
2. Click Data Workshop.
3. Under Get Started, click Unload Data.

   The Unload Data Wizard appears.

4. Select XML Format and click Next.

   The Unload to XML - Columns appears.

5. On Unload to XML - Columns, edit the following:
   1. Table Owner - Select the database schema that owns the object you would like to export.
   2. Table - Select the database object name you would like to export to an XML file format.
   3. Columns - Highlight the database object columns you would like to export to an XML document.
   4. Where Clause - Enter SQL WHERE clause to limit the rows that are selected. For example: DEPTNO = 10
   5. Click Next.
6. Click Unload Data.

   The file is downloaded.