VIEWS_AS_TABLES in 11g and even 10g ???

12c introduced a new feature for datapump which allowed you to extract a 'view' as if it were a table and then take this off somewhere else to import as a 'table'.

Very nice.

However you can kind of do it (though not so elegantly) in earlier versions using datapump/external tables - here's how..... (and note i did the test in 12c as thats what i had to hand - but the process works fine in 10g and 11g)

In the example below I'm adding a couple of dummy columns to a select from a table - but the SQL query could be anything you like here - i just kept the example simple.

First up we create a very basic table and insert one row (1 row is enough to illustrate the point...)

SQL> create table dummytable (col1 number,col2 number);

Table created.

SQL> insert into dummytable values (1,2);

1 row created.

I then create an extenral table definition using the datapump driver - which does allow writing (where the normal sqlloader one does not). Notice i add 2 'dummy' columns - but the query could be a 10 table join - anything you like. You can also use parallel etc if need be.

CREATE TABLE demoxt
    ORGANIZATION EXTERNAL
   (
       TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY data_pump_dir
       LOCATION ( 'demo.dmp' )
    )
    AS
       SELECT 1 as newcol1, 'A' as newcol2, col1,col2
      FROM   dummytable;

I then extract the table ddl for later use.

select dbms_metadata.get_ddl('TABLE','DEMOXT') from dual
SQL> /

DBMS_METADATA.GET_DDL('TABLE','DEMOXT')
--------------------------------------------------------------------------------

  CREATE TABLE "OPS$ORACLE"."DEMOXT"
   (    "NEWCOL1" NUMBER,
        "NEWCOL2" CHAR(1),
        "COL1" NUMBER,
        "COL2" NUMBER
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY "DATA_PUMP_DIR"
      LOCATION
       ( 'demo.dmp'
       )
    )

A quick ls reveals the dumpfile is created

SQL>  !ls /oracle/12.1.0.2/rdbms/log/demo.dmp
/oracle/12.1.0.2/rdbms/log/demo.dmp

 I drop the original table and the external table now

SQL> drop table demoxt;

Table dropped.

SQL> drop table dummytable;

Table dropped.

 Note the dumpfile remains intact.

SQL>  !ls /oracle/12.1.0.2/rdbms/log/demo.dmp
/oracle/12.1.0.2/rdbms/log/demo.dmp

I then recreate the external table definition pointing at the previously created file

SQL>   CREATE TABLE "OPS$ORACLE"."DEMOXT"
  2     (    "NEWCOL1" NUMBER,
  3          "NEWCOL2" CHAR(1),
  4          "COL1" NUMBER,
  5          "COL2" NUMBER
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_DATAPUMP
      DEFAULT DIRECTORY "DATA_PUMP_DIR"
  6    7    8    9   10        LOCATION
       ( 'demo.dmp'
       )
    )
11   12   13   14  ;

Table created.

I then create a 'real' table from the external table dumpfile.

SQL> create table dummytable as select * from demoxt;

Table created.

 I can now describe it showing the extra columns

SQL> desc dummytable;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
NEWCOL1                                            NUMBER
NEWCOL2                                            CHAR(1)
COL1                                               NUMBER
COL2                                               NUMBER

And a quick select shows the table is there

SQL> select * from dummytable;

   NEWCOL1 N       COL1       COL2
---------- - ---------- ----------
         1 A          1          2


This is probably not that useful as the dumpfile is only useful to map an external table definition on top of - the file cannot just be loaded directly with impdp.

It's a pity there is no sql unloader which works in the same way and would just create a csv directly - this surely is not that hard to create and would be very useful - rather than using sqlplus or utl_file to create files.