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

Limitations of SQL*Plus

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:

• VARCHAR2

• NVARCHAR2

• NUMBER

• DATE

• ROWID

• CHAR

• NCHAR

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.