Pavol Drotován
DB expert
09-09-2021

External tables in Oracle

External tables are an important intermediary between external environments and the database. In ORACLE, one cannot imagine data pumps or work with ETL and DWH without them. In general, it is a read-only table that is defined in the database but exists outside the database, meaning that the metadata is stored in the database and the data it contains is outside the database. A table that is connected to an external table looks like a normal table, but it does not have all its functionalities. It can be queried with the SELECT command, but no DML (update, insert, delete) can be done. Advantages:

the main benefit is to create an easy-to-use bridge between SQL tables and non-database data sources. (just like the Oracle SQL*Loader or Data Pump tool in the past)
An example of use is when you have some non-SQL data source that periodically produces information that need to be put in the DB, such as a text file, web page, excel, DMP file (file for working with Datapump) and so on. which is structured so that it can be copied directly to a file that the external SQL table will immediately recognize and be able to query. (note. Only txt works, for other files with the exception of DMP you need to install drivers)

Creating external tables

you declare columns and data types
you define the parameters according to which the SQL database communicates with the external table (parameters include the type of load, the first line for reading data or the location of the directory where the data is located)
you need to know what directory objects are and where they are located
you need to know the functionality of tools - drivers for working with external tables: oracle_loader, oracle_datapump

!Additional Constraints: No CONSTRAINTS can be created and External Table Column cannot be set to SET UNUSED!

DIRECTORY OBJECTS

To create an external table, we will need to identify the location in the operating system (in the Oracle client's $ORACLE_HOME-database folder) where the external file containing the table will be located.


CREATE OR REPLACE DIRECTORY
directory_name
AS
directory_reference;

Once the directory is created, the owner must grant READ or WRITE access to the USER

Tools for working with external tables:

The Oracle database provides a number of tools that are part of various packages. The tools that are important for external tables are:

Oracle_Loader
Oracle_DataPump

The basic difference between loader and Datapump is that Loader only loads data and Datapump also exports with a specific DMP extension.

Procedure for creation

We will create a directory that will contain the file
We assign read/write access for the user
We choose an external file (will it be txt, excel, web)
We will prepare the file/clean/format the data
We save the file in the directory (see point 1)
We compile the create table script

The create statement looks like this:

CREATE TABLE INVOICES_EXTERNAL
( INV_ID CHAR(3),
    INVOICE_DATE CHAR(11),
    PRODUCT_ID CHAR(5)
) ORGANIZATION EXTERNAL
(TYPE oracle_loader
  DEFAULT DIRECTORY t_orcl_ext
  ACCESS PARAMETERS
  (
   RECORDS DELIMITED BY newline
   SKIP 2 FIELDS(
    INV_ID CHAR(3),
    INVOICE_DATE CHAR(11),
    PRODUCT_ID CHAR(5)
   )
  )
  LOCATION ('inv.txt')
);
/* description r.2-4 column declaration
yr.1-5 classic create script + keyword "ORGANIZATION EXTERNAL"
From line 6, the definition regarding the external file
r.6 Access driver type for data import (oracle_loader) (MUST!)
r.7 referenced directory
r. 8-16 input parameters
r.10 as the rows are separated
r.11 which lines are skipped
r.11-15 column declaration (must have the same name and variable type as line 2-4
r.17 referenced file (MUST!)*/

Summary

Using examples, we showed how external tables work in Oracle. We found out what their use is and what role they play in the ETL process and DWH. More examples can be found here

Contact

+421 911 065 745