Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Chapter 9. Extracting and Loading Data

You can use SQL*Plus to extract data from Oracle for use in a spreadsheet or some other application. The need to do this is so common that it's a wonder Oracle doesn't supply an application specifically for that purpose. Unfortunately, the company doesn't. Oracle does provide SQL*Loader, a utility that can load data into Oracle from almost any form of flat file, but there is no corresponding SQL*Unloader.

Tip

Oracle's new, built-in, web development environment, HTML DB, does have some built-in data-unloading capabilities that may be worth investigating if you need that sort of thing.

Oracle does, however, provide SQL*Plus. Even though SQL*Plus is not a generic data extraction utility, you can extract numeric, date, and text data to a flat file through the creative use of SQL and SQL*Plus's formatting options. Depending on your needs, you can format the file as a comma-delimited file or a tab-delimited file, or you can format the data in fixed-width columns. Comma-delimited files are most useful if you are transferring data to a spreadsheet such as Lotus 1-2-3 or a desktop database such Microsoft Access. Fixed-width, columnar datafiles are often used to transfer data to legacy applications.

In addition to extracting data, you can get more creative and use SQL*Plus to generate a script file containing SQL statements. This is referred to as "using SQL to write SQL." You can do something as simple as generating a flat file of INSERT statements to ...