Oracle External tables

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

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 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 with in oracle. Basically you just 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 with in the database.

5) The oracle server provides two major oracle driver to read the flat files

1. Oracle_loader: This is used for the reading the flat files using the oracle loader technology. It basically allows to read the files which can be interpreted using SQL loader technology
2. Oracle_datapump: This is used for both importing and exporting of data using platform independent format

ACCESSING EXTERNAL TABLE DATA

There are few steps 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

1. You cannot perform insert, update, and delete operations
2. Oracle Indexing not possible
3.  Constraints not possible

BENEFITS OF EXTERNAL TABLES IN ORACLE

1. a) Queries of external tables complete very quickly even though a full table scan id required with each access
2. b) You can join external tables to each other or to standard oracle tables
3. 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 12c 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

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