document updated 11 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:

CSV [1]
HTML [1]
XML via dbms_xmlgen [1]

using External Data in Excel

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

how to use Oracle as an External Data source in Excel: [1] [2] [3]
how to use an SQL query when loading External Data: [1]
how to use SQL parameters (by filling them in from a specific cell, or prompting the user): [1] [2] [3]
how to modify the SQL query from VBA: [1] [2]

using dbms_xmlgen

Use a query like this:

SELECT DBMS_XMLGEN.GETXML('SELECT * FROM TABLENAME');

If you want to use single-quotes in the query, escape them by doubling them up:

SELECT DBMS_XMLGEN.GETXML('SELECT * FROM TABLENAME WHERE ID=''ABC123'' AND ROWNUM<10');

To prevent line-wrapping or other formatting problems:

SET LINESIZE 32767 LONGCHUNKSIZE 99999999 LONG 99999999

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