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.

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.

This site uses cookies to deliver our services and to show you relevant ads and job listings. By using our site, you acknowledge that you have read and understand our
Cookie Policy,
Privacy Policy, and our
Terms of Service. Your use of GeekInterview’s Content, Products and Services, including the Exforsys Inc Network, is subject to these policies and terms.