External tables in Oracle

External tables allow someone to store data in a file while these files are shown as tables within Oracle. So, we have a two sided object – on one hand it can be shown as a file that is human readable, on the other hand it can be seen as a table. Let us see at the list of files on a hard disk somewhere:

Files line info.dat, info2.dat, dataDeal.txt are ordinary files that can be read. On the other hand, we have tables in Oracle that show like:

The Oracle system has the concept of external files. The code to create these is:

drop table dataDeal;
CREATE TABLE dataDeal
(
TECHNICAL_KEY NUMBER(10),
ID_ERSTE_ENT NUMBER(10),
EDT_DEAL DATE,
SDT_DEAL DATE,
UNQ_ID_SRC_SYS VARCHAR2(50),
LV_G_PRD_L3 VARCHAR2(25),
LVW_FIN_AMT_TP VARCHAR2(25),
LV_CCY VARCHAR2(25),
AMT  NUMBER(18)
)
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
 DEFAULT DIRECTORY external
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
SKIP 1
BADFILE 'bad_%a_%p.bad' 
LOGFILE 'log_%a_%p.log' 
 fields terminated by '\t'
  MISSING FIELD VALUES ARE NULL
 ( TECHNICAL_KEY
, ID_ERSTE_ENT
,EDT_DEAL date'dd.mm.yy' 
,SDT_DEAL date'dd.mm.yy'
,UNQ_ID_SRC_SYS
,LV_G_PRD_L3 
,LVW_FIN_AMT_TP 
,LV_CCY 
,AMT
)
)
 LOCATION (external:'dataDeal - Copy.txt')
);

select * from dataDeal;
select count(*) from dataDeal;


CREATE TABLE emp_load
      (employee_number      CHAR(5),
       employee_dob         CHAR(20),
       employee_last_name   CHAR(20),
       employee_first_name  CHAR(15),
       employee_middle_name CHAR(15),
       employee_hire_date   DATE)
    ORGANIZATION EXTERNAL
      (TYPE ORACLE_LOADER
      DEFAULT DIRECTORY EXTERNAL
      ACCESS PARAMETERS
        (RECORDS DELIMITED BY NEWLINE
         FIELDS (employee_number      CHAR(2),
                 employee_dob         CHAR(20),
                 employee_last_name   CHAR(18),
                 employee_first_name  CHAR(11),
                 employee_middle_name CHAR(11),
                 employee_hire_date   CHAR(10) date_format DATE mask "mm/dd/yyyy"
                )
        )
      LOCATION ('info.dat')
     );
 
 drop table emp_load2;
 CREATE TABLE emp_load2
      (employee_number      CHAR(5),
       employee_dob         CHAR(20),
       employee_last_name   CHAR(20),
       employee_first_name  CHAR(15),
       employee_middle_name CHAR(15),
       employee_hire_date   DATE)
    ORGANIZATION EXTERNAL
      (TYPE ORACLE_LOADER
      DEFAULT DIRECTORY EXTERNAL
      ACCESS PARAMETERS
        (RECORDS DELIMITED BY NEWLINE
        fields terminated by ';'
          (employee_number      ,
                 employee_dob         ,
                 employee_last_name   ,
                 employee_first_name  ,
                 employee_middle_name ,
                 employee_hire_date     DATE'mm/dd/yyyy'
                )
        )
      LOCATION ('info2.dat')
     );
    
     
     select employee_middle_name,to_char(employee_hire_date,'YYYY-MON-DD') from  emp_load2;
     
     
     drop table emp_load3;
 CREATE TABLE emp_load3
      (employee_number      CHAR(5),
       employee_dob         CHAR(20),
       employee_last_name   CHAR(20),
       employee_first_name  CHAR(15),
       employee_middle_name CHAR(15),
       employee_hire_date   DATE)
    ORGANIZATION EXTERNAL
      (TYPE ORACLE_LOADER
      DEFAULT DIRECTORY EXTERNAL
      ACCESS PARAMETERS
        (RECORDS DELIMITED BY NEWLINE
        fields terminated by '\t'
          (employee_number      ,
                 employee_dob         ,
                 employee_last_name   ,
                 employee_first_name  ,
                 employee_middle_name ,
                 employee_hire_date     DATE'mm/dd/yyyy'
                )
        )
      LOCATION ('info3.dat')
     );
    
     
     select employee_middle_name,to_char(employee_hire_date,'YYYY-MON-DD') from  emp_load3;

One only needs to create a directory for which the parameter value is:

Finally: the files can be found here