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



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  Oracle Tips by Burleson

The SPOOL Command 

The SPOOL  command causes SQL*Plus to write the results to a file.

SQL> spool /tmp/myfile.lst

Once spool is set, it will continue to spool the output until the command SPOOL  OFF.  Note that the file cannot be seen or used until the SPOOL OFF command.


The @ command tells SQL*Plus to execute a file.  The @@ command is used to run a nested script that is located in the same directory as the outer scripts.  You can run a nested script with the @ command, but you must fully qualify the file name.

The COLUMN Command

The COLUMN command is used to format the output of a column.  Once set it will format any column of that name until it is unset with the CLEAR COLUMNS command.

The TTITLE Command

The TTITLE  command sets a title that is printed at the top of each page.  Remember that “set pages” defines the page size.  If you justify a title, it will be located according to the “set line” command.  To change a title, redefine it with the TTITLE command or turn it off (TTITLE OFF).

The HOST Command

The HOST  command allows you to run operating system commands.  To get a listing of the files in the current directory use:

SQL> host dir              Windows
SQL> host ls               Linux/Unix

Typing commands at the SQL> prompt is problematic if you type as badly as I do.  I want to type in a text editor without leaving SQL*Plus.  The host command allows me to do just that. 

SQL> host notepad test.sql
SQL> host vi test.sql

Now I can edit my script using host, and then run it using @.

Let’s put all this to work.  My boss, the publisher, wants a list of authors (first and last name) and their city.  Because it is for my boss, I want the report to look professional.  If I simply query the data, it is almost unreadable as seen below.

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

-------------------- ----------------------------------------
mark                 jones
st. louis 

alvis                hester
st. louis 

erin                 weaton
st. louis 

pierre               jeckle
north hollywood 

lester               withers
pie town

Now let’s fix this up and make it look nice.  First we need to fix and size the columns.  I am going to alias the columns to c1,c2 and c3.  Then, use the COLUMN command to format each column.  I use the HOST  command to open my text editor.  I create a file called auth.sql.  Note the first line.  Two dashes 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

   author_first_name c1,
   author_last_name c2,
   author_city c3

Once I aliased the column names, the database will return the results using the c1, c2, c3 names.  I then use the COLUMN command to configure my output.  The heading simply names the column much the same as an alias would. 

The first column heading has a space in it, so I have to enclose it in quotes.  The second heading has a vertical bar which tell SQL*Plus to stack the name (see result below).  Finally, I use the FORMAT command to define how many characters wide the column is.  If you define the column width smaller that the text returned, the text is wrapped within that column.  Below are the results of running my script.

SQL>  @auth.sql

First Name      Name                           City
--------------- ------------------------------ ---------------mark            jones                          st. louis
alvis           hester                         st. louis
erin            weaton                         st. louis
pierre          jeckle                         north hollywood
lester          withers                        pie town

The FORMAT  command used above determined the width of my columns.  But what if I return a number?  You define the size of a number 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. 

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, you get pound signs.  Since SQL*Plus  can’t give you the correct number, it gives you nothing. 

But, the boss wanted the authors as last name, comma, first name, then city.  This is accomplished using concatenation (||).   To get this, I need to concatenate three items, last name, the comma, and the first name.

-- auth2.sql 

column c1 heading Name Format a30
column c2 heading City Format a20 

   author_last_name||', '||author_first_name c1,
   author_city c3

SQL>  @auth2.sql

Name                           City
------------------------------ --------------------
jones, mark                    st. louis
hester, alvis                  st. louis
weaton, erin                   st. louis
jeckle, pierre                 north hollywood
withers, lester                pie town

Don’t forget to include any spaces you desire inside the single quotes.  We will use these formatting commands throughout this book, focusing on producing readable, professional output.

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


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.