DEFINITION: You can user external table feature
to access external files as if they are tables inside the database.
When you create an external table,
you define its structure and location with in oracle.
When you query the table, oracle
reads the external table and returns the results just as if the data had been
stored with in the database.
ACCESSING EXTERNAL TABLE DATA
To access external files from within
oracle, you must first use the create directory command to define a directory
object pointing to the external file location
Users who will access the external
files must have the read and write privilege on the directory.
Ex:
CREATING DIRECTORY AND OS LEVEL FILE
SQL> Sqlplus system/manager
SQL> Create directory saketh_dir as
‘/Visdb/visdb/9.2.0/external’;
SQL> Grant all on directory saketh_dir to saketh;
SQL> Conn saketh/saketh
SQL> Spool dept.lst
SQL> Select deptno || ‘,’ || dname || ‘,’ || loc from
dept;
SQL> Spool off
CREATING EXTERNAL TABLE
SQL> Create table dept_ext
(deptno number(2),
Dname varchar(14),
Loc varchar(13))
Organization
external ( type oracle_loader
Default directory saketh_dir
Access parameters
( records delimited by newline
Fields terminated by “,”
( deptno number(2),
Dname varchar(14),
Loc
varchar(13)))
Location
(‘/Visdb/visdb/9.2.0/dept.lst’));
SELECTING DATA FROM EXTERNAL TABLE
SQL>
select * from dept_ext;
This will read from dept.lst which is
a operating system level file.
LIMITATIONS ON EXTERNAL TABLES
a) You can not perform insert, update,
and delete operations
b) Indexing not possible
c) Constraints not possible
BENEFITS OF EXTERNAL TABLES
a) Queries of external tables complete very quickly even
though a full table scan id required with each access
b) You can join external tables to each
other or to standard tables
No comments:
Post a Comment