User's Guide and Reference
Part Number A82950-01
This chapter helps you learn to manipulate SQL*Plus commands, SQL commands, and PL/SQL blocks. It covers the following topics:
Read this chapter while sitting at your computer and try
out the examples shown. Before beginning, make sure you have access to
the sample tables described in Chapter 1.
You can use a number of SQL*Plus commands to edit the SQL
command or PL/SQL block currently stored in the buffer. Alternatively,
you can use a host operating system editor to edit the buffer contents.
Table 3-1 lists the SQL*Plus
commands that allow you to examine or change the command in the buffer
without re-entering the command.
adds text at the end of a line
changes old to new in a line
deletes text from a line
deletes all lines
deletes the current line
deletes line n
deletes the current line
deletes line n through the current line
deletes the last line
deletes a range of lines (m to n)
deletes the current line through line n
adds one or more lines
adds a line consisting of text
lists all lines in the SQL buffer
lists line n
lists the current line
lists line n through the current line
lists the last line
lists a range of lines (m to n)
lists the current line through line n
Suppose you want to list the current command. Use the LIST command as shown below. (If you have EXITed SQL*Plus or entered another SQL command or PL/SQL block since following the steps in Example 2-3, perform the steps in that example again before continuing.)
Notice that the semicolon you entered at the end of the SELECT
command is not listed. This semicolon is necessary to mark the end of the
command when you enter it, but SQL*Plus does not store it in the SQL buffer.
This makes editing more convenient, since it means you can append a new
line to the end of the buffer without removing a semicolon.
The SQL*Plus CHANGE command allows you to edit the current line. Various actions determine which line is the current line:
Suppose you try to select the DEPTNO column but mistakenly enter it as DPTNO. Enter the following command, purposely misspelling DEPTNO in the first line:
You see this message on your screen:
Instead of re-entering the entire command, you can correct the mistake by editing the command in the buffer. The line containing the error is now the current line. Use the CHANGE command to correct the mistake. This command has three parts, separated by slashes or any other non-alphanumeric character:
The CHANGE command finds the first occurrence in the current
line of the character sequence to be changed and changes it to the new
sequence. If you wish to re-enter an entire line, you do not need to use
the CHANGE command: re-enter the line by typing the line number followed
by a space and the new text and pressing [Return].
To change DPTNO to DEPTNO, change the line with the CHANGE command:
The corrected line appears on your screen:
Now that you have corrected the error, you can use the RUN command to run the command again:
SQL*Plus lists the command, and then runs it:
1 select deptno, ename, sal 2 from emp 3* where deptno = 10 DEPTNO ENAME SAL ---------- ---------- ---------- 10 CLARK 2450 10 KING 5000 10 MILLER 1300
To insert a line before line 1, enter a zero ("0") and follow the zero with text. SQL*Plus inserts the line at the beginning of the buffer and that line becomes line 1.
Suppose you want to add a fourth line to the SQL command you modified in Example 3-3. Since line 3 is already the current line, enter INPUT (which may be abbreviated to I) and press [Return]. SQL*Plus prompts you for the new line:
Enter the new line. Then press [Return]. SQL*Plus prompts you again for a new line:
SQL*Plus lists the modified command, and then runs it:
1 select deptno, ename, sal 2 from emp 3 where deptno = 10 4* order by sal DEPTNO ENAME SAL ---------- ---------- ---------- 10 MILLER 1300 10 CLARK 2450 10 KING 5000
To add text to the end of a line in the buffer, use the APPEND command.
To append a space and the clause DESC to line 4 of the current query, first list line 4:
Next, enter the following command (be sure to type two spaces between APPEND and DESC):
SQL*Plus lists the modified command, and then runs it:
1 select deptno, ename, sal 2 from emp 3 where deptno = 10 4* order by sal desc DEPTNO ENAME SAL ---------- ---------- ---------- 10 KING 5000 10 CLARK 2450 10 MILLER 1300
To delete lines in the buffer, use the DEL command.
Suppose you want to delete the current line to the last line inclusive. Use the DEL command as shown below.
Your computer's host operating system may have one or more
text editors that you can use to create and edit host system files. Text
editors perform the same general functions as the SQL*Plus editing commands,
but you may find them more familiar.
You can run your host operating system's default text editor without leaving SQL*Plus by entering the EDIT command:
EDIT loads the contents of the buffer into your system's
default text editor. You can then edit the text with the text editor's
commands. When you tell the text editor to save edited text and then exit,
the text is loaded back into the buffer.
To load the buffer contents into a text editor other than the default, use the SQL*Plus DEFINE command to define a variable, _EDITOR, to hold the name of the editor. For example, to define the editor to be used by EDIT as EDT, enter the following command:
You can also define the editor to be used by EDIT in your
user or site profile. See "Setting Up Your SQL*Plus
Environment" later in this chapter and the DEFINE
and EDIT commands in Chapter
8 for more information.
Through SQL*Plus, you can store one or more commands in a
file called a command file. After you create a command file, you
can retrieve, edit, and run it. Use command files to save commands for
use over time, especially complex commands or PL/SQL blocks.
You can store one or more SQL commands, PL/SQL blocks, and SQL*Plus commands in command files. You create a command file within SQL*Plus in one of three ways:
To save the current SQL command or PL/SQL block for later use, enter the SAVE command. Follow the command with a file name:
SQL*Plus adds the extension SQL to the filename to identify
it as a SQL query file. If you wish to save the command or block under
a name with a different file extension, type a period at the end of the
filename, followed by the extension you wish to use.
.sql is the file extension used by default for files saved from SQL*Plus, You can use the SQL*Plus command, SET SUFFIX extension, to set the file extension you want to use.
First, LIST the buffer contents to see your current command:
If the query shown is not in your buffer, re-enter the query now. Next, enter the SAVE command followed by the filename DEPTINFO:
You can verify that the command file DEPTINFO exists by entering the SQL*Plus HOST command followed by your host operating system's file listing command:
If you use INPUT to enter your commands, you can enter SQL*Plus
commands (as well as one or more SQL commands or PL/SQL blocks) into the
buffer. You must enter the SQL*Plus commands first, and the SQL command(s)
or PL/SQL block(s) last--just as you would if you were entering the commands
directly to the command prompt.
Suppose you have composed a query to display a list of salespeople and their commissions. You plan to run it once a month to keep track of how well each employee is doing. To compose and save the query using INPUT, you must first clear the buffer:
Next, use INPUT to enter the command (be sure not to type a semicolon at the end of the command):
SQL> INPUT 1 COLUMN ENAME HEADING SALESMAN 2 COLUMN SAL HEADING SALARY FORMAT $99,999 3 COLUMN COMM HEADING COMMISSION FORMAT $99,990 4 SELECT EMPNO, ENAME, SAL, COMM 5 FROM EMP 6 WHERE JOB = 'SALESMAN' 7
The zero at the end of the format model for the column COMM
tells SQL*Plus to display a zero instead of a blank when the value of COMM
is zero for a given row. Format models and the COLUMN command are described
in more detail in Chapter 4 and in the Oracle8i
Now use the SAVE command to store your query in a file called SALES with the extension SQL:
Note that you do not type a semicolon at the end of the query;
if you did include a semicolon, SQL*Plus would attempt to run the buffer
contents. The SQL*Plus commands in the buffer would produce an error because
SQL*Plus expects to find only SQL commands in the buffer. You will learn
how to run a command file later in this chapter.
To input more than one SQL command, leave out the semicolons
on all the SQL commands. Then, use APPEND to add a semicolon to all but
the last command. (SAVE appends a slash to the end of the file automatically;
this slash tells SQL*Plus to run the last command when you run the command
To input more than one PL/SQL block, enter the blocks one after another without including a period or a slash on a line between blocks. Then, for each block except the last, list the last line of the block to make it current and use INPUT in the following form to insert a slash on a line by itself:
You can also create a command file with a host operating system text editor by entering EDIT followed by the name of the file, for example:
Like the SAVE command, EDIT adds the filename extension SQL
to the name unless you type a period and a different extension at the end
of the filename. When you save the command file with the text editor, it
is saved back into the same file.
When you create a command file using EDIT, you can also include
SQL*Plus commands at the end of the file. You cannot do this when you create
a command file using the SAVE command because SAVE appends a slash to the
end of the file. This slash would cause SQL*Plus to run the command file
twice, once upon reaching the semicolon at the end of the last SQL command
(or the slash after the last PL/SQL block) and once upon reaching the slash
at the end of the file.
You can enter comments in a command file in three ways:
Use the REMARK command on a line by itself in the command file, followed by comments on the same line. To continue the comments on additional lines, enter additional REMARK commands. Do not place a REMARK command between different lines of a single SQL command.
REMARK Commissions report REMARK to be run monthly. COLUMN ENAME HEADING SALESMAN COLUMN SAL HEADING SALARY FORMAT $99,999 COLUMN COMM HEADING COMMISSION FORMAT $99,990 REMARK Includes only salesmen. SELECT EMPNO, ENAME, SAL, COMM FROM EMP WHERE JOB = 'SALESMAN'
The comments can span multiple lines, but cannot be nested within one another:
/* Commissions report to be run monthly. */ COLUMN ENAME HEADING SALESMAN COLUMN SAL HEADING SALARY FORMAT $99,999 COLUMN COMM HEADING COMMISSION FORMAT $99,990 SELECT EMPNO, ENAME, SAL, COMM FROM EMP WHERE JOB = 'SALESMAN' /* Includes only salesmen. */
You can use ANSI/ISO "- -" style comments within SQL statements, PL/SQL blocks, or SQL*Plus commands. Since there is no ending delimiter, the comment cannot span multiple lines. For PL/SQL and SQL, enter the comment after a command on a line, or on a line by itself:
For SQL*Plus commands, you can only include "- -" style comments if they are on a line by themselves. For example, these comments are legal:
--set maximum width for LONG to 777 SET LONG 777 -- set the heading for ENAME to be SALESMAN COLUMN ENAME HEADING SALESMAN
These comments are illegal:
If you enter the following SQL*Plus command, SQL*Plus interprets it as a comment and does not execute the command:
SQL*Plus does not have a SQL or PL/SQL command parser. It scans the first few keywords of each new statement to determine the command type, SQL, PL/SQL or SQL*Plus. Comments in some locations can prevent SQL*Plus from correctly identifying the command type, giving unexpected results. The following usage notes may help you to use SQL*Plus comments more effectively:
SQL> create or replace 2 /* hello */ 3 procedure hello 4 as 5 begin 6 null; Warning: Procedure created with compilation errors.
The location of the comment prevents SQL*Plus from recognizing the command as a PL/SQL command. SQL*Plus submits the block to the server when it sees the slash '/' at the beginning of the comment, which it interprets as the '/' statement terminator. You can avoid this error by moving the comment. For example:
SQL> SELECT 'Y' FROM DUAL; -- Testing 2 ; SELECT 'Y' FROM DUAL; -- Testing * ERROR at line 1: ORA-00911: invalid character
SQL*Plus expects no text after statement terminators on the same line and is unable to recognize the comment.
SQL> select * 2 -- comment; -- comment * ERROR at line 2: ORA-00923: FROM keyword not found where expected
The semicolon is interpreted as a statement terminator and SQL*Plus submits the partially formed SQL command to the server for processing, resulting in an error.
SQL> select * from /* this & that */ dept; Enter value for that: old 1: select * from /* this & that */ dept new 1: select * from /* this */ dept DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
SQL*Plus interprets text after the ampersand character '&' as a substitution variable and prompts for the value of the variable. You can SET DEFINE OFF to prevent scanning for the substitution character.
Just as you can save a query from the buffer to a file with the SAVE command, you can retrieve a query from a file to the buffer with the GET command:
Suppose you need to retrieve the SALES file in a later session. You can retrieve the file by entering the GET command. To retrieve the file SALES, enter
SQL> GET SALES 1 COLUMN ENAME HEADING SALESMAN 2 COLUMN SAL HEADING SALARY FORMAT $99,999 3 COLUMN COMM HEADING COMMISSION FORMAT $99,990 4 SELECT EMPNO, ENAME, SAL, COMM 5 FROM EMP 6* WHERE JOB = 'SALESMAN' 7
SQL*Plus retrieves the contents of the file SALES with the
extension SQL into the SQL buffer and lists it on the screen. Then you
can edit the command further. If the file did not contain SQL*Plus commands,
you could also execute it with the RUN command.
The START command retrieves a command file and runs the command(s) it contains. Use START to run a command file containing SQL commands, PL/SQL blocks, and/or SQL*Plus commands. Follow the START command with the name of the file:
To retrieve and run the command stored in SALES.SQL, enter
SQL*Plus runs the commands in the file SALES and displays the results of the commands on your screen, formatting the query results according to the SQL*Plus commands in the file:
EMPNO SALESMAN SALARY COMMISSION ----- --------- -------- ----------- 7499 ALLEN $1,600 $300 7521 WARD $1,250 $500 7654 MARTIN $1,250 $1,400 7844 TURNER $1,500 $0
To see the commands as SQL*Plus "enters" them, you can set
the ECHO variable of the SET command to ON. The ECHO variable controls
the listing of the commands in command files run with the START, @ and
@@ commands. Setting the ECHO variable to OFF suppresses the listing.
You can also use the @ ("at" sign) command to run a command file:
To run a command file as you start SQL*Plus, use one of the following four options:
SQL*Plus starts and runs the command file.
SQL*Plus prompts you for your password, starts, and runs
the command file.
To run a series of command files in sequence, first create a command file containing several START commands, each followed by the name of a command file in the sequence. Then run the command file containing the START commands. For example, you could include the following START commands in a command file named SALESRPT:
You can modify an existing command file in two ways:
To edit an existing command file with the EDIT command, follow the word EDIT with the name of the file. For example, to edit an existing file named PROFIT that has the extension SQL, enter the following command:
To edit an existing file using GET, the SQL*Plus editing
commands, and SAVE, first retrieve the file with GET, then edit the file
with the SQL*Plus editing commands, and finally save the file with the
SQL> GET MYREPORT 1* SELECT * FROM EMP SQL> C/*/ENAME, JOB 1* SELECT ENAME, JOB FROM EMP SQL> SAVE MYREPORT REPLACE Wrote file MYREPORT
If you want to append the contents of the buffer to the end of an existing command file, use the SAVE command and follow the filename with the word APPEND:
If your command file generates a SQL error while running
from a batch file on the host operating system, you may want to abort the
command file and exit with a return code. Use the SQL*Plus command WHENEVER
SQLERROR to do this; see the WHENEVER SQLERROR
command in Chapter 8 for more information.
You may wish to set up your SQL*Plus environment in a particular
way (such as showing the current time as part of the SQL*Plus command prompt)
and then reuse those settings with each session. You can do this through
a host operating system file called LOGIN with the file extension SQL (also
called your User Profile). The exact name of this file is system
dependent; see the Oracle installation and user's manual(s) provided for
your operating system for the precise name.
You can add any SQL commands, PL/SQL blocks, or SQL*Plus
commands to this file; when you start SQL*Plus, it automatically searches
for your LOGIN file (first in your local directory and then on a system-dependent
path) and runs the commands it finds there. (You may also have a Site Profile,
for example, GLOGIN.SQL. See for more information on the relationship of
Site and User Profiles.)
Followed by V7 or V8, sets compatibility to the version of Oracle you specify. Setting COMPATIBILITY to V7 allows you to run command files created with Oracle7.
Followed by a number format (such as $99,999), sets the default format for displaying numbers in query results.
Followed by a number, sets the number of lines per page.
Followed by ON, causes SQL*Plus to pause at the beginning of each page of output (SQL*Plus continues scrolling after you enter [Return]). Followed by text, sets the text to be displayed each time SQL*Plus pauses (you must also set PAUSE to ON).
Followed by VISIBLE, will display shift characters as a visible character. Setting SHIFTINOUT to INVISIBLE, will not display any shift characters. Note, this command can only be used with shift sensitive character sets.
Followed by ON, displays the current time before each command prompt.
You can store the current SQL*Plus system ("SET") variables
in a host operating system file (a command file) with the STORE command.
If you alter any variables, this command file can be run to restore the
original values. This is useful if you run a report that alters system
variables and you want to reset their values after the report has finished.
To store the current setting of all system variables, enter
By default, SQL*Plus adds the extension "SQL" to the file
name. If you want to use a different file extension, type a period at the
end of the file name, followed by the extension. Alternatively, you can
use the SET SUFFIX command to change the
default file extension.
To restore the stored system variables, enter
If the file has the default extension (as specified by the
SUFFIXcommand), you do not need to add the period and extension to the
To store the current values of the SQL*Plus system variables in a new command file "plusenv.sql":
Now the value of any system variable can be changed:
The original values of the system variables can then be restored from the command file:
The following features of SQL*Plus make it possible for you to set up command files that allow end-user input:
You can define variables, called user variables, for
repeated use in a single command file by using the SQL*Plus DEFINE command.
Note that you can also define user variables to use in titles and to save
you keystrokes (by defining a long string as the value for a variable with
a short name).
To define a user variable EMPLOYEE and give it the value "SMITH", enter the following command:
To confirm the definition of the variable, enter DEFINE followed by the variable name:
SQL*Plus lists the definition:
To list all user variable definitions, enter DEFINE by itself
at the command prompt. Note that any user variable you define explicitly
through DEFINE takes only CHAR values (that is, the value you assign to
the variable is always treated as a CHAR datatype). You can define a user
variable of datatype NUMBER implicitly through the ACCEPT command. You
will learn more about the ACCEPT command later in this chapter.
Suppose you want to write a query like the one in SALES (see
3-7) to list the employees with various jobs, not just those whose
job is SALESMAN. You could do that by editing a different CHAR value into
the WHERE clause each time you run the command, but there is an easier
A substitution variable is a user variable name preceded
by one or two ampersands (&). When SQL*Plus encounters a substitution
variable in a command, SQL*Plus executes the command as though it contained
the value of the substitution variable, rather than the variable itself.
For example, if the variable SORTCOL has the value JOB and the variable MYTABLE has the value EMP, SQL*Plus executes the commands
as if they were
(Here the BREAK command suppresses duplicate values of the
column named in SORTCOL. For more information about the BREAK command,
see the section "Clarifying Your Report with
Spacing and Summary Lines" in Chapter 4.)
You can use substitution variables anywhere in SQL and SQL*Plus
commands, except as the first word entered at the command prompt. When
SQL*Plus encounters an undefined substitution variable in a command, SQL*Plus
prompts you for the value.
You can enter any string at the prompt, even one containing
blanks and punctuation. If the SQL command containing the reference should
have quote marks around the variable and you do not include them there,
the user must include the quotes when prompted.
SQL*Plus reads your response from the keyboard, even if you
have redirected terminal input or output to a file. If a terminal is not
available (if, for example, you run the command file in batch mode), SQL*Plus
uses the redirected file.
After you enter a value at the prompt, SQL*Plus lists the
line containing the substitution variable twice: once before substituting
the value you enter and once after substitution. You can suppress this
listing by setting the SET command variable VERIFY to OFF.
Create a command file named STATS, to be used to calculate a subgroup statistic (the maximum value) on a numeric column:
SQL> CLEAR BUFFER SQL> INPUT 1 SELECT &GROUP_COL, 2 MAX(&NUMBER_COL) MAXIMUM 3 FROM &TABLE 4 GROUP BY &GROUP_COL 5 SQL> SAVE STATS Created file STATS
Now run the command file STATS and respond to the prompts for values as shown:
SQL> @STATS Enter value for group_col: JOB old 1: SELECT &GROUP_COL, new 1: SELECT JOB, Enter value for number_col: SAL old 2: MAX(&NUMBER_COL) MAXIMUM new 2: MAX(SAL) MAXIMUM Enter value for table: EMP old 3: FROM &TABLE new 3: FROM EMP Enter value for group_col: JOB old 4: GROUP BY &GROUP_COL new 4: GROUP BY JOB
SQL*Plus displays the following output:
If you wish to append characters immediately after a substitution variable, use a period to separate the variable from the character. For example:
is interpreted as
Suppose you wanted to expand the file STATS to include the
minimum, sum, and average of the "number" column. You may have noticed
that SQL*Plus prompted you twice for the value of GROUP_COL and once for
the value of NUMBER_COL in Example 3-12,
and that each GROUP_COL or NUMBER_COL had a single ampersand in front of
it. If you were to add three more functions--using a single ampersand before
each--to the command file, SQL*Plus would prompt you a total of four times
for the value of the number column.
You can avoid being re-prompted for the group and number
columns by adding a second ampersand in front of each GROUP_COL and NUMBER_COL
in STATS. SQL*Plus automatically DEFINEs any substitution variable preceded
by two ampersands, but does not DEFINE those preceded by only one ampersand.
When you have DEFINEd a variable, SQL*Plus substitutes the value of variable
for each substitution variable referencing variable (in the form
&variable or &&variable). SQL*Plus will not prompt
you for the value of variable in this session until you UNDEFINE
To expand the command file STATS using double ampersands and then run the file, first suppress the display of each line before and after substitution:
Now retrieve and edit STATS by entering the following commands:
SQL> GET STATS 1 SELECT &GROUP_COL, 2 MAX(&NUMBER_COL) MAXIMUM 3 FROM &TABLE 4 GROUP BY &GROUP_COL SQL> 2 2* MAX(&NUMBER_COL) MAXIMUM SQL> APPEND , 2* MAX(&NUMBER_COL) MAXIMUM, SQL> C /&/&& 2* MAX(&&NUMBER_COL) MAXIMUM, SQL> I 3i MIN(&&NUMBER_COL) MINIMUM, 4i SUM(&&NUMBER_COL) TOTAL, 5i AVG(&&NUMBER_COL) AVERAGE 6i SQL> 1 1* SELECT &GROUP_COL, SQL> C /&/&& 1* SELECT &&GROUP_COL, SQL> 7 7* GROUP BY &GROUP_COL SQL> C /&/&& 7* GROUP BY &&GROUP_COL SQL> SAVE STATS2 created file STATS2
Finally, run the command file STATS2 and respond to the prompts for values as follows:
SQL> START STATS2 Enter value for group_col: JOB Enter value for number_col: SAL Enter value for table: EMP
SQL*Plus displays the following output:
JOB MAXIMUM MINIMUM TOTAL AVERAGE ---------- ---------- ---------- ---------- --------- ANALYST 3000 3000 6000 3000 CLERK 1300 800 4150 1037.5 MANAGER 2975 2450 8275 2758.33333 PRESIDENT 5000 5000 5000 5000 SALESMAN 1600 1250 5600 1400
Note that you were prompted for the values of NUMBER_COL
and GROUP_COL only once. If you were to run STATS2 again during the current
session, you would be prompted for TABLE (because its name has a single
ampersand and the variable is therefore not DEFINEd) but not for GROUP_COL
or NUMBER_COL (because their names have double ampersands and the variables
are therefore DEFINEd).
Before continuing, set the system variable VERIFY back to ON:
You cannot use substitution variables in the buffer editing
commands, APPEND, CHANGE, DEL, and INPUT, nor in other commands where substitution
would be meaningless, such as in SQL*Plus comments (REMARK, /* ... */ or
--). The buffer editing commands, APPEND, CHANGE, and INPUT, treat text
beginning with "&" or "&&" literally, as any other text string.
Defines the substitution character (by default the ampersand "&") and turns substitution on and off.
Defines an escape character you can use before the substitution character. The escape character instructs SQL*Plus to treat the substitution character as an ordinary character rather than as a request for variable substitution. The default escape character is a backslash (\).
Lists each line of the command file before and after substitution.
Defines the character that separates the name of a substitution variable or parameter from characters that immediately follow the variable or parameter--by default the period (.).
You do this by placing an ampersand (&) followed by a
numeral in the command file in place of a substitution variable. Each time
you run this command file, START replaces each &1 in the file with
the first value (called an argument) after START filename, then
replaces each &2 with the second value, and so forth.
For example, you could include the following commands in a command file called MYFILE:
In the following START command, SQL*Plus would substitute CLERK for &1 and 7900 for &2 in the command file MYFILE:
To create a new command file based on SALES that takes a parameter specifying the job to be displayed, enter
SQL> GET SALES 1 COLUMN ENAME HEADING SALESMAN 2 COLUMN SAL HEADING SALARY FORMAT $99,999 3 COLUMN COMM HEADING COMMISSION FORMAT $99,990 4 SELECT EMPNO, ENAME, SAL, COMM 5 FROM EMP 6* WHERE JOB = 'SALESMAN' SQL> CHANGE /SALESMAN/&1 6* WHERE JOB = '&1' SQL> 1 1* COLUMN ENAME HEADING SALESMAN SQL> CHANGE /SALESMAN/&1 1* COLUMN ENAME HEADING &1 SQL> SAVE ONEJOB Created file ONEJOB
Now run the command with the parameter CLERK:
SQL*Plus lists the line of the SQL command that contains the parameter, before and after replacing the parameter with its value, and then displays the output:
old 3: WHERE JOB = '&1' new 3: WHERE JOB = 'CLERK' EMPNO CLERK SALARY COMMISSION ----- ------ -------- ----------- 7369 SMITH $800 7876 ADAMS $1,100 7900 JAMES $950 7934 MILLER $1,300
You cannot use parameters when you run a command with RUN or slash (/). You must store the command in a command file and run it with START or @.
Before continuing, return the column ENAME to its original heading by entering the following command:
Three SQL*Plus commands--PROMPT, ACCEPT, and PAUSE--help
you communicate with the end user. These commands enable you to send messages
to the screen and receive input from the user, including a simple [Return].
You can also use PROMPT and ACCEPT to customize the prompts for values
SQL*Plus automatically generates for substitution variables.
Through PROMPT and ACCEPT, you can send messages to the end
user and accept values as end-user input. PROMPT simply displays a message
you specify on-screen; use it to give directions or information to the
user. ACCEPT prompts the user for a value and stores it in the user variable
you specify. Use PROMPT in conjunction with ACCEPT when your prompt for
the value spans more than one line.
To direct the user to supply a report title and to store the input in the variable MYTITLE for use in a subsequent query, first clear the buffer:
Next, set up a command file as shown below:
SQL> INPUT 1 PROMPT Enter a title up to 30 characters long. 2 ACCEPT MYTITLE PROMPT 'Title: ' 3 TTITLE LEFT MYTITLE SKIP 2 4 SELECT * FROM DEPT 5 SQL> SAVE PROMPT1 Created file PROMPT1
Finally, run the command file, responding to the prompt for the title as shown:
SQL*Plus displays the following output:
Department Report as of 1/1/99 DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
Before continuing, turn the TTITLE command you entered in the command file off as shown below:
As you have seen in Example 3-15, SQL*Plus automatically generates a prompt for a value when you use a substitution variable. You can replace this prompt by including PROMPT and ACCEPT in the command file with the query that references the substitution variable. To create such a file, enter the commands shown:
SQL> CLEAR BUFFER buffer cleared SQL> INPUT 1 PROMPT Enter a valid employee number 2 PROMPT For example: 7369, 7499, 7521 3 ACCEPT ENUMBER NUMBER PROMPT 'Emp. no.: ' 4 SELECT ENAME, MGR, JOB, SAL 5 FROM EMP 6 WHERE EMPNO = &ENUMBER 7 SQL> SAVE PROMPT2 Created file PROMPT2
Next, run the command file. SQL*Plus prompts for the value of ENUMBER using the text you specified with PROMPT and ACCEPT:
Try entering characters instead of numbers to the prompt for "Emp. No.":
Since you specified NUMBER after the variable name in the ACCEPT command, SQL*Plus will not accept a non-numeric value. Now enter a number:
SQL*Plus displays the following output:
If you want to display a message on the user's screen and then have the user enter [Return] after reading the message, use the SQL*Plus command PAUSE. For example, you might include the following lines in a command file:
If you want to clear the screen before displaying a report (or at any other time), include the SQL*Plus CLEAR command with its SCREEN clause at the appropriate point in your command file, using the following format:
Before continuing to the next section, reset all columns to their original formats and headings by entering the following command:
Suppose that you want to be able to display the variables
you use in your PL/SQL subprograms in SQL*Plus or use the same variables
in multiple subprograms. If you declare a variable in a PL/SQL subprogram,
you cannot display that variable in SQL*Plus. Use a bind variable in PL/SQL
to access the variable from SQL*Plus.
Bind variables are variables you create in SQL*Plus and then
reference in PL/SQL. If you create a bind variable in SQL*Plus, you can
use the variable as you would a declared variable in your PL/SQL subprogram
and then access the variable from SQL*Plus. You can use bind variables
for such things as storing return codes or debugging your PL/SQL subprograms.
You create bind variables in SQL*Plus with the VARIABLE command. For example
This command creates a bind variable named ret_val with a
datatype of NUMBER. For more information, see the VARIABLE
command in Chapter 8. (To list all of the
bind variables created in a session, type VARIABLE without any arguments.)
You reference bind variables in PL/SQL by typing a colon (:) followed immediately by the name of the variable. For example
To change this bind variable in SQL*Plus, you must enter a PL/SQL block. For example
To display the value of a bind variable in SQL*Plus, you use the SQL*Plus PRINT command. For example
To declare a local bind variable named id with a datatype of NUMBER, enter
Next, put a value of "1" into the bind variable you have just created:
If you want to display a list of values for the bind variable named id, enter
You must have Oracle7, Release 7.3 or above to assign the return value of a stored function to a REFCURSOR variable.
To create, reference and display a REFCURSOR bind variable, first declare a local bind variable of the REFCURSOR datatype
Next, enter a PL/SQL block that uses the bind variable in
an OPEN ... FOR SELECT statement. This statement opens a cursor variable
and executes a query. See the PL/SQL User's
Guide and Reference for information on the OPEN command and cursor
In this example we are binding the SQL*Plus dept_sel bind variable to the cursor variable.
SQL> BEGIN 2 OPEN :dept_sel FOR SELECT * FROM DEPT; 3 END; 4 / PL/SQL procedure successfully completed.
The results from the SELECT statement can now be displayed in SQL*Plus with the PRINT command.
SQL> PRINT dept_sel DEPTNO DNAME LOC ------ ----------- --------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
A REFCURSOR bind variable is passed as a parameter to a procedure. The parameter has a REF CURSOR type. First, define the type.
SQL> CREATE OR REPLACE PACKAGE cv_types AS 2 TYPE DeptCurTyp is REF CURSOR RETURN dept%ROWTYPE; 3 END cv_types; 4 / Package created.
Next, create the stored procedure containing an OPEN ... FOR SELECT statement.
SQL> CREATE OR REPLACE PROCEDURE dept_rpt 2 (dept_cv IN OUT cv_types.DeptCurTyp) AS 3 BEGIN 4 OPEN dept_cv FOR SELECT * FROM DEPT; 5 END; 6 / Procedure created.
Execute the procedure with a SQL*Plus bind variable as the parameter.
Now print the bind variable.
SQL> PRINT odcv DEPTNO DNAME LOC ------ ----------- --------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
The procedure can be executed multiple times using the same or a different REFCURSOR bind variable.
SQL> VARIABLE pcv REFCURSOR SQL> EXECUTE dept_rpt(:pcv) PL/SQL procedure successfully completed. SQL> PRINT pcv DEPTNO DNAME LOC ------ ----------- --------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
Create a stored function containing an OPEN ... FOR SELECT statement:
SQL> CREATE OR REPLACE FUNCTION dept_fn RETURN - > cv_types.DeptCurTyp IS 2 resultset cv_types.DeptCurTyp; 3 BEGIN 4 OPEN resultset FOR SELECT * FROM DEPT; 5 RETURN(resultset); 6 END; 7 / Function created.
Execute the function.
Now print the bind variable.
SQL> PRINT rc DEPTNO DNAME LOC ------ ----------- --------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
The function can be executed multiple times using the same or a different REFCURSOR bind variable.
SQL> EXECUTE :rc := dept_fn PL/SQL procedure successfully completed. SQL> PRINT rc DEPTNO DNAME LOC ------ ----------- --------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
You can automatically get a report on the execution path
used by the SQL optimizer and the statement execution statistics. The report
is generated after successful SQL DML (that is, SELECT, DELETE, UPDATE
and INSERT) statements. It is useful for monitoring and tuning the performance
of these statements.
No AUTOTRACE report is generated. This is the default.
The AUTOTRACE report shows only the optimizer execution path.
The AUTOTRACE report shows only the SQL statement execution statistics.
The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any.
Shows the line number of each execution step.
Shows the relationship between each step and its parent. This column is useful for large reports.
Shows each step of the report.
Shows the database links or parallel query servers used.
The format of the columns may be altered with the COLUMN command. For example, to stop the PARENT_ID_PLUS_EXP column being displayed, enter
The Execution Plan output is generated using the EXPLAIN
PLAN command. For information about interpreting the output of EXPLAIN
PLAN, see the Oracle8i Designing and Tuning
for Performance guide.
For more information about the statistics and how to interpret
them, see the Oracle8i Designing and Tuning
for Performance guide.
If the SQL buffer contains the following statement:
The statement can be automatically traced when it is run:
SQL> SET AUTOTRACE ON SQL> / DNAME ENAME SAL JOB -------------- ---------- ---------- --------- ACCOUNTING CLARK 2450 MANAGER ACCOUNTING KING 5000 PRESIDENT ACCOUNTING MILLER 1300 CLERK RESEARCH SMITH 800 CLERK RESEARCH ADAMS 1100 CLERK RESEARCH FORD 3000 ANALYST RESEARCH SCOTT 3000 ANALYST RESEARCH JONES 2975 MANAGER SALES ALLEN 1600 SALESMAN SALES BLAKE 2850 MANAGER SALES MARTIN 1250 SALESMAN SALES JAMES 950 CLERK SALES TURNER 1500 SALESMAN SALES WARD 1250 SALESMAN 14 rows selected. Execution Plan ----------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN 2 1 SORT (JOIN) 3 2 TABLE ACCESS (FULL) OF 'DEPT' 4 1 SORT (JOIN) 5 4 TABLE ACCESS (FULL) OF 'EMP' Statistics ---------------------------------------------------------- 148 recursive calls 4 db block gets 24 consistent gets 6 physical reads 43 redo size 591 bytes sent via Net8 to client 256 bytes received via Net8 from client 3 Net8 roundtrips to/from client 2 sort (memory) 0 sort (disk) 14 rows processed
Your output may vary depending on the version of the server to which you are connected and the configuration of the server.
To trace the same statement without displaying the query data:
SQL> SET AUTOTRACE TRACEONLY SQL> / Execution Plan ----------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 MERGE JOIN 2 1 SORT (JOIN) 3 2 TABLE ACCESS (FULL) OF 'DEPT' 4 1 SORT (JOIN) 5 4 TABLE ACCESS (FULL) OF 'EMP' Statistics ----------------------------------------------------------- 0 recursive calls 4 db block gets 2 consistent gets 0 physical reads 0 redo size 599 bytes sent via Net8 to client 256 bytes received via Net8 from client 3 Net8 roundtrips to/from client 2 sort (memory) 0 sort (disk) 14 rows processed
To trace a statement using a database link:
SQL> SET AUTOTRACE TRACEONLY EXPLAIN SQL> SELECT * FROM EMP@MY_LINK; Execution Plan ----------------------------------------------------------- 0 SELECT STATEMENT (REMOTE) Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'EMP' MY_LINK.DB_DOMAIN
When you trace a statement in a parallel or distributed query,
the Execution Plan shows the cost based optimizer estimates of the number
of rows (the cardinality). In general, the cost, cardinality and
bytes at each node represent cumulative results. For example, the cost
of a join node accounts for not only the cost of completing the join operations,
but also the entire costs of accessing the relations in that join.
Lines marked with an asterisk (*) denote a parallel or remote
operation. Each operation is explained in the second part of the report.
See the Oracle8i Designing and Tuning for Performance
guide for more information on parallel and distributed operations.
Shows the line number of each execution step.
Describes the function of the SQL statement in the OTHER_PLUS_EXP column.
Shows the text of the query for the parallel server or remote database.
You must have Oracle7, Release 7.3 or greater to view the second section of this report.
To trace a parallel query running the parallel query option:
SQL> create table D2_t1 (unique1 number) parallel - > (degree 6); Table created. SQL> create table D2_t2 (unique1 number) parallel - > (degree 6); Table created. SQL> create unique index d2_i_unique1 on d2_t1(unique1); Index created. SQL> set long 500 longchunksize 500 SQL> set autotrace on EXPLAIN SQL> select /*+ index(b,d2_i_unique1) use_nl(b) ordered - > */ count (a.unique1) 2 from d2_t2 a, d2_t1 b 3 where a.unique1 = b.unique1;
SQL*Plus displays the following output:
Execution Plan ----------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=263 Bytes=5786) 1 0 SORT (AGGREGATE) 2 1 NESTED LOOPS* (Cost=1 Card=263 Bytes=5785) :Q8200 3 2 TABLE ACCESS* (FULL) OF 'D2_T2' :Q8200 4 2 INDEX* (UNIQUE SCAN) OF 'D2_I_UNIQUE1' (UNIQUE) :Q8200 2 PARALLEL_TO_SERIAL SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDEX(A2) PIV_SSF */ COUNT(A1.C0) FROM (SELECT/*+ ROWID(A3) */ A3."UNIQUE1" FROM "D2_T2" A3 WHERE ROWID BETWEEN :1 AND :2) A1, "D2_T1" A2 WHERE A1.C0=A2."UNIQUE1" 3 PARALLEL_COMBINED_WITH_PARENT 4 PARALLEL_COMBINED_WITH_PARENT
Lines 2, 3 and 4 are marked with asterisks, denoting parallel
operations. For example, the NESTED LOOPS step on line 2 is a PARALLEL_TO_SERIAL
operation. PARALLEL_TO_SERIAL operations execute a SQL statement to produce
output serially. Line 2 also shows that the parallel query server had the