Oracle SQL*Plus: The Definitive Guide

Chapter 5. Extracting and Loading Data

SQL*Plus can be used 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, they don’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.

Oracle does, however, provide SQL*Plus. Even though it’s not a generic data extraction utility, through the creative use of SQL and SQL*Plus’s formatting options, you can extract numeric, date, and text data to a flat file. Depending on your needs, you can format the file as a comma-delimited file, 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 data files are often used to transfer data to legacy applications.

In addition to simply 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 be used in recreating the data at another site, or you can generate a file of Data Definition Language (DDL) statements to modify your own database. I’ve even seen people use SQL*Plus ...