In Oracle, how do I make an external table that has the exact same structure as an existing table?

Question

I know that you can create a table for export like this:

create table bulk_mbr organization external( 
type ORACLE_DATAPUMP
default directory jason_home 
location ('mbr.dat')) 
as SELECT * FROM mbr;

But I'd like to do something like this for imports so I can create an external import table with the same structure as an existing table, load data into it, and then do a simple INSERT INTO/SELECT FROM query to move the data over there. Is there a way to do this?

I've tried this, but it doesn't work:

create table bulk_mbr organization external( 
type ORACLE_LOADER 
default directory jason_home 
location ('mbr.dat')) 
as SELECT * FROM mbr where 1=0;

But got:

ORA-30657: operation not supported on external organized table

Answer 1

Hi Jason,

just use your table description:

SQL> CREATE TABLE bulk_mbr (
  2     ID NUMBER,
  3     d VARCHAR2(4000)
  4  )
  5  ORGANIZATION EXTERNAL (
  6     TYPE ORACLE_LOADER
  7     DEFAULT DIRECTORY jason_home
  8     LOCATION ('mbr.dat')
  9  );

Table created

Either from your DDL repository (you have one haven't you? :) or dynamically with DBMS_METADATA.get_ddl for example.