|
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;
|
