 |
|
Formatting Output in SQL*Plus
By John Garmany |
(To learn more about Oracle output formatting,
see this note on
easy formatting of Oracle output in HTML and XML format, a very
robust solution to Oracle output.)
Formatting Output in
SQL*Plus
So far, the results returned by our queries have
been unformatted and pretty bad looking. What we need to do is format
the results. It is important to realize that you are formatting the
results in SQL*Plus, not the database. The database just returns the answer;
SQL*Plus formats what you see.
The difference between a SQL*Pluscommand
and a query is that the SQL*Plus command does not end in a semicolon.
To see the current settings of SQL*Plus’ parameters, enter “show
all”. Most items we will leave at the default. Below is a listing of
some of the important parameters that we will need to set to format
our output.
pages
or pagesize – this
parameter determines how many lines SQL*Plus will print
to the screen before starting a new page and reprinting the column
headings. To turn off column headings, set pages to zero. To have
the column headings appear only once, set pages very high (pages 999).
pages defaults to 14.
line
or linesize –
determines how long a line is. It defaults to 80 characters.
feedback
– This is the comment at the end
of your listing that tells you how many rows were returned. If you
turn feedback off, and you query returns no rows, you will simply
return to the SQL prompt.
trims
or trimspool –
normally SQL*Plus will fill out a column with blank spaces. trimspool
causes SQL*Plus to not fill spaces at the end of a line when spooling
the results.
All of the commands above are changed using the
SET command.
SQL> set pages
999 line 132 feedback off
Notice that I did not end the line with a
semicolon. You can, but it is not necessary since the commands are
not sent to the database.
To get a list of SQL*Pluscommands
type “help index”.
SQL> help index
Enter Help
[topic] for help.
@
COPY PAUSE SHUTDOWN
@@ DEFINE PRINT SPOOL
/ DEL PROMPT SQLPLUS
ACCEPT DESCRIBE QUIT START
APPEND DISCONNECT RECOVER STARTUP
ARCHIVE LOG EDIT REMARK STORE
ATTRIBUTE EXECUTE REPFOOTER TIMING
BREAK EXIT REPHEADER TTITLE
BTITLE GET RESERVED WORDS (SQL) UNDEFINE
CHANGE HELP RESERVED WORDS (PL/SQL) VARIABLE
CLEAR HOST RUN WHENEVER OSERROR
COLUMN INPUT SAVE WHENEVER
SQLERROR
COMPUTE LIST SET
CONNECT PASSWORD SHOW
For more detail use “help <command>”
SQL> help
column
COLUMN
------
Specifies display attributes for a given column, such as:
- text for
the column heading
- alignment for the column heading
- format for NUMBER data
- wrapping of column data
Also lists the
current display attributes for a single column
or all columns.
COL[UMN] [{column | expr} [option ...] ]
where option represents one of the following clauses:
ALI[AS] alias
CLE[AR]
ENTMAP {ON|OFF}
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]}
LIKE {expr | alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT] | PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]
Lets go through some of the more used SQL*Pluscommands.
The above book excerpt is from:
Easy Oracle
SQL
Get Started
Fast writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
Col. John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
