Oracle External Table Example

by
One common use of an external table is the selection of data from an OS CSV (flat) file via SQL *Plus. When using an external table in this mode, you must specify the type of data in the file and how the data are organized. You can select from an external table but aren’t permitted to modify the contents (no inserts, updates, or deletes).

The Oracle external table feature enables you to perform two distinct operations:

  • Transparently select information from OS comma-separated-value (CSV) files via SQL, which allows you to do tasks such as loading these files into the database.
  • Create platform-independent dump files that can be used to transfer data. You can also create these files as compressed and encrypt them for efficient and secure data transportation

In this example the flat file is named ex.csv and is located in the D:Extbl directory. It contains the following data:
5|2|0|0|12/04/2012|Half
6|1|0|1|09/06/2013|Quarter
7|4|0|1|08/10/2013|Full
8|1|1|0|06/15/2013|Quarter
First, create a directory object that points to the location of the flat file on disk:
SQL> create directory exa_dir as 'D:Extbl';
Then create script that creates the external table that will reference the flat file:
create table exadata_et(
exa_id NUMBER
,machine_count NUMBER
,hide_flag NUMBER
,oracle NUMBER
,ship_date DATE
,rack_type VARCHAR2(32)
)
organization external (
type oracle_loader
default directory exa_dir
access parameters
(
records delimited by newline
fields terminated by '|'
missing field values are null
(exa_id
,machine_count
,hide_flag
,oracle
,ship_date char date_format date mask "mm/dd/yyyy"
,rack_type)
)
location ('ex.csv')
)
reject limit unlimited;
An external table named EXADATA_ET is created when you execute this script. Now, use SQL*Plus to view the contents of the flat file:
SQL> select * from exadata_et;
EXA_ID MACHINE_COUNT HIDE_FLAG ORACLE SHIP_DATE RACK_TYPE
---------- ------------- ---------- ---------- ---------- --------------------
5 2 0 0 04-DEC-11 Half
6 1 0 1 06-SEP-12 Quarter
7 4 0 1 10-AUG-12 Full
8 1 1 0 15-JUN-12 Quarter

1 thought on “Oracle External Table Example”

Leave a Comment