External Table Example In Oracle

External table is useful when working data outside of database. This means you can select formatted data without loading them into database. You can also load the data with SQL*Loader utility if you wish.
A small example shows how to query data outside of database:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as SYS

SQL> --select * from all_directories;
SQL> create or replace directory ext as 'c:\ext';
Directory created
SQL> grant read,write on directory ext to hr;
Grant succeeded
SQL> drop table t;
Table dropped
SQL> /*
  2  t.txt :
  3  --------------
  4  1|mennan
  5  2|ali
  6  3|mehmet
  7  4|
  8 
  9  ------------- 10  */
 11 
SQL>
SQL> create table t(i number, a varchar2(12))
  2  organization external(
  3    type oracle_loader
  4    default directory ext
  5    access parameters(
  6      records delimited by newline
  7      badfile ext:'rejected.txt'
  8      logfile ext:'log.txt'
  9      fields terminated by '|'
 10      missing field values are null
 11      (i,a)
 12    )
 13    location ('t.txt')
 14  )
 15  parallel 2
 16  reject limit unlimited;
Table created
SQL> select * from t;
         I A
---------- ------------
         1 mennan
         2 ali
         3 mehmet
         4

SQL> create table tt(i ,a)
  2  organization external(
  3    type oracle_datapump
  4    default directory ext
  5    access parameters(
  6      logfile ext:'log_tt.txt'
  7    )
  8    location ('tt.txt')
  9  )
 10  as
 11  select * from t;
Table created

Go directory and check files that are created....