8,127
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_EXTERNA L_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.
Expand|Select|Wrap|Line Numbers
- CREATE DIRECTORY MYDIR AS 'D:\';
Data in the file should be specific to match the table structure.
Next
Grant read,write pregiledges to specified user.
Expand|Select|Wrap|Line Numbers
- GRANT READ,WRITE ON DIRECTORY MYDIR TO DEBASIS;
Expand|Select|Wrap|Line Numbers
- 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
- ;
=============== =======
Expand|Select|Wrap|Line Numbers
- 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;
