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’;
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.
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
DEFAULT DIRECTORY DATALOAD
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ‘,’
MISSING FIELD VALUES ARE NULL
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”
Sorry, the comment form is closed at this time.