4.1 Using Data Workshop

Data Workshop enables you to load and unload data to the database.

Parent topic: Using Oracle Application Express Utilities

4.1.1 About the Data Load and Unload Wizards

Use the Data Load and Data Unload wizards to load and unload delimited text data to and from the database.

The step-by-step wizards have the following features:

  • Load or unload XML files or delimited-field text files (such as comma-delimited (.csv) or tab-delimited files).

  • Load by copying and pasting from a spreadsheet.

  • Omit (skip) columns when loading or unloading.

  • Load into an existing table or create a new table from the loaded data.

  • When loading into a new table, the primary key can be taken from the data or generated from a new or existing Oracle sequence.

  • When loading into a new table, column names can be taken from the loaded data.

  • Each time that you load from a file, file details are saved in a Text Data Load Repository. You can access these files from within the repository at any time.

Limitations include the following:

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

  • There are no data type limitations for unloading to text or XML files, or for loading from XML files. However, when loading from spreadsheets (through copy and paste) or from text files, only the following data types are supported: NUMBER, DATE, VARCHAR2, CLOB, BINARY_FLOAT, and BINARY_DOUBLE.

Supported unload formats include:

  • Text such as comma-delimited or tab-delimited data

  • XML documents

See Also:

"Import/Export/Load/Unload Options"

Parent topic: Using Data Workshop

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

Parent topic: Using Data Workshop

4.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 4-1 provides a summary of these options.

Table 4-1 Summary of Oracle Application Express Import/Export Options

Feature or Utility Description

Data Load/Unload wizards in Oracle Application Express

  • Easy to use graphical interface

  • Loads/unloads from and to external text files (delimited fields) or XML files

  • 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 4-2 provides several load, unload, import, and export scenarios and suggests the appropriate option to use for each.

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

Data Load/Unload 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

Parent topic: Using Data Workshop

4.1.4 Accessing the Data Load/Unload Page

Access the Data Load and Unload page by clicking Utilities and then Data Workshop.

To access the Data Load/Unload 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, unload data, or view the repository.

Parent topic: Using Data Workshop

4.1.5 Loading Data

Use the Load Data Wizard to copy and paste or upload data.

See Also:

"Import/Export/Load/Unload Options"

Parent topic: Using Data Workshop

4.1.5.1 About Loading Data

You can load data into the Oracle Application Express database in several ways.

  • Copy and paste data from a spreadsheet.

  • Upload a spreadsheet file in a delimited format (such as comma-delimited (.csv) or tab-delimited).

    Note:

    Upon selecting the file to be uploaded, a data sample of the first 5 lines of the file is displayed in the preview window. This is active by default and works for every chosen file. You can see the data and adjust the separator and enclosed by characters before actually uploading the data. You can do the column mapping in the next dialog box.

  • Upload a text file containing comma-delimited or tab-delimited data.

  • Upload an XML document.

Parent topic: Loading Data

4.1.5.2 Loading a Text File or Spreadsheet Data

You can upload a Text file or copy and paste tab-delimited data directly into the Load Data Wizard.

