Skip to Main Content
Oracle SQL*Plus: The Definitive Guide
book

Oracle SQL*Plus: The Definitive Guide

by Jonathan Gennick
March 1999
Intermediate to advanced content levelIntermediate to advanced
528 pages
14h 39m
English
O'Reilly Media, Inc.
Content preview from Oracle SQL*Plus: The Definitive Guide

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Jonathan Gennick
Oracle PL/SQL Programming, Third Edition

Oracle PL/SQL Programming, Third Edition

Steven Feuerstein, Bill Pribyl
Oracle SQL

Oracle SQL

Dan Hotka

Publisher Resources

ISBN: 1565925785Supplemental ContentCatalog PageErrata