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