To load data from a text file:

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

    The Load Data Wizard appears.

  5. Under Load To, select either Existing table or New table.
  6. Under Load From, select either Upload file or Copy and paste.

    • Upload file - Select to specify an existing text file to upload.

    • Copy and paste - Select to create a text file to upload by performing a copy and paste.

  7. Click Next.
  8. If you selected Upload file, under Load Data, make the following changes:

    • Text File - Browse for the name of the Text File to load.

    • Separator - Enter the column separator character. See field-level help for more details.

    • Optionally Enclosed By - If your data contains a delimiter character, enter it here. See field-level help for more details.

    • First row contains column names - Check this box if the first row of your data contains column names.

    • File Character Set - Select the character set in which your text file is encoded. See field-level help for more details.

  9. If you selected Copy and paste, under Data make these changes:

    • Data - Enter the text file to upload.

    • First row contains column names - Check this box if the first row of your data contains column names.

  10. Under Globalization, make the following changes:

    • Currency Symbol - If your data contains an international currency symbol, enter it here.

    • Group Separator - If your data contains a character that separates integer groups, for example to show thousands and millions, enter it here.

    • Decimal Character - If your data contains a character that separates the integer and decimal parts of a number, enter it here.

    Tip:

    See field-level help for more details about a specific attribute.

  11. Click Next.

    The table Properties page appears. Before performing the data upload, this page allows you to optionally customize the data.

  12. For Table Properties, make the following changes and click Next:

    • Schema - Select the schema containing the table to load.

    • Table Name - Identify the table name you want to create. By default, all table names are converted to upper case. Select Preserve Case to override this default behavior.

    • Column Names - Enter the names of the columns.

    • Data Type - Select the data type for this column.

    • Format - Enter the format type.

    • Description - Enter the description.

    • Column Length - Enter the length of the data in this column.

    • Upload - Select Yes to upload data for this column. Select No to not upload data for this column and skip to the next column.

  13. For Primary Key, make the following changes:

    • Primary Key From - Define the primary key column by choosing either an existing column or creating a new column. Options include:

      • Use an existing column - If you choose an existing column you must select the column from the columns being loaded.

      • Create new column - If you choose to create a new column, you must specify the name of the new column.

    • Constraint Name - The constraint name defaults to the table name appended with _PK. You can update the name if desired.

    • Primary Key Population - You must decide how your primary key is populated. You either generate it from a new sequence, from a new sequence or not at all. Options include:

      • Generated from a new sequence - If you choose a new sequence, you can use the sequence name given or update it as necessary. The sequence is created for you.

      • Generated from an existing sequence - If you choose an existing sequence, you must select the sequence from those currently defined in your schema.

        If you generate the primary key from a sequence, either new or existing, the wizard creates a trigger on the table to retrieve the next sequence value and populate the primary key when a record is inserted.

      • Not generated

  14. Click Load Data.

Parent topic: Loading Data

4.1.5.3 Loading an XML Document

Load an XML document using Load XML Data Wizard.

Oracle Application Express supports XML documents in Oracle's canonical XML format.

In Oracle's canonical XML format, each element represents a column value, each element is named after the column, all elements that are part of the same row are children of a <ROW> element, and all <ROW> elements are children of a <ROWSET> element.

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 XML Data.

    The Load XML Data Wizard appears.

  5. Select the appropriate schema for your data and click Next.
  6. Select the table into which to load the data.
  7. Follow the on-screen instructions.

Parent topic: Loading Data

4.1.6 Unloading Data

Use the Unload page to export the contents of a table to a text file or XML document.

  • Unloading a Text File
    Use the Unload to Text Wizard to export the contents of a table to a text file.
  • Unloading to an XML Document
    Use the Unload to XML 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"

Parent topic: Using Data Workshop

4.1.6.1 Unloading a Text File

Use the Unload to Text 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 Data Unload, click To Text.

    The Unload to Text Wizard appears.

  5. Select the appropriate schema.
  6. Select the table to unload.
  7. Highlight the columns to include, optionally enter a Where Clause to limit the records unloaded and click Next.
  8. For Options, enter the following:

    • Separator - Enter the type of separator used to separate column values.

    • Optionally Enclosed By - If your data contains a delimiter character, enter it here.

    • Include Column Names - Check this box to include the name of each column in the text file.

    • File Format - Select the appropriate file format.

    • File Character Set - Select the character set in which your text file is encoded.

    Tip:

    See field-level help for more details.

  9. Click Unload Data.

    The File Download window appears.

  10. Click Save to download the file.

Parent topic: Unloading Data

4.1.6.2 Unloading to an XML Document

Use the Unload to XML 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 data Unload, click to XML.

    The Unload to XML Wizard appears.

  4. Select the appropriate schema.
  5. Select the table to unload.
  6. Highlight the columns to include.
  7. Optionally enter a Where Clause to limit the records unloaded, and click Unload Data.

    A browser displays the XML data.

Parent topic: Unloading Data

4.1.7 Using the Repository

Loaded text data files and spreadsheets are stored in the Repository. You can select the appropriate repository.

To access the different repositories:

  1. On the Workspace home page, click the Utilities icon.
  2. Click Data Workshop.
  3. Under Repository, select one of the following:

    • Import Repository - Displays the status of loaded Text data. Details include file name, imported by, imported on, schema, table, bytes, and the number of rows that were loaded successfully and the number were not.

    • Spreadsheet Imports - Displays the status of loaded spreadsheet data. Details include file name, imported by, imported on, schema, table, and the number of rows that were loaded successfully and the number were not.

  4. To filter the display, make a selection from the Show list and click Go.
  5. To view information about a specific file, click the View icon.
  6. To delete an imported file, select it and click Delete Checked.

Parent topic: Using Data Workshop