Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

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

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.