Before 10g External Tables were used to read external data into destination database but after Oracle 10g external tables can be created as a CTAS(Create Table As Select) operation, which enables a one-time unloading of inside data.
This is NOT a native data unloader(no more sqlplus spools, UTL_FILE writes, Pro*C unloaders etc). Oracle supplied a new access driver called “ORACLE_DATAPUMP” and Oracle will “unload” an external table to a format only usable by the Data Pump utility another new 10g feature to replace traditional imp and exp. So generating an ASCII or CSV file from external tables is NOT possible, at least for today :)
Below is a simple demonstration of how to dump the data from the ALL_SOURCE view to a file.
The log file can be avoided using the NOLOGFILE keyword, but contains usefull information such as the time the file was opened and any error messages that occur during the creation or read-back of the external table.
Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0