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

external_tables

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 user 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. a) 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. b) Oracle_datapump: This is used for both importing and exporting of data using platform independent format

external table in oracle with example

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/orac/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. a) You cannot perform insert, update, and delete operations
  2. b) Indexing not possible
  3. c) 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 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 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 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

How to resolve ORA-29913 with external tables

Tips on external table

How to load a CLOB field with an external table