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

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

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

external tables = sql loader

Use external tables if you can.

for Data loading and transformation External Tables are preferred than SQL Loader

Hi Xperts,

Thanks a lot.

Vijay

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