Data Workshop enables you to load and unload data to the database.
Parent topic: Using Oracle Application Express Utilities
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:
Parent topic: Using Data Workshop
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
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 |
|
SQL*Loader utility |
|
Data Pump Export and Data Pump Import utilities |
|
Export and Import utilities |
|
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 |
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
Access the Data Load and Unload page by clicking Utilities and then Data Workshop.
To access the Data Load/Unload page:
Parent topic: Using Data Workshop
Use the Load Data Wizard to copy and paste or upload data.
See Also:
Parent topic: Using Data Workshop
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
You can upload a Text file or copy and paste tab-delimited data directly into the Load Data Wizard.
Parent topic: Loading Data
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:
Parent topic: Loading Data
Use the Unload page to export the contents of a table to a text file or XML document.
See Also:
Parent topic: Using Data Workshop
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:
Parent topic: Unloading Data
Use the Unload to XML Wizard to export the contents of a table to an XML document adhering to the Canonical XML specification.
Parent topic: Unloading Data
Loaded text data files and spreadsheets are stored in the Repository. You can select the appropriate repository.
To access the different repositories:
Parent topic: Using Data Workshop