External Table in Oracle

External Table in Oracle

Oracle has come with a new feature facilitating us to query directly from the FLAT File. This is know as external tables.

Steps:
1) Create Directory

create or replace directory abhi_ext_dir as '/home/abhi/external_table'

2) Grant access to Directory

grant read, write on directory abhi_ext_dir to apps

The user must have a read and write permission the directory('/home/abhi/external_table' in this case).

3) Create External Table

CREATE TABLE abhi_external_Table ( col1 VARCHAR2(50) ,col2 VARCHAR2(50) )
ORGANIZATION EXTERNAL
( TYPE oracle_loader DEFAULT DIRECTORY abhi_ext_dir ACCESS PARAMETERS ( FIELDS TERMINATED BY '|' ) LOCATION ('abc.txt'))

abc.txt is the name of the file that will be located in the path mentioned in the directory abhi_ext_dir

4) Place the file in the Directory Path

abc.txt (used in above create table command) is copied the directory.


5) Thats it, query table to extract data.

SELECT * FROM abhi_external_table

Everytime the file is updated with new data the data in the table will be automatically refreshed.

Ex.


--External Table


create or replace directory abhi_ext_dir as '/tmp'

--grant read, write on directory abhi_ext_dir to apps;


cat xyz.txt

56november, 15, 1980  baker             mary       alice     09/01/2004
87december, 20, 1970  roper             lisa       marie     01/01/1999


CREATE TABLE emp (emp_no CHAR(6), last_name CHAR(25), first_name CHAR(20), middle_initial CHAR(1), hire_date DATE, dob DATE);

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 abhi_ext_dir --def_dir1
   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 ('xyz.txt')
  );


select * from emp_load

drop table emp_load;

INSERT INTO emp (emp_no,
                 first_name,
                 middle_initial,
                 last_name,
                 hire_date,
                 dob)
(SELECT employee_number,
        employee_first_name,
        substr(employee_middle_name, 1, 1),
        employee_last_name,
        employee_hire_date,
        to_date(employee_dob,'month, dd, yyyy')
FROM emp_load);


SELECT * FROM emp;

  
select * from USER_EXTERNAL_TABLES;


drop table emp_load

--Other Commands


cat abc.txt
 123456, JIRI, 444-555-6666
 234567, TOM, 555-666-5555
 345678, CHRIS, 111-222-3333


CREATE TABLE abhi_external_table
 (
 CUSTOMER_ID     NUMBER(6),
 CUSTOMER_NAME   VARCHAR2(60),
 CUSTOMER_SSN    VARCHAR2(60)
 )
 ORGANIZATION EXTERNAL
    (
        TYPE oracle_loader
        DEFAULT DIRECTORY abhi_ext_dir
        ACCESS PARAMETERS ( FIELDS TERMINATED BY ',' )
        LOCATION ('abc.txt')
    )


select * from abhi_external_table
----------------------------------------
CUSTOMER_ID    CUSTOMER_NAME    CUSTOMER_SSN
123456     JIRI     444-555-6666
234567     TOM     555-666-5555
345678     CHRIS     111-222-3333