In Oracle, how do I make an external table with the same structure as an existing table?
I know that you can create an export table like this:
create table bulk_mbr organization external(
type ORACLE_DATAPUMP
default directory jason_home
location ('mbr.dat'))
as SELECT * FROM mbr;
But I would like to do something like this for the import, to create an external import table with the same structure as the existing table, load the data into it, and then do a simple INSERT INTO / SELECT FROM query to move the data there. Is there a way to do this?
I 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 I got:
ORA-30657 operation not supported external organized table
+2
source to share
1 answer
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 (don't you have one? :) or dynamically using DBMS_METADATA.get_ddl
eg.
+2
source to share