Oracle SQL*Plus: The Definitive Guide

Limitations of SQL*Plus

When using SQL*Plus to extract data, there are some limitations to keep in mind. 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 data or binary data, SQL*Plus is not the tool to use.

A second SQL*Plus limitation you may encounter when extracting data is the linesize. This maximum output line supported by SQL*Plus varies from one platform to the next, but under Windows NT/95 it is 32,767 characters.

The final thing to keep in mind are the datatypes you can extract. SQL*Plus was really designed to work with the traditional scalar datatypes, such as NUMBER, VARCHAR2, etc. It can’t be used to extract large object types, such as BLOBs and CLOBs, nor can it handle other large, complex data types, such as VARRAYs and Oracle8 objects. Here is a list of the datatypes you can easily extract using SQL*Plus:

VARCHAR2

NVARCHAR2

NUMBER

DATE

ROWID

CHAR

NCHAR

These types can be converted to character strings, and you can easily concatenate those strings together to produce whatever output format you want, whether that be 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.