EXTERNAL TABLE

-----------------------------------------------------------

. EXTERNAL TABLE .

-----------------------------------------------------------

(SOME FEAUTRE---->READ ONLY,STORE IN OUTSIDE OF DATABASE FILE,STORE IN FLAT FILE,NO DML STATEMENT WILL WORK, NO IN DEX CAN BE CREATED)

Create a directory in any path

c:/first is my directory

Creating an directory object amed first

create directory first as 'c:/first';

Create a file "emp1.txt" inside the directory and in sert the following data into it

10,jones,11-Dec-1934

20,smith,12-Jun-1972

Create an external table

CREATE TABLE oldemp

(empno NUMBER(8), empname CHAR(20), birthdate DATE)

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY first

ACCESS PARAMETERS

(

RECORDS DELIMITED BY NEWLINE

FIELDS TERMINATED BY ','

)

LOCATION ('emp1.txt')

)

PARALLEL 5

REJECT LIMIT 200;

--->(ORACLE_LOADER)oracle driver from its external representation into an equivalent Oracle native representation

--->(ACCESS PARAMETER)data identificaton from the flat file(emp1.txt)

--->LOCATION identification of flat file

--->PARALLEL enables five parallel execution servers to simultaneously scan the external data sources (files) when executing the INSERT INTO TABLE statement

--->The REJECT LIMIT clause specifies that if more than 200 conversion errors occur during a query

of the external data, the query is aborted and an error returned*/

close the session and after starting another session query the table. It will remain as it is

Querying extenal table

select * from oldemp;

Open the "emp1.txt" and add the following data

30,arka,13-jun-1999

40,gora,12-jun-2004

Again query the data you will see the data added to the table

Now remove the "emp1.txt"

And query it again you will see the error

Take it to the old place and query again. it will run clearly