Systems Engineering and RDBMS

Using External Tables in Oracle to load up data

Posted by decipherinfosys on April 17, 2007

Another way to load data in the oracle is by putting a file in the o/s (operating system) file folder and creating an Oracle directory object which points to o/s file structure to access the file resides in that folder.

We will create directory object which specifies an alias for above shown directory on a server. One should have ‘CREATE ANY DIRECTORY’ privilege to create the directory. The directory structure we are using in this example is defined by user_dump_dest oracle parameter. To check the value of this parameter on your system, issue following command.

SQL> show parameter user_dump_Dest

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

Value of the above parameter may be different in your case. Please make sure to change path appropriately in following sql. Now we will create directory.

SQL> CREATE OR REPLACE DIRECTORY DATALOAD AS ‘D:\oracle\product\10.2.0\admin\orcl\udump’;

Directory created.

Oracle user, who issues the create directory command, automatically gets read and write privilege on the directory. Other oracle users should be given specific grants to read and write content from the directory. One thing to keep in mind though is, this is different from the directory permission of the o/s. If grant is given to directory object but read or write permission is not given at o/s level, user may not be able to perform read/write operations on the directory.

Now we will create comma delimited text file. Following is the structure of the file. It contains the same four fields City, State, Zip and Country as mentioned in one of the earlier blog. Save following content in the file and save it as city_comma.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

This time we will create external tables to access data directly from the file. Defining an external table is a way to access data straight from the file using SELECT statement. This will eliminate the need to load data into interim table

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 (‘city_comma.txt’)
)
REJECT LIMIT UNLIMITED;

Above command will create an external table. Here Oracle uses SQL*Loader functionality via ORACLE_LOADER access driver. Like SQL*Loader, we can also specify badfile and logfile. We can also set REJECT LIMIT to specific number. Once table is created, we can use ‘SELECT’ statement against external table in a same way as we use it against 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

Few things we need to keep in mind for external tables are
•    DML operations cannot be performed against external tables.
•    Constraints and indexes cannot be created against external tables.
•    In 9i, data can be only read from external tables. In 10g we can also write into flat file using external tables. We will cover this in next blog.

For complete explanation and limitations of CREATE DIRECTORY and CREATE TABLE….EXTERNAL command, please refer to Oracle SQL Reference manual.

One Response to “Using External Tables in Oracle to load up data”

  1. […] by decipherinfosys on April 28th, 2007 In a previous blog post, we saw how using an external table, we can read the data in the text file and put it in the […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: