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.


Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151
Simiral book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net