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.


Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements

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

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