469,903 Members | 1,511 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

USING EXTERNAL TABLE

debasisdas
8,127 Expert 4TB
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
0 8122

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Erohsik | last post: by
reply views Thread by Sameers (theAngrycodeR) via .NET 247 | last post: by
1 post views Thread by monishkar | last post: by
reply views Thread by Ujval Shah | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.