Import, Export, Load, and Unload Options

5.1.3 Import, Export, Load, and Unload Options

The Oracle Database and Oracle APEX 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 APEX Import/Export Options

Feature or Utility	Description
Load Data /Unload Data wizards in Oracle APEX	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 APEX
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`)