When using SQL*Plus to extract data, keep in mind some limitations. Because SQL*Plus was designed as a reporting tool and not a data extraction tool, the output must be text. If you need to write a file containing packed-decimal or binary data, SQL*Plus is not the tool to use.
A second SQL*Plus limitation you may encounter (but you probably won't) when extracting data is the line size . On many platforms, the upper limit for the SET LINESIZE command is 32,767 characters . Unless you want to deal with spreading data from one logical record over multiple, physical records, you'll need to keep your output line size at or under the maximum line size for your platform.
Finally, SQL*Plus was designed to work with traditional, scalar datatypes. If you're going to use SQL*Plus as an unloader, you'll find it works best for the following types of data:
These types can be converted to character strings, and you can concatenate those strings together to produce whatever output format you want, whether comma-delimited, tab-delimited, or fixed-width. Text is the key to extracting data with SQL*Plus. Think text. If you have nonalphanumeric data to extract, you should use a different tool.
Introduction to SQL*Plus
A Lightning SQL Tutorial
Generating Reports with SQL*Plus
Creating HTML Reports
Writing SQL*Plus Scripts
Extracting and Loading Data
Exploring Your Database
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 (Definitive Guides)
Authors: Jonathan Gennick
Similar book on Amazon