Oracle SQLPlus [Electronic resources] : 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.

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
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.