Total Pageviews

August 4, 2016

8/04/2016 10:13:00 PM


What is External Table?

External tables allow Oracle to query data that is stored outside the database in flat files. 
No DML can be performed on external tables but they can be used for query, join and sort operations. Views and synonyms can be created against external tables. 
They are useful in the ETL process of data warehouses since the data doesn't need to be staged and can be queried in parallel. They should not be used for frequently queried tables.

How to create a External Table

Create a directory object pointing to the location of the files.

CREATE OR REPLACE DIRECTORY ext_tab_data AS '/data';
Create the external table using the CREATE TABLE..ORGANIZATION EXTERNAL syntax. This defines the metadata for the table describing how it should appear and how the data is loaded.

CREATE TABLE emp_ext (
  emp_code      VARCHAR2(5),
  emp_name      VARCHAR2(50),
  mgr_name  VARCHAR2(50)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY ext_tab_data
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
      emp_code      CHAR(5),
      emp_name      CHAR(50),
      mgr_name  CHAR(50)
    )
  )
  LOCATION ('Emp1.txt','Emp2.txt')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;
 
Related Posts Plugin for WordPress, Blogger...