Loading External Table from dumpfile I have created a dumpfile using EXPDP process.

I am trying to create an external table using the above said dumpfile.

I am using the following statement to create the external table:

CREATE TABLE HR.DATA_LOAD
(
"EMP_NO" NUMBER(10,0) NOT NULL ENABLE, 
    "EMP_NAME" VARCHAR2(20 BYTE), 
    "DEPT_ID" NUMBER(10,0), 
    "DEPT_NM" VARCHAR2(20 BYTE), 
    "DOJ" VARCHAR2(20 BYTE), 
    "LOCATION" VARCHAR2(20 BYTE), 
    "PHN_NUM" NUMBER(20,0), 
    "EMAIL" VARCHAR2(20 BYTE), 
    "COL1" VARCHAR2(20 BYTE), 
    "COL2" VARCHAR2(20 BYTE)
)
ORGANIZATIONAL EXTERNAL
(
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY DATA_PUMP_DIR
  LOCATION ('exp_dumpfile.dmp')
);


After execution of the above statement the table is created but when I issue SELECT statement to see data I get the following error:




ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-39324: cannot load data from Data Pump dump file 





Am I doing something wrong or is it the case that I can use a dumpfile generated by Datapump method to create an external table?
If you look up the description of error ORA-39324 in the Oracle documentation you will see:

Cause: A dump file was specified for a load operation that had been created using the Data Pump. This type of dump file cannot be processed by external tables using the ORACLE_DATAPUMP access driver.
Action: Access the data in this dump file using the Data Pump.

A dump created by EXPDP can only be read by IMPDP (or dbms_datapump).  Whereas a dump created using writable external tables (using datapump driver) can be re-read only using external table interface (using datapump driver). Expdp does more than dump data (which is the only thing done by writable external tables using datapump driver).

Community Wiki answer originally based on comments by BriteSponge and Raj

Community wiki posts have been donated to the community in hopes that others will edit them to keep them up to date, to add useful information, and generally improve their quality.