|
External Tables in Oracle 10g
Overview:
External table in Oracle are a representation of external data in a file. These
tables are not stored in the database. Oracle first introduced External tables
in 9i database however you could only read from external tables. Now in Oracle
10g, you can also write in the external tables. In the article below, we will
describe the mechanism of creating external tables.
Creating External Tables:
There are three steps in creating external tables.
- Create the datafile
- Create the directory object
- Create the external table
Create The Datafile:
Create some sample data file called "org_data.txt" that we will load in the
table.
Create The Directory Object:
Create the directory object to hold the external datafile as sys user.
| SQL> create or replace directory org_data as 'C:\Oracle\External_files\org_data'; |
Create External Table:
Before creating table issues the grant read,write on directory" to user
where you want to create the external table.
SQL> Grant Read, Write on directory org_data to vigyan;
Create the external table as followsSQL> Connect as vigyan@db
SQL> Create table org_external (
empid number(10),
ename varchar2(50),
dept varchar2(50),
start_date varchar2(50)
)
organization external (
Type Oracle_Loader
Default Directory org_data
Access Parameters
(
Records Delimited by Newline
Fields Terminated by ','
Missing Field Value Are Null
)
Location ('org_data.txt')
)
Reject Limit Unlimited; |
The table Org_External is created and ready to query.
Environment: This article has been written
in the following environment.
Operating System: Windows XP
Database: Oracle 10g R2
| 
