 |
|
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.
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.
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
SELECT
author_first_name c1,
author_last_name c2,
author_city c3
FROM
author;
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
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 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
SELECT
author_last_name||', '||author_first_name c1,
author_city c3
FROM
author;
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
petty, juan happyville
clark, louis rose garden
mee, minnie belaire
shagger, dirk cross trax
smith, diego tweedle
10 rows
selected.
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
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
