paperlined.org
apps > oracle > sql*plus
document updated 10 years ago, on Oct 18, 2013

SQL*Plus is a PITA. Its text formatting is horrible.

Some alternatives to SQL*Plus are listed below:

best option

By far, your best option is to use a program like SQL Developer, or to use an API like DBD::Oracle. (note: Oracle contains its own Perl distribution, with a pre-compiled DBD::Oracle included [1] [2])

However, this is not always possible for various reasons. Sometimes you are forced to use whatever bare-bones tools that are installed on a basic OS. The rest of this page deals with these bare-bones solutions.

format SQL*Plus output better

SET   PAGESIZE 50000   LINESIZE 16000 NEWPAGE 0   SPACE 0   FEED OFF   HEAD OFF   TRIMSPOOL ON
You can also use COLUMN formatting commands. However, you have to do this for every column in the response, so this can get quite tedious.

format SQL*Plus output to be easily parseable by a script

You can generate data in several formats:

using External Data in Excel

This lets you dump the output of a query into Excel cells.

using dbms_xmlgen

Use a query like this: If you want to use single-quotes in the query, escape them by doubling them up: To prevent line-wrapping or other formatting problems:

Another issue: By default, dbms_xmlgen ignores columns that include a null. You can fix this by using dbms_xmlgen.setnullhandling(2)