Import, Export, Load, and Unload Options

The Oracle AI Database and Oracle APEX provide several powerful options for importing, exporting, loading, and unloading data.

The following table describes options for importing, exporting, loading, and unloading data.

Feature or Utility Description
Load Data /Unload Data wizards in 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-* provides several load, unload, import, and export scenarios and suggests the appropriate option to use for each.

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

See Also: Oracle AI Database Utilities for more information on Data Pump, the Import and Export utilities, and SQL*Loader