What is External Table?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: What is External Table?

Hybrid View

  1. #1
    Join Date
    Jan 2003
    Posts
    7

    What is External Table?

    Hi Xperts,

    What is Externam Table? (Feature in 9i)
    What can i do with this one?

    Is it possible for me to create a new DATABASE table using the External table? like "CREATE TABLE TNAME1 AS SELECT * FROM EXTRENAL_TABLE".

    Thanks in Advance.

    Vijay

  2. #2
    Join Date
    Dec 2002
    Location
    India
    Posts
    34
    Hi Vijay Radha,

    I am giving u an example by connecting to scott/tiger user.
    Probably my explanation can clear ur doubt in external tables!!
    Loading Data by Using External Tables
    In this section of the lesson, you will load data into the Data Warehouse using External Tables, introduced in Oracle9i Release 1.
    Loading data using external tables
    To show you how external tables can be created and used, perform the following steps:
    1. Create the necessary directory objects.
    2. Create the external table.
    3. Change external file.
    4. Select from the external table.
    5. Parallel access of external tables.
    6. Oracle9iís new parallel insert capabilities.
    7. Perform parallel insert.

    CREATING DIRECTORIES
    ------------------------------
    CREATE DIRECTORY data_dir1 AS 'C:\EXTERNAL'
    CREATE DIRECTORY log_dir1 AS 'C:\EXTERNAL'

    CREATE TABLE EMP_ADDR_XT
    (
    empno NUMBER(4),
    country varchar2(10)
    )
    ORGANIZATION external
    (
    TYPE oracle_loader
    DEFAULT DIRECTORY data_dir1
    location
    (
    'emp_addr.dat'
    )
    )REJECT LIMIT UNLIMITED NOPARALLEL;

    Sample Dat file
    The Dat file emp_addr.dat contains the following data

    7369, USA
    7499, UK
    7521, INDIA
    7566, SA
    7654, RUSSIA
    7698, FRANCE
    7782, CHINA
    7788, MALAYSIA
    7839, ITALY
    7844, INDIA
    7876, INDIA
    7900, AUSTRALIA
    7902, SRI LANKA
    7934, INDIA

    1 SELECT EMP.EMPNO,ENAME,COUNTRY FROM EMP,EMP_ADDR_XT
    2* WHERE EMP.EMPNO=EMP_ADDR_XT.EMPNO
    SQL> /

    EMPNO ENAME COUNTRY
    ---------- ---------- ----------
    7369 SMITH USA
    7499 ALLEN UK
    7521 WARD INDIA
    7566 JONES SA
    7654 MARTIN RUSSIA
    7698 BLAKE FRANCE
    7782 CLARK CHINA
    7839 KING ITALY
    7844 TURNER INDIA
    7876 ADAMS INDIA
    7934 MILLER INDIA

    11 rows selected.


    Note: you cannot update any field in the flat file(EXTERNAL TABLE)
    SQL> UPDATE EMP_ADDR_XT SET COUNTRY='INDIA' WHERE EMPNO=7369;
    UPDATE EMP_ADDR_XT SET COUNTRY='INDIA' WHERE EMPNO=7369
    *
    ERROR at line 1:
    ORA-30657: operation not supported on external organized table

    The external tables are only read only files

    You can also create a Database Table from an external table.

    CREATE TABLE EMP_EXTERNAL AS SELECT * FROM EMP_ADDR_XT;

    SQL> select * from emp_external

    EMPNO COUNTRY
    ---------- ----------
    7369 USA
    7499 UK
    7521 INDIA
    7566 SA
    7654 RUSSIA
    7698 FRANCE
    7782 CHINA
    7788 MALAYSIA
    7839 ITALY
    7844 INDIA
    7876 INDIA

    EMPNO COUNTRY
    ---------- ----------
    7900 AUSTRALIA
    7902 SRI LANKA
    7934 INDIA

    14 rows selected.

    for more information
    http://otn.oracle.com/products/oracl.../OBE9i-Public/

    Thanks
    Sridhar
    Last edited by ksridhar; 02-05-2003 at 03:51 AM.

  3. #3
    Join Date
    Jan 2003
    Posts
    7

    External Table Vs. SQL*Loader

    Hi Xpert,

    Thanks a lot for detailed explanation.

    Will i get all the features supported by SQL*Loader in "External Table"?

    Which one is best to load data? If i get a comparative answer/href, it will be helpful for me.

    Vijay

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    external tables = sql loader

    Use external tables if you can.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Dec 2002
    Location
    India
    Posts
    34
    for Data loading and transformation External Tables are preferred than SQL Loader

  6. #6
    Join Date
    Jan 2003
    Posts
    7
    Hi Xperts,

    Thanks a lot.

    Vijay

  7. #7
    Join Date
    Mar 2002
    Posts
    534
    From my point of view SQL Loader is not equal external tables, it is similaire and probably based on the same code but not the same. Some of the differences can be found at:
    http://otn.oracle.com/docs/products/...11.htm#1008525

    As far as I know the "Direct Path Load" Option which you have with SQL*Loader is not available with the external tables.
    So if you have a big amount of data and that performance is important I would recommand to use SQL*Loader.

    Regrads
    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width