This section introduces some of the advanced features of
SQL*Plus including editing the SQL command buffer,
formatting output from SQL SELECT statements, saving
the output from SQL statements and collecting
performance statistics on the execution of SQL
statements.
SQL*Plus has several commands to allow the user to edit or modify
SQL statements. Once a new SQL statement has been typed in (ending
with a ;) this statement is placed into a buffer and is
considered to be the current SQL statement. All of the
following commands operate on the current SQL statement in the
buffer.
/ - Execute the current SQL statement in the buffer
APPEND - Add text to the end of the current line of the SQL statement in the buffer
CHANGE - Replace text on the current line of the SQL statement with new text
CLEAR - Clear the buffer
DEL - Delete the current line in the buffer
INPUT - Add one or more lines to the SQL statement in the buffer
LIST - List the current SQL statement in the buffer
RUN - Execute the current SQL statement in the buffer
SAVE - Save the current SQL statement to a script file
START - Load a SQL statement located in a script file and then run that SQL statement
SQL statements may be typed with a free format. Spaces and
<cr> characters may be used to separate key words in a SQL
statement. SQL*Plus displays line numbers in the left hand margin
indicating the current line for a SQL statement that spans multiple
lines.
In the following example, an erroneous SQL statement has been
entered. After the ; was typed, an error message was
displayed indicating the approximate location of the error and
a brief error message description.
SQL> SELECT tname, tabtype
2 FRO
3 tab;
FRO
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
To correct line number 2, the user can type the line number
followed by the correct portion of the SQL statement. This corrects
the SQL statement in the buffer. The last step is to execute the
SQL statement in the buffer by typing the RUN command.
SQL> 2 FROM
SQL> RUN
TNAME TABTYPE
------------------------------ -------
MACHINE TABLE
EMPLOYEE TABLE
The LIST command can be used to display the current contents of the
SQL buffer. An asterisk (*) is used to mark the current line
of the SQL statement within the buffer.
SQL> LIST
1 SELECT tname, tabtype
2 FROM
3* tab
The current line of the SQL statement in the buffer can be appended
using the APPEND command. The syntax is: APPEND new text. In
the following example the new text ``xyz'' is appended to line
number 3 which is the current line in the buffer.
SQL> LIST
1 SELECT tname, tabtype
2 FROM
3* tab
SQL> APPEND xyz
3* tabxyz
SQL> LIST
1 SELECT tname, tabtype
2 FROM
3* tabxyz
Text on a line in the SQL statement can also be replaced using the
CHANGE command. The syntax for the CHANGE command is: CHANGE /
old text / new text /
In the following example, text on the current line number 3 will be
replaced with blank text:
SQL> LIST
1 SELECT tname, tabtype
2 FROM
3* tabxyz
SQL> CHANGE/xyz//
3* tab
SQL> LIST
1 SELECT tname, tabtype
2 FROM
3* tab
To move to a different line of the SQL statement in the buffer, simply type the line number.
SQL> LIST
1 SELECT tname, tabtype
2 FROM
3* tab
SQL> 2
2* FROM
SQL> LIST
1 SELECT tname, tabtype
2* FROM
3 tab
The DEL command can be used to delete the current line of the SQL
statement out of the buffer as in the following example.
SQL> LIST
1 SELECT tname, tabtype
2* FROM
3 tab
SQL> DEL
2
SQL> LIST
1 SELECT tname, tabtype
2* tab
A SQL statement in the buffer can be saved to a file for later use.
The SAVE command serves this purpose. The syntax for the SAVE
command is: SAVE filename
In this example, the current contents of the buffer are saved to a
file called query.sql:
SQL> LIST
1 SELECT tname, tabtype
2* FROM
3 tab
SQL> SAVE query.sql
A directory and/or drive letter (for those using MS DOS or
MS Windows) can be placed in front of the file name in
order to re-direct the file to another drive or directory.
For example, to save the current statement to a floppy disk:
SQL> SAVE a:\query.sql
A SQL statement saved in a file can then be loaded and executed
using the START command. The syntax for the START command is: START
filename
Here, the file query.sql created in the previous example is
loaded and executed using the START command:
Again, a drive letter and/or directory name can be
placed in front of the file name.
In many cases, it is easiest to create and edit
a set of text files containing the queries and
then use the START command to execute them.
Instructions for this vary depending on the
operating system. For example, under a UNIX
system, one can use a text editor such as
VI, Emacs or Pico
to create text files with the create statements to
create the tables, insert statements to add data and
select statements to perform some queries.
Under MS Windows 95 or NT, one can use the
Windows NotePad editor to create these same
types of files. If the files are stored on a
floppy disk (for example, the a:
drive), then the START command can be used
as follows:
SQL> START a:\query.sql
When working with SQL statements and SQL*Plus
commands in a script file, be sure and make
backups of your disks and files.
SQL*Plus contains several commands that can
alter the appearance of the output. These commands
are only in effect for the current SQL*Plus session.
They can also be included in SQL script files and
can be executed using the START command.
The formatting commands include:
BREAK - Set the formatting behavior for the output of SQL statements
BTITLE - Place a title on the bottom of each page in the printout from a SQL statement
COLUMN - Change the appearance of an output column from a query
REMARK - Place a comment following the REMARK keyword
SET - Set a SQL*Plus variable to a new value
SHOW - Show the current value of a SQL*Plus variable
TTITLE - Place a title on the top of each page in the printout from a SQL statement
UNDEFINE - Delete a user defined variable
Note that none of these SQL*Plus formatting commands
changes the underlying table structures.
Perhaps the most useful command is COLUMN
which changes the appearance of data for a given column.
The syntax for the COLUMN command is as
follows:
COLUMN column_name option1 option2 ...
Where option can be one or more of the following:
FORMAT format
- Changes the format for the column.
For example, to only display the first 10 characters of
an employee's last name (column LNAME), use the following:
COLUMN lname FORMAT A10
This indicates to format the lname column as an Ascii
column with only 10 characters.
Numbers can be formatted using "9" to indicate digits.
For example:
COLUMN salary FORMAT $9,999,990.99
HEADING heading_text - changes the heading for a column.
JUSTIFY LEFT or JUSTIFY CENTER or
JUSTIFY RIGHT - aligns the output with the left, center or right of the column.
NULL text - Indicates the text that
should be displayed in place of a NULL value.
WRAPPED or WORD_WRAPPED or TRUNCATED
- Indicates how text that is longer than the displayed column
width should be handled. TRUNCATED means
it will be cut off at the maximum width of the field.
WRAPPED will wrap the value down to the
next line of output. WORD_WRAPPED does the same
as Wrapped but breaks the value up on white space.
The SET and SHOW commands can
also be useful. To see a listing of all of the SQL*Plus
variables, type SHOW ALL
SQL> show all
appinfo is ON and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autotrace OFF
shiftinout INVISIBLE
blockterminator "." (hex 2e)
btitle OFF and is the 1st few characters of the next SELECT
cmdsep OFF
colsep " "
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
copytypecheck is ON
define "&" (hex 26)
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
feedback ON for 6 or more rows
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
linesize 100
lno 24
loboffset 1
long 80
longchunksize 80
newpage 1
null ""
numformat ""
numwidth 9
pagesize 24
pause is OFF
pno 0
recsep WRAP
recsepchar " " (hex 20)
release 800030000
repfooter OFF and is NULL
repheader OFF and is NULL
serveroutput OFF
showmode OFF
spool OFF
sqlcase MIXED
sqlcode 0
sqlcontinue "> "
sqlnumber ON
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "SQL"
tab ON
termout ON
time OFF
timing OFF
trimout ON
trimspool OFF
ttitle OFF and is the 1st few characters of the next SELECT
underline "-" (hex 2d)
user is "TUCANO"
verify ON
wrap : lines will be wrapped
Some of the SQL*Plus variables of interest include
BTITLE and TTITLE described above,
Other useful variables include:
SET COLSEP - The separator characters between columns
(default is a space)
SET ECHO - Determines if SQL*Plus commands should be
echoed or not. The default is OFF
meaning SQL*Plus commands will not be echoed.
SET HEADING - Determines if column headings should be displayed
or not. Default is ON.
SET LINESIZE - Determines the maximum number of characters
in a line of output. The default is 80.
SET LONG - Determines how much data in a LONG
column will be displayed. The default is 80
bytes.
SET NULL - Determines what value should be displayed in place of
a NULL value.
SET PAGESIZE - The number of lines for one page (before
the headers repeat). The default is 24 lines.
SET PAUSE - Determines if output should be paused after
PAGESIZE lines have been displayed.
The default is OFF.
SET TRIMOUT - Determines if each line should be padded with
blanks out to a length of LINESIZE.
The default is ON meaning the output will
be trimmed (e.g., will not be padded with spaces).
SET WRAP - Determines if the output will be wrapped to the next
line or truncated if the line is longer than
LINESIZE. The default is ON
meaning long output will be wrapped.
The following example shows an SQL*Plus script file
(myquery.sql) that
utilizes some of the above formatting commands. The output
of this script is displayed afterwards.
TTITLE 'Employees, Departments and Department Managers'
SET PAGESIZE 36
COLUMN address FORMAT A20 WORD_WRAPPED
COLUMN dept_manager FORMAT A13 WORD_WRAPPED HEADING 'Dept. Manager'
COLUMN dno FORMAT 999
SELECT employee.fname, employee.lname, employee.address,
employee.dno, department.dname,
employee2.fname || ' ' || employee2.lname dept_manager
FROM employee, department, employee employee2
WHERE employee.dno = department.dnumber
AND department.mgrssn = employee2.ssn ;
When the above script is executed, the following output is displayed:
SQL> START a:\myquery.sql
Mon Jan 05
Employees, Departments and Department Managers
FNAME LNAME ADDRESS DNO DNAME Dept. Manager
-------- -------- -------------------- --- -------------- -------------
JOHN SMITH 731 FONDREN, 5 RESEARCH FRANKLIN WONG
HOUSTON, TX
FRANKLIN WONG 638 VOSS,HOUSTON TX 5 RESEARCH FRANKLIN WONG
RAMESH NARAYAN 975 FIRE OAK, 5 RESEARCH FRANKLIN WONG
HUMBLE, TX
JOYCE ENGLISH 5631 RICE, HOUSTON, 5 RESEARCH FRANKLIN WONG
TX
JAMES BORG 450 STONE, HOUSTON, 1 HEADQUARTERS JAMES BORG
TX
ALICIA ZELAYA 3321 CASTLE, SPRING, 4 ADMINISTRATION JENNIFER
TX WALLACE
JENNIFER WALLACE 291 BERRY, BELLAIRE, 4 ADMINISTRATION JENNIFER
TX WALLACE
AHMAD JABBAR 980 DALLAS, HOUSTON, 4 ADMINISTRATION JENNIFER
TX WALLACE
8 rows selected.
SQL*Plus has a command called SPOOL that can send the
output from any SQL statement to a file. Indeed, anything
that is displayed in SQL*Plus can be echoed to this spool file.
The SPOOL command is invoked with the
name of a file that will contain the output.
Once this has been executed, the output from
all subsequent SQL statements will be copied to
the file. To end capturing the output, issue the
SPOOL OFF command.
The following is an example:
SQL> SPOOL a:\myfile.out
SQL> SELECT * from EMPLOYEE;
etc. Any SQL statements typed here will show up in the output.
SQL> SPOOL OFF
The SPOOL OFF command turns the output off.
Everything between SPOOL a:\myfile.out and
SPOOL OFF will be in the
file myfile.out. This is a simple ASCII text file
that can be read by Windows Notepad, MS Word, or
just about any word processor, e-mail package, etc.
To print, load this file into MS Word, set the font to
Courier and print as you would with any other
document.
Note that some SQL*Plus commands will not show up in the
SPOOL file. To have them echo to the SPOOL file, use the
SET ECHO ON option.
Also, when SPOOLing to a file, SQL*Plus makes each line
80 characters long by padding with spaces. This can be shortened
to fewer characters using the SET LINESIZE
option. For example, SET LINESIZE 70 will pad
each line of output to 70 characters.
The SET TRIMOUT and SET TABS
options offer other ways to change the spooled output.
SQL*Plus has several commands that can be used to
prompt the user for input, accept input from the
user and store it in a variable, and then use
that variable in a query.
The following example shows the prompt/accept
sequence for a query.
PROMPT Type the department you are looking for
ACCEPT dept NUMBER PROMPT "Department Number: "
SELECT fname, lname, dno
FROM employee
WHERE dno = &dept;
When this script is executed, the following output is shown:
SQL> START a:\empquery.sql
Type the department you are looking for
Department Number: 5
old 3: WHERE dno = &dept
new 3: WHERE dno = 5
FNAME LNAME DNO
-------- -------- ---------
JOHN SMITH 5
FRANKLIN WONG 5
RAMESH NARAYAN 5
JOYCE ENGLISH 5
In the above example, the user typed "5" in response to the
Department Number: prompt.
The first PROMPT command simply echoes out a line
to the display. The second command,
ACCEPT, accepts input from the user. In this case,
the variable that will hold the input is
called dept. The input should be
of type NUMBER, and the PROMPT
Department Number: should be displayed.
Once the user types the department number and
presses enter, the variable dept
takes on the value. The following two lines
(starting with old and new) are verifying the
values used for the dept variable.
To suppress the display of this verification, use
the SET VERIFY OFF command before
running the script or as one of the first
commands in the script.
In the following example, additional commands have
been added including the SET VERIFY and REMARK
commands to improve the script.
REMARK This script accepts a department number as
REMARK input from the user and then displays the
REMARK last name, first name, address and department number
REMARK of the employees in that department
REMARK Turn off VERIFY
SET VERIFY OFF
REMARK Format some columns
COLUMN dno FORMAT 99999 HEADING 'Dept.|Number'
COLUMN address FORMAT A25 HEADING 'Address'
COLUMN fname FORMAT A10 HEADING 'First|Name'
COLUMN lname FORMAT A12 HEADING 'Last|Name'
REMARK Prompt the user and get a department number
PROMPT Type the department you are looking for
ACCEPT dept NUMBER PROMPT "Department Number: "
REMARK Perform the query
SELECT fname, lname, address, dno
FROM employee
WHERE dno = &dept;
When this script is executed, the following output is shown:
SQL> START a:\empquery.sql
Type the department you are looking for
Department Number: 4
First Last Dept.
Name Name Address Number
---------- ------------ ------------------------- ------
ALICIA ZELAYA 3321 CASTLE, SPRING, TX 4
JENNIFER WALLACE 291 BERRY, BELLAIRE, TX 4
AHMAD JABBAR 980 DALLAS, HOUSTON, TX 4
SQL*Plus has several commands that monitor the execution of
SQL statements. The commands can be used to gather
statistical information for performance monitoring
purposes.
The first command is called AUTOTRACE and
is used to trace the execution plan for an SQL
statement. To use AUTOTRACE, a special table
must be created in the schema to hold the statistical
information. Execute the following CREATE TABLE
command in your schema:
This table need only be created once. No
data is permanently stored in PLAN_TABLE
so it will not take much if any space.
To check the execution plan for each SQL
statement, turn the AUTOTRACE
option on with the following SQL*Plus command:
SET AUTOTRACE ON
Then execute an SQL statement:
SQL> SELECT * FROM employee;
FNAME MI LNAME SSN BDATE ADDRESS
-------- -- -------- --------- --------- ------------------------
JOHN B SMITH 123456789 09-JAN-55 731 FONDREN, HOUSTON, TX
FRANKLIN T WONG 333445555 08-DEC-45 638 VOSS,HOUSTON TX
ALICIA J ZELAYA 999887777 19-JUL-58 3321 CASTLE, SPRING, TX
JENNIFER S WALLACE 987654321 20-JUN-31 291 BERRY, BELLAIRE, TX
RAMESH K NARAYAN 666884444 15-SEP-52 975 FIRE OAK, HUMBLE, TX
JOYCE A ENGLISH 453453453 31-JUL-62 5631 RICE, HOUSTON, TX
AHMAD V JABBAR 987987987 29-MAR-59 980 DALLAS, HOUSTON, TX
JAMES E BORG 888665555 10-NOV-27 450 STONE, HOUSTON, TX
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'EMPLOYEE'
Statistics
----------------------------------------------------------
0 recursive calls
3 db block gets
2 consistent gets
0 physical reads
0 redo size
1891 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
8 rows processed
Notice the regular output from the SQL command is
given followed by the Execution plan and the statistics.
To turn off AUTOTRACE, issue the following command:
SET AUTOTRACE OFF
The next useful command for performance
monitoring is called TIMING.
This command starts a timer that can be
read at any interval, similar to how a stopwatch
operates. To set up a timer, issue the following
command:
SQL> TIMING START select_emp
SQL> SELECT * FROM employee ;
FNAME MI LNAME SSN BDATE
-------- -- -------- --------- ---------
JOHN B SMITH 123456789 09-JAN-55
FRANKLIN T WONG 333445555 08-DEC-45
ALICIA J ZELAYA 999887777 19-JUL-58
JENNIFER S WALLACE 987654321 20-JUN-31
RAMESH K NARAYAN 666884444 15-SEP-52
JOYCE A ENGLISH 453453453 31-JUL-62
AHMAD V JABBAR 987987987 29-MAR-59
JAMES E BORG 888665555 10-NOV-27
8 rows selected.
SQL> TIMING SHOW select_emp
timing for: select_emp
real: 1760
Thus the above query took 1.76 seconds to complete.
To stop a timer, issue the TIMING STOP
command. Note that AUTOTRACE and TIMING should probably not
be used in conjunction as it would be difficult to separate
the execution time for the SQL statement from the
time taken to generate the plan and statistics.
In the previous examples of SQL statements, the default format
of data of type DATE has been in the form: DD-MON-YY
The TO_CHAR and TO_DATE functions can be used to
convert dates to other formats, however, this may
become inconvenient, especially when inserting
a large number of rows.
The ALTER SESSION statement can be used to alter various
characteristics of the current SQL*Plus session including
the default date format. This statement is often used to
format dates to conform to regional customs. The
syntax of ALTER SESSION for use with changing the
default date format is as follows:
ALTER SESSION
SET NLS_DATE_FORMAT =
The date_format can include the following codes:
YY
A 2 digit year such as 98.
YYYY
A 4 digit year such as 1998.
NM
A month number.
MONTH
The full name of the month.
MON
The abbreviated month (Jan, Feb, Mar).
DDD
The day of the year. For use is Julian dates.
DD
The day of the month.
D
The day of the week.
DAY
The name of the day.
HH
The hour of the day (12 hour clock)
HH24
The hour of the day (24 hour clock)
MI
The minutes.
SS
The seconds.
For example, to change the default date to include a full
four digit year, issue the following ALTER SESSION
statement:
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'
From this point, all INSERT, UPDATE and DELETE statements
must format the date accordingly. Also, any SELECT statements
will return the date formatted accordingly.
Note that this change only remains in effect for the
current session. Logging out of SQL*Plus and logging back in
(or re-connecting to the Oracle database using the
connect command) will reset the date format
back to its default.
