External Tables:-
http://docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm#i1012231
Using External Tables to Load and Unload Data
In the context of external tables, loading data refers to the act of reading data from an external table and loading it into a table in the database. Unloading data refers to the act of reading data from a table in the database and inserting it into an external table. Note: Data can only be unloaded using the ORACLE_DATAPUMP access driver.
Loading Data
When data is loaded, the data stream is read from the files specified by the LOCATION and DEFAULT DIRECTORY clauses. The INSERT statement generates a flow of data from the external data source to the Oracle SQL engine, where data is processed. As data from the external source is parsed by the access driver and provided to the external table interface, it is converted from its external representation to its Oracle internal datatype.
Unloading Data Using the ORACLE_DATAPUMP Access Driver
To unload data, you use the ORACLE_DATAPUMP access driver. The data stream that is unloaded is in a proprietary format and contains all the column data for every row being unloaded. An unload operation also creates a metadata stream that describes the contents of the data stream. The information in the metadata stream is required for loading the data stream. Therefore, the metadata stream is written to the datafile and placed before the data stream.
Behavior Differences Between SQL*Loader and External Tables
This section describes important differences between loading data with external tables, using the ORACLE_LOADER access driver, as opposed to loading data with SQL*Loader conventional and direct path loads. This information does not apply to the ORACLE_DATAPUMP access driver.
Multiple Primary Input Datafiles
If there are multiple primary input datafiles with SQL*Loader loads, a bad file and a discard file are created for each input datafile. With external table loads, there is only one bad file and one discard file for all input datafiles. If parallel access drivers are used for the external table load, each access driver has its own bad file and discard file.
Syntax and Datatypes
The following are not supported with external table loads:
Use of CONTINUEIF or CONCATENATE to combine multiple physical records into a single logical record. Loading of the following SQL*Loader datatypes: GRAPHIC, GRAPHIC EXTERNAL, and VARGRAPHIC Use of the following database column types: LONGs, nested tables, VARRAYs, REFs, primary key REFs, and SIDs
Byte-Order Marks
With SQL*Loader, if a primary datafile uses a Unicode character set (UTF8 or UTF16) and it also contains a byte-order mark (BOM), then the byte-order mark is written at the beginning of the corresponding bad and discard files. With external table loads, the byte-order mark is not written at the beginning of the bad and discard files.
Default Character Sets, Date Masks, and Decimal Separator
For fields in a datafile, the settings of NLS environment variables on the client determine the default character set, date mask, and decimal separator. For fields in external tables, the database settings of the NLS parameters determine the default character set, date masks, and decimal separator.
Use of the Backslash Escape Character
In SQL*Loader, you can use the backslash (\) escape character to mark a single quotation mark as a single quotation mark, as follows:
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\''
In external tables, the use of the backslash escape character within a string will raise an error. The workaround is to use double quotation marks to mark the separation string, as follows:
TERMINATED BY ',' ENCLOSED BY "'"
