Efficiently Load data using Oracle External Tables!!

 Author: Jared Still

Today we will discuss how to efficiently load data using Oracle external tables. You can load data in Oracle by putting a file in the operating system file folder. Then we will create an Oracle directory object that points to operating system file structure to access the file resides in that folder.

First of all we will create a directory object. This directory object will specify an alias for directory on a server. Remember you should have ‘CREATE ANY DIRECTORY' privilege to create the directory.

Specify Directory Structure:

We will define the directory structure by user_dump_dest oracle parameter. Run below command to check the value of user_dump_dest parameter on your system.

SQL> show parameter user_dump_Dest

NAME            TYPE        VALUE
————— ———– —————————————–
user_dump_dest  string      D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP

Create Directory:

Now we will create directory by running below command. Oracle automatically grants read and write privilege on the directory to user who issues the create directory command. Therefore you must assign specific grants to other users in order to read and write content from the directory. It will not be possible for a user to perform read/write operations on the directory if grant is assigned to directory object but read or write permission is not given at operating system level,.

SQL> CREATE OR REPLACE DIRECTORY DATALOAD AS ‘D:\oracle\product\10.2.0\admin\orcl\udump';
Directory created.

Create Comma Delimited Text File:

Now we will create comma delimited text file. Our file will contain the City, State, Zip and Country fields. We will save these contents in the file and save it as myCities.txt in D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP folder.

ATLANTA,GA,30318,USA
MARIETTA,GA,30067,USA
DULUTH,GA,30097,USA
JOHNS CREEK,GA,30005,USA
ROSWELL,GA,30021,USA

Create External Tables:

Now we will create external tables that will access data directly from the file using SELECT statement. Thus we need not to load data into interim table. You can also specify badfile and logfile or set REJECT LIMIT to specific number.

SQL>CREATE TABLE External_City
(
CITY            VARCHAR2(30),
STATE        VARCHAR2(20),
ZIP            VARCHAR2(10),
COUNTRY        VARCHAR2(30)
)

ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY DATALOAD
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ‘,'
MISSING FIELD VALUES ARE NULL
)
LOCATION ('myCities.txt')
)
REJECT LIMIT UNLIMITED;

View Data:

Here you can see the use of ‘SELECT' statement against external table. You will see no difference in its use from regular tables.

SQL> SELECT * FROM External_City;

CITY                    STATE      ZIP        COUNTRY
——————– ———-    ———-    —————
ATLANTA               GA         30318      USA
MARIETTA             GA         30067      USA
DULUTH                GA         30097      USA
JOHNS CREEK    GA         30005      USA
ROSWELL             GA         30021      USA

Limitations:

Although external tables provide an efficient method of loading data but there are some limitations associated with it. For example you can not perform DML operations or create constraints and indexes against external tables. In Oracle 9i you can only read data from external tables. However in Oracle 10g you can use external tables to read and write into flat file. Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Improved Statistics Gathering in Oracle 11g
   12 U’s for Good Database Design
   Oracle Performance Enhancement using Parallel Execution Tips
   Oracle 11g Enhanced Optimizer Statistics Maintenance!!