The Data Load/Unload wizards in Oracle Application Express enable you to easily load and unload delimited text data to and from the database. The step-by-step wizards have the following features:
You can load or unload XML files or delimited-field text files (such as comma-delimited (.csv) or tab-delimited files).
You can load by copying and pasting from a spreadsheet.
You can omit (skip) columns when loading or unloading.
You can 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
Topics:
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.
Topics:
The Oracle Database and Oracle Application Express provide several powerful options for importing, exporting, loading, and unloading data. Table 4-2 provides a summary of these options.
Table 4-2 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-3 provides several load, unload, import, and export scenarios and suggests the appropriate option to use for each.
Table 4-3 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. There is no XMLType data in any of the data. |
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 ( |
Oracle Database Utilities for more information on Data Pump, the Import and Export utilities, and SQL*Loader
To access the Data Load/Unload page:
On the Workspace home page, click the SQL Workshop icon.
Click Utilities.
Click Data Workshop.
The Data workshop page appears.
Click the appropriate icon to load data, unload data, or view the repository.
"Loading Data", "Unloading Data", and "Using Repository"
You can load data into the Oracle Application Express database in the following ways:
Copy and paste data from a spreadsheet.
Upload a spreadsheet file in a delimited format (such as comma-delimited (.csv) or tab-delimited).
Upload a text file containing comma-delimited or tab-delimited data.
Upload an XML document.
Topics:
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:
On the Workspace home page, click the SQL Workshop icon.
Click Utilities.
Click Data Workshop.
Under Data Load, click Text Data.
Under Load To, select either Existing table or New table.
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.
Click Next.
If you selected Upload file, under Load Data, make the following changes:
Text File - Browse for or enter the name of the Text File to load.
Separator - Enter the column separator character. See Item Help for more details.
Optionally Enclosed By - If your data contains a delimiter character, enter it here. See Item 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 Item Help for more details.
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.
Under Globalization, make the following changes:
Currency Symbol - If your data contains an international currency symbol, enter it here. See Item Help for more details.
Group Separator - If your data contains a character that separates integer groups, for example to show thousands and millions, enter it here. See item Help for more details.
Decimal Character - If your data contains a character that separates the integer and decimal parts of a number, enter it here. See item Help for more details.
Click Next.
The table Properties page appears. Before performing the data upload, this page allows you to optionally customize the data.
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.
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.
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
Click Load Data.
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:
You can use the Unload page to export the contents of a table to a text file or XML document.
Topics:
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:
On the Workspace home page, click the SQL Workshop icon.
Click Utilities.
Click Data Workshop.
Under Data Unload, click To Text.
The Unload to Text Wizard appears.
Select the appropriate schema and click Next.
Select the table to unload and click Next.
Highlight the columns to include, optionally enter a Where Clause to limit the records unloaded and click Next.
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. See item Help for more details.
Include Column Names - Check this box to include the name of each column in the text file. See item Help for more details.
File Format - Select the appropriate file format. See item Help for more details.
File Character Set - Select the character set in which your text file is encoded. See item Help for more details.
Click Unload Data.
The File Download window appears.
Click Save to download the file.
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:
On the Workspace home page, click the Utilities icon.
Click Data Workshop.
Under data Unload, click to XML.
Select the appropriate schema and click Next.
Select the table to unload and click Next.
Highlight the columns to include.
Check Export as File to save the output directly to a file.
If you do not check Export as File, the XML document is displayed in your browser and can be saved to a file or cut and pasted as necessary.
Optionally enter a Where Clause to limit the records unloaded, and click Unload Data.
A browser displays the XML data.
Loaded text data files and spreadsheets are stored in the Repository.
To access the different repositories:
On the Workspace home page, click the Utilities icon.
Click Data Workshop.
Make one of the following selections:
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.
By clicking the details magnifying glass you can review the failed rows.
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.
By clicking the details magnifying glass you can review the failed rows.
To filter the display, make a selection from the Show list and click Go.
To view information about a specific file, click the View icon.
To delete an imported file, select it and click Delete Checked.