External Tables

Benefit of using External Tables is you can alleviates the need for staging tables and redundant flat file loading as the file expands.

1.First you need to diagnose which character you can able to define as a delimited file.
2.CREATE DIRECTORY data_dir as 'c:\test';

3.CREATE DIRECTORY log_dir as 'c:\test\log';

4.
CREATE TABLE EXTERNAL_FILE_READ
(
EMPNO NUMBER(5),
EMP_NAME VARCHAR2(80),
DEPT_ID NUMBER(5)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS
(
RECORDs DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE log_dir:'emp_%p.bad'
LOGFILE log_dir:'emp_%p.log'
FIELDS TERMINATED BY "|"LDRTRIM
)
location
('emp.csv')
)
REJECT LIMIT UNLIMITED NOPARALLEL
/
5.select * from external_file_read;
more information on external table you can read Database Utilities guide of oracle documentation.