Steps for Creating EXTERNAL TABLE in 10g

Steps for Creating EXTERNAL TABLE in 10g

Step1:

Create DIRECTORY and Grant necessary privileges to user

CREATE DIRECTORY JAGAT_DIR AS \'C:\\JAGAT\';
GRANT READ ON DIRECTORY BALA_DIR TO PUBLIC;
GRANT READ ON DIRECTORY BALA_DIR TO PUBLIC;

Step2

Create a .txt file(data.txt) in \'C:\\JAGAT\' Directory

7369,SMITH,CLERK,20
7499,ALLEN,SALESMAN,30
7521,WARD,SALESMAN,30
7566,JONES,MANAGER,20
7654,MARTIN,SALESMAN,30

Step 3

Now create the table using below command.

CREATE TABLE ext_tab (
empno CHAR(4),
ename CHAR(20),
job CHAR(20),
deptno CHAR(2))
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY JAGAT_DIR
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
BADFILE \'bad_%a_%p.bad\'
LOGFILE \'log_%a_%p.log\'
FIELDS TERMINATED BY \',\'
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(empno, ename, job, deptno))
LOCATION (\'DATA.txt\')
)
PARALLEL
REJECT LIMIT 0
NOMONITORING;