Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

SQL*Plus COLUMN FORMAT tips

Expert Oracle Database Tips by Donald BurlesonConsulting

December 21, 2015

SQL*Plus COLUMN FORMAT tips

SQL*Plus is a command line tool that can be used with an Oracle database.  It is useful for manipulating tables or other database objects.  Once a SQL*Plus session has been established, SQL statements or SQL*Plus commands can be issued.

The SQL*Plus commands are one way a user can manipulate and submit SQL statements.  For example, these SQL*Plus commands can be used to store, retrieve, run, edit or enter SQL statements.  With SQL*Plus commands, data can be accessed and copied between SQL databases.  It also possible to list the column definitions for any table. The representative list below shows a few of the more common SQL*Plus commands.  Query results can be formatted, used for calculations, stored and printed as reports with SQL*Plus commands:

  • HELP
  • RUN
  • SAVE
  • SPOOL
  • START

More information about the SQL*Plus Command Line interface is available HERE.

SQL*Plus COLUMN FORMAT

SQL*Plus COLUMN FORMAT is one of several commands that can alter the appearance of the output.  SQL*Plus COLUMN FORMAT does not alter table structures; it only changes the format for the specified column.

Normally, SQL*Plus commands, including COLUMN FORMAT, are only effective for the current session of SQL*Plus.  To have the SQL*Plus COLUMN FORMAT command used in recurring sessions, it is possible to include COLUMN FORMAT, among other SQL*Plus commands, in a SQL script file to be executed using the SQL*Plus START command.   

The SQL*Plus COLUMN command is used to change the appearance of the data returned for a given column using the following syntax:

COLUMN column_name option1 option2 ...

Once set, the SQL*PLUS COLUMN command will format any column of the specified name until it is unset with the CLEAR COLUMNS command.

 

SQL*Plus COMMENT FORMAT Option

The SQL*Plus COMMENT FORMAT combination can be used to accomplish a number of objectives.  For example, a publisher demands a list of authors by first and last name along with their city of residence. Since this is for work, it is normal to want the report to look professional.  A simple query the data is almost unreadable as seen below.:

SQL>  SELECT
  2     author_first_name,
  3     author_last_name,
  4     author_city
  5   FROM
  6     author;
 

AUTHOR_FIRST_NAME    AUTHOR_LAST_NAME
-------------------- ----------------------------------------
AUTHOR_CITY
--------------------
mark                 jones
st. louis 

alvis                hester
st. louis 

erin                 weaton
st. louis 

pierre               jeckle
north hollywood 

lester               withers
pie town

juan                 petty
happyville

louis                clark
rose garden

minnie               mee
belaire 

dirk                 shagger
cross trax

diego                smith
tweedle

10 rows selected.

The SQL*Plus COLUMN FORMAT command can be used to fix this up and make it look nicer and be more useful to the recipient.  The first step is to fix and size the columns.  The columns are aliased to c1,c2 and c3.  Then, use the COLUMN command to format each column.  The two dashes in the first line define a comment and are ignored by SQL*Plus.

-- auth.sql 

column c1 heading "First Name" Format a15
column c2 heading Last|Name    Format a30
column c3 heading City         Format a20

 SELECT
   author_first_name c1,
   author_last_name c2,
   author_city c3
 FROM
   author;
 

The aliased column names will be returned by the database using the c1, c2, c3 names.  The COLUMNcommand is used to configure the output.  The heading simply names the column much the same as an alias would.

The first SQL*Plus COLUMN FORMAT command is:

column c1 heading "First Name" Format a15

Based on the alias, column c1 is author_first_name from the author table.  The column heading, "First Name", is enclosed in double quotes because it has a space in it.  The COLUMN FORMAT value allows 15 characters to accommodate the author's first name in the results.

Likewise, based on the alias, column c2 is author_last_name.  30 characters are allowed for this column, and the column heading includes a vertical bar, which tells SQL*Plus to stack the heading in the results.

 The result of setting the column width smaller that the text returned is that the text is wrapped within that column.  The following are the results of running the script:

SQL>  @auth.sql

                Last
First Name      Name                           City
--------------- ------------------------------ ---------------mark            jones                          st. louis
alvis           hester                         st. louis
erin            weaton                         st. louis
pierre          jeckle                         north hollywood
lester          withers                        pie town
juan            petty                          happyville
louis           clark                          rose garden
minnie          mee                            belaire
dirk            shagger                        cross trax
diego           smith                          tweedle

10 rows selected. 

The COLUMN FORMAT command was used above to determine the width of the columns.  But what if a number is returned rather than text?  The size of a number is defined using the number nine and zero.  The number nine says if there is a digit in that location then print it, if not then print nothing.  The number zero says that if there is a digit in that location then print it, otherwise print a zero. 

The following are examples of COLUMN FORMAT with numbers:

Format 9999
    2345             prints                2345
    2345.432         prints                2345
    23456            prints                ####  too large. 

Format 9999.00
    2345             prints                2345.00
    2345.432         prints                2345.43
 

If the number exceeds the size of the format, the result will be pound signs.  Since SQL*Plus cannot return the full number, it gives you nothing. 

Some of the information and examples above were excerpted from Easy Oracle SQL: Get Started Fast Writing SQL Reports with SQL*Plus.

 
Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.


 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster