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.

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 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 to generate operating system shell scripts to use in modifying and maintaining their database.

In this chapter, I will walk you through the process of writing a script to extract data from the sample database into a flat file. You will see how SQL can be written to produce a comma-delimited text file, a fixed-width text file, or a file of INSERT statements. Once this is done, you will see how that same data can be loaded back into Oracle.