By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,165 Members | 1,089 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

USING EXTERNAL TABLE

debasisdas
Expert 5K+
P: 8,127
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.
Expand|Select|Wrap|Line Numbers
  1. CREATE DIRECTORY MYDIR AS 'D:\';
  2.  
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.

Expand|Select|Wrap|Line Numbers
  1. GRANT READ,WRITE ON DIRECTORY MYDIR TO DEBASIS;
  2.  
Sample code to create EXTERNAL TABLE

Expand|Select|Wrap|Line Numbers
  1. create table extemp1
  2. (
  3. empno number(4),
  4. ename varchar2(10),
  5. JOB VARCHAR2(9),
  6. MGR NUMBER(4),
  7. HIREDATE DATE,
  8. SAL NUMBER(7,2),
  9. COMM NUMBER(7,2),
  10. DEPTNO NUMBER(2)
  11. )
  12. ORGANIZATION EXTERNAL
  13. (
  14. TYPE ORACLE_LOADER
  15. DEFAULT DIRECTORY MYDIR
  16. ACCESS PARAMETERS
  17. (
  18. RECORDS DELIMITED BY NEWLINE SKIP 1
  19. FIELDS TERMINATED BY ','
  20. (
  21. empno CHAR,
  22. ename CHAR,
  23. JOB CHAR,
  24. MGR CHAR,
  25. HIREDATE CHAR,
  26. SAL CHAR,
  27. COMM CHAR,
  28. DEPTNO CHAR
  29. ))
  30. LOCATION('DATA.LST')
  31. )
  32. REJECT LIMIT 1
  33. ;
  34.  
Sample example #2
======================
Expand|Select|Wrap|Line Numbers
  1.  
  2. CREATE TABLE SCOTT.et
  3. (
  4.   id    NUMBER(8,3),
  5.   name  VARCHAR2(10)
  6. )
  7. ORGANIZATION EXTERNAL
  8.   (  TYPE ORACLE_LOADER
  9.      DEFAULT DIRECTORY DDD
  10.      ACCESS PARAMETERS 
  11.        (        RECORDS DELIMITED BY NEWLINE
  12.        BADFILE DDD:'et.bad'
  13.        DISCARDFILE DDD:'et.dsc'
  14.        LOGFILE DDD:'et.log'
  15.        READSIZE 512
  16.        DATE_CACHE 1000
  17.        SKIP 0
  18.        FIELDS TERMINATED BY ','
  19.        MISSING FIELD VALUES ARE NULL
  20.        REJECT ROWS WITH ALL NULL FIELDS
  21.        (
  22.          id CHAR,
  23.          name CHAR
  24.        ) )
  25.      LOCATION (DDD:'aa.txt')
  26.   )
  27. REJECT LIMIT Unlimited
  28. NOPARALLEL
  29. NOMONITORING;
  30.  
May 11 '07 #1
Share this Article
Share on Google+