Custom Search . . .

Friday, July 13, 2007

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.


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




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;

---------- --------------------------------------------------
10 babu
20 taj

2 rows selected.

No comments: