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

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.

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