External Table

External Tables

Sometimes you want to access data that resides outside of the database in the text format, but you want to use it as if it were a table in the db. You could use a utility such as SQL*Loader to load the table into the database, the data may be quite volatile or your user base expertise might not in include executing SQL * Loader at win or unix.

Example:

SQL> create directory tst as 'f:\';

Directory created.

SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

D:\Documents and Settings\Administrator>f:

F:\>type babu.sql

10,babu
20,taj

F:\>

F:\>exit

SQL> create table emp ( eno_id int,ename varchar2(50))
2 organization external
3 (type oracle_loader
4 default directory tst
5 access parameters
6 (records delimited by newline
7 fields terminated by ','
8 (eno_id char,ename char)
9 )
10 location ('babu.sql')
11 );

Table created.

SQL> select * from emp;

ENO_ID ENAME
---------- --------------------------------------------------
10 babu
20 taj

2 rows selected.