sheraz baig

• Nov 20th, 2013

 

External Tables let you query data in a flat file. In 9i, only read operations were permitted; in 10g, you can also write out data to an external table. While external tables can be queried, theyre not usable in many ways regular Oracle tables are. You cannot perform any DML operations on external tables other than table creation; one consequence is that you cant create an index on an external table. External tables are largely used as a convenient way of moving data into and out of the database.

Oracle uses SQL*Loader functionality to transfer data from file to oracle table and uses DATA Pump to move data out of the db into a file. External tables are convenient, SQL-based way to use SQL*Loader and Data Pump functionality

TYPE: Two types ORACLE_LOADER and ORACLE_DATAPUMP, first one is used just for dataload from external table to internal table
ORACLE_DATAPUMP is used for both load and unload the data.
DEFAULT DIRECTORY - specifies the default location of files that are read or written by external tables
ACCESS PARAMETERS - describe the external data source and implements the type of external table that was specified
LOCATION - specifies the location of the external data
For example, suppose that you receive a daily .csv report from another department. Instead of writing a SQL*Loader script to import each days .csv file into your database, you can simply create an external table and write an "insert ... select" SQL query to insert the data directly into your tables. Place the days CSV file in the location specified in the external table definition, run the query, and youre done.