Oracle External tables - Overview

Overview on Oracle External tables

External tables can read flat files (that follow some rules) as though they were ordinary (although read-only) Oracle tables. Therefore, it is convenient to use external tables to load flat files into the DB. External can be used like SQL tables in querying purpose. So we can use it for insert and update of data into permanent tables. 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.

Separate directory needs to be created to place the reference file i.e. the file which contains data to be uploaded. Directory objects can be created by DBAs or by any user with the CREATE ANY DIRECTORY privilege. After a directory is created, the user creating the directory object needs to grant READ or WRITE permission on the directory to other users.

Ex:

To create directory

create or replace directory ext_dir as '/home/ext_dir’; 

To grant access:

GRANT READ ON DIRECTORY ext_dir to fnuser;

After creating the directory for external table usage we can upload the reference file into that directory. Normally the flat file would be the excel files. The excel file would be converted into Comma separated files and will be used in loading into external tables as the input file.

External tables are read-only. No data manipulation language (DML) operations or index creation is allowed on an external table. When the external table is accessed through a SQL statement, the fields of the external table can be used just like any other field in a normal table.

Syntax for creating external tables

create table sample
organization external (
  type              oracle_loader
  default directory ext_dir
  access parameters (
    records delimited  by newline
    fields terminated by ','
   optionally enclosed by '"'
    missing field are values null
  )
  location ('inputfile.csv')
)
reject limit unlimited;

 

• ORGANIZATION EXTERNAL clause tells Oracle that we are creating an external table.



• ORACLE_LOADER driver is a new type that "powers" external tables.



• We can set a DEFAULT DIRECTORY once for the entire table. In most cases, we will wish to write log/bad/discard files to a logging directory and read our incoming data files from a data directory.



• ACCESS PARAMETERS clause contains the SQL*Loader-style reference to enable Oracle to parse the flat-file into rows and columns. Note that if we have made any syntactical errors in our ACCESS PARAMETERS, we can still create the external table. Access parameters themselves are not parsed until we issue a SELECT against the external table.



• LOCATION clause is where we specify the input file(s).

Accessing external tables:

After creating an external table, the data in the table can be viewed like normal SQL table.

Ex:

Select * from sample;

In the directory specified with ext_dir, a log file will as well be written upon selecting from the external table.

Advantages of External tables:

• Data in external tables can be queried before it is loaded into the tables.



• External tables are suitable for large data loads that may have a onetime use in the database.



• External tables eliminate the need to create staging or temporary tables.



• No need of physical space even for the largest external tables. Once the data files are loaded into OS, external tables can be created and can execute SQL queries against them.



• An external table load allows modification of the data being loaded by using SQL functions and PL/SQL functions as part of the INSERT statement that is used to create the external table.

Disadvantages:

• Till Oracle 11g there is no option to execute DML against an external table. External tables supports SELECT only.



• No index can be created on External tables.