Oracle Utilities - Unloading data to External Tables

Author: Packt Publishing Published on: 4th Jan 2010

The driver used to unload data to an External Table is the ORACLE_DATAPUMP access driver. It dumps the contents of a table in a binary proprietary format file. This way you can exchange data with other 10g and higher databases in a preformatted way to meet the other database's requirements. Unloading data to an External Table doesn't make it updateable, the tables are still limited to being read only.

Ads

Let's unload the EMPLOYEES table to an External Table:

Sample Code

create table dp_employees
organization external(
type oracle_datapump
default directory EXTTABDIR
location ('dp_employees.dmp')
)
as
select * from employees

This creates a table named DP_EMPLOYEES, located at the specified EXTTABDIR directory and with a defined OS file name.

In the next example, at a different database a new DP_EMPLOYEES table is created, this table uses the already unloaded data by the first database. This DP_EMPLOYEES External Table is created on the 11g database side.

Sample Code

create table dp_employees(
EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
EMAIL VARCHAR2(25),
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE,
JOB_ID VARCHAR2(10),
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6),
DEPARTMENT_ID NUMBER(4)
)
organization external
(
type oracle_datapump
default directory EXTTABDIR
location ('dp_employees.dmp')
)

This table can already read in the unloaded data from the first database. The second database is a regular 11g database. So this shows the inter-version upward compatibility between a 10g and an 11g database.

Ads

Sample Code