How to use external tables in Oracle with example

External table is one of the important feature in oracle , we will exploring external table in oracle with example in this post

Table of Contents

What are EXTERNAL TABLES in Oracle?

1)External tables are read-only tables where the data is stored in flat files outside the database

2) You can use the external table feature to access external files as if they are tables inside the database.

3) When you create an external table, you define its structure and location within Oracle. Basically, you store the metadata inside the oracle

4) When you query the table, oracle reads the external table and returns the results just as if the data had been stored within the database.

5) The Oracle server provides two major Oracle drivers to read the flat files

Oracle_loader: This is used for reading the flat files using the Oracle loader technology. It allows the reading of the files which can be interpreted using SQL loader technology

Oracle_datapump: This is used for both importing and exporting data using the platform-independent format

ACCESSING EXTERNAL TABLE DATA

There are a few steps which need to be performed in order to access the external table

We must first use the create directory command to define a directory object pointing to the external file location

sqlplus "/ as sysdba"
Create directory ext_dir as ‘/export/home/ora/external’;

Users who will access the external files must have the read and write privilege on the directory.

Grant all on directory ext_dir to user;

Let us now create or put some files under the directory

SQL> Conn user/user
SQL> Spool city.lst
SQL> Select city || ‘,’ || state || ‘,’ || country from countries;
SQL> Spool off

Now you should be able to see the “city.lst” file in the directory

Now we should create the external table using the below command

Create table city_ext
(city varchar(10),
state varchar(14),
country varchar(13))
Organization external  ( type oracle_loader
Default directory ext_dir
Access parameters
( records delimited by newline
Fields terminated by “,”
( city char(10),
state char(14),
country char(13)))
Location (‘city.lst’))
PARALLEL 5
REJECT LIMIT UNLIMITED;

Explanation of each term

Organization external	It tells that data is stored outside the database
Type	It is the Oracle driver or API which interpret the external data for the database. If you don’t specify anything then oracle_loader is the default
Default directory	It is the directory location where the flat files can be stored. Default directory is also used for storing the auxiliary files created from the access of oracle driver such as log file and bad files. We Can specify multiple directories to facilitate load balancing on multiple drives
Access parameters	It let u specify values of the parameter of the specific access driver for this external table
Location	It let you specify the external flat file name
REJECT LIMIT	It let you specify how many conversion errors can occur before oracle server abort the operation and return the error

5) SELECTING DATA FROM EXTERNAL TABLE

SQL> select * from city_ext;

This will read from “city.lst” which is an operating system level file.

If the file “city.lst” is not present, the select statement would throw this error

SELECT *
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file city.lst in EXT_DIR not found
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

LIMITATIONS ON EXTERNAL TABLES

You cannot perform insert, update, and delete operations

Oracle Indexing not possible
Constraints not possible

BENEFITS OF EXTERNAL TABLES IN ORACLE

a) Queries of external tables complete very quickly even though a full table scan id required with each access

b) You can join external tables to each other or to standard oracle tables
c) We can create views on the external table

New Update in 11gR2 for External Table

With Oracle 11g Release 2, new PREPROCESSOR clause is introduced which can identify a directory object and script used to process the files before they are read by the external table. This feature was backported to 11gR1 (11.1.0.7).

The PREPROCESSOR clause is especially useful for reading compressed files, since they are unzipped and piped straight into the external table process without ever having to be unzipped on the file system.

Let us take an example to better understand this parameter.

Suppose the city.list is compressed, then file name would be city.list.Z

Create table city_ext
(city varchar(10),
state varchar(14),
country varchar(13))
Organization external  ( type oracle_loader
Default directory ext_dir
PREPROCESSOR ext_dir:uncompress
Access parameters
( records delimited by newline
Fields terminated by “,”
( city char(10),
state char(14),
country char(13)))
Location (‘city.lst.Z’))
PARALLEL 5
REJECT LIMIT UNLIMITED;

Here the PREPROCESSOR clause will first uncompress the file city.lst.Z using command uncompress before looking for the data. All would happen on the fly and you wont need to uncompress the file city.lst.Z

Unloading/Loading data using Oracle External tables

Oracle also provided support for datapump technology in external table.

We can unload the table using oracle_datadump access driver

CREATE TABLE countries_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY ext_dir
LOCATION ('countries.dmp')
)
AS SELECT * FROM countries;

This will create countries.dmp in the directory. Now even the table countries is dropped, we can still watch the data using countries_xt table.

We can even take dump file to another database and then create external table to watch the data

CREATE TABLE countries_xt (
city    VARCHAR2(10),
state       VARCHAR2(9),
countries    VARCHAR2(9))
ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY ext_dir
LOCATION ('countries.dmp ')
);

Select * from countries_xt;

Enhancements in External tables in the 12cR1 database

Oracle_loader

Location clause can have wild character. * stands for multiple character and ? for single character.

Fields csv clause has been introduced

Oracle_datapump

We can unload the data using advanced compression option

Enhancements in External tables in 12cR2

As of Oracle Database 12c Release 2 (12.2.0.1), you can partition data contained in external tables, which allows you to take advantage of the same performance improvements provided when you partition tables stored in a database (for example, partition pruning).

test1.dat:
1, AAAAA Plumbing,01372,
28, Sparkly Laundry,78907,
13, Andi's Doughnuts,54570,
51, XV,61614,
47, HTFH,22201,
89, Friendly Pharmacy,89901,

test2.dat:
121, Pleasant Pets,33893,
130, Bailey the Bookmonger,99915,
105, Le Bistrot du Chat Noir,94114,

test3.dat:
210, The Electric Eel Diner,07101,
222, Everyt'ing General Store,80118,
231, Big Rocket Market,01754,

There are three fields in the data file: CUSTOMER_NUMBER, CUSTOMER_NAME and POSTAL_CODE. The external table uses range partitioning on CUSTOMER_NUMBER to create three partitions.
Partition 1 is for customer_number less than 100
Partition 2 is for customer_number less than 200
Partition 3 is for customer_number less than 300

SQL> create table customer_list_xt
2 (CUSTOMER_NUMBER number, CUSTOMER_NAME VARCHAR2(50), POSTAL_CODE CHAR(5))
3 organization external
4 (type oracle_loader default directory def_dir1)
5 partition by range(CUSTOMER_NUMBER)
6 (
7 partition p1 values less than (100) location('test1.dat'),
8 partition p2 values less than (200) location('test2.dat'),
9 partition p3 values less than (300) location('test3.dat')
10 );

Table created.

Enhancements in External tables in 19c

The hybrid partitioned tables feature extends Oracle partitioning by enabling partitions to reside in both Oracle Database segments and in external files and sources. This feature significantly enhances the functionality of partitioning for Big Data SQL where large portions of a table can reside in external partitions.

Hybrid partitioned tables enable you to integrate internal partitions and external partitions into a single partition table. With this feature, you can also move non-active partitions to external files, such as Oracle Data Pump files, for a cheaper storage solution.

How to find the external tables in the Database

SELECT * FROM dba_external_tables where owner='&1';

Related articles

ORA-29913 with external tables
Tips on external table
How to load a CLOB field with an external table
oracle create table as select