Example of Oracle External Table

Example of Oracle External Table
================================

CREATE OR REPLACE DIRECTORY dir_test AS 'C:\dir_test';

GRANT READ,WRITE ON DIRECTORY dir_test TO scott;

data on text.txt
/*
00100,halim,md,1234566,halim@yahoo.com,58
00200,jamal,khan,456789,jamal@yahoo.com,50
00300,nazu,haque,456789,nazu@yahoo.com,52
00400,nazim,md,456799221,nazim@yahoo.com,90
00500,niger,sultana,123456787,niger@yahoo.com,50
00600,zakir,Dr,465465464,zakir@yahoo.com,17
*/

DROP TABLE external_table_emp;


CREATE TABLE external_table_emp
( empid VARCHAR2(6),
last_name VARCHAR2(100),
first_name VARCHAR2(100),
phone_no VARCHAR2(20),
email VARCHAR2(200),
age NUMBER(3)
)
ORGANIZATION EXTERNAL
( DEFAULT DIRECTORY dir_test
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
)
LOCATION ('test.txt')
);


SELECT * FROM external_table_emp;