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:
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.
|
