USING EXTERNAL TABLE

[debasisdas]

This thread contains some useful tips for using External tables.


USING EXTERNAL TABLE
=======================
1.THE TABLE POINTS TO EXTERNAL FILE. IF DATA IS ALTERED IN THE EXTERNAL FILE,DATA IN THE TABLE WILL ALSO CHANGE.
2.EXTERNAL TABLES CAN BE QUERIED IN THE SAME WAY AS STANDARD TABLES IN JOINS,VIEWS.....AND CAN USE ALL TYPES OF FUNCTION ON THE EXTERNAL TABLE.
3.TO GET INFO REGARDING EXTERNAL TABLES QUERY THE "USER_EXTERNAL_TABLES" DATA DICTIONARY VIEW.
4."USER_EXTERNAL_TABLES" DOES NOT SHOW THE NAME OF THE EXTERNAL FILE TO WHICH THE TABLE POINTS.FOR THIS QUERY "USER_EXTERNAL_LOCATIONS"
5.USER CAN'T PERFORM ANY 'DML' OPERATION ON EXTERNAL TABLES.
6.EXTERNAL TABLES CAN'T BE INDEXED.
7.CONSTAINTS CAN'T BE SPECIFIED ON EXTERNAL TABLES,NOT EVEN A NOT NULL OR FOREIGN KEY CONSTRAINT.

First create a directory using following sample code.

CREATE DIRECTORY MYDIR AS 'D:\';
 

Place the source file in the specified directory (path as above)

Data in the file should be specific to match the table structure.

Next

Grant read,write pregiledges to specified user.

GRANT READ,WRITE ON DIRECTORY MYDIR TO DEBASIS;
 

Sample code to create EXTERNAL TABLE

create table extemp1
(
empno number(4),
ename varchar2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY MYDIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE SKIP 1
FIELDS TERMINATED BY ','
(
empno CHAR,
ename CHAR,
JOB CHAR,
MGR CHAR,
HIREDATE CHAR,
SAL CHAR,
COMM CHAR,
DEPTNO CHAR
))
LOCATION('DATA.LST')
)
REJECT LIMIT 1
;
 

Sample example #2
======================

 
CREATE TABLE SCOTT.et
(
  id    NUMBER(8,3),
  name  VARCHAR2(10)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY DDD
     ACCESS PARAMETERS 
       (        RECORDS DELIMITED BY NEWLINE
       BADFILE DDD:'et.bad'
       DISCARDFILE DDD:'et.dsc'
       LOGFILE DDD:'et.log'
       READSIZE 512
       DATE_CACHE 1000
       SKIP 0
       FIELDS TERMINATED BY ','
       MISSING FIELD VALUES ARE NULL
       REJECT ROWS WITH ALL NULL FIELDS
       (
         id CHAR,
         name CHAR
       ) )
     LOCATION (DDD:'aa.txt')
  )
REJECT LIMIT Unlimited
NOPARALLEL
NOMONITORING;