O'Reilly logo

Oracle SQL*Plus: The Definitive Guide by Jonathan Gennick

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required