Go to Product Documentation Library
Library		 Go to books for this product
Product		 Go to Contents for this book
Contents		 Go to Index
Index


 

Go to previous file in sequence Go to next file in sequence

Manipulating Commands


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[*].

 

Editing Commands

Because SQL*Plus does not store SQL*Plus commands in the buffer, you edit a SQL*Plus command entered directly to the command prompt by using [Backspace] or by re-entering the command.

 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.

 
Command  Abbreviation  Purpose 
APPEND text  A text  adds text at the end of a line 
CHANGE /old/new  C /old/new  changes old to new in a line 
CHANGE /text  C /text  deletes text from a line 
CLEAR BUFFER  CL BUFF  deletes all lines 
DEL  (none)  deletes the current line 
DEL n  (none)  deletes line n 
DEL *  (none)  deletes the current line 
DEL n *  (none)  deletes line n through the current line 
DEL LAST  (none)  deletes the last line 
DEL m n  (none)  deletes a range of lines (m to n) 
DEL * n  (none)  deletes the current line through line n 
INPUT  I  adds one or more lines 
INPUT text  I text  adds a line consisting of text 
LIST  L  lists all lines in the SQL buffer 
LIST n  L n or n  lists line n 
LIST *  L *  lists the current line 
LIST n *  L n *  Lists line n through the current line 
LIST LAST  L LAST  lists the last line 
LIST m n  L m n  lists a range of lines (m to n) 
LIST * n  L * n  Lists the current line through line n 
Table 3 - 1. SQL*Plus Editing Commands 
You will find these commands useful if you mistype a command or wish to modify a command you have entered.

 

Listing the Buffer Contents

Any editing command other than LIST and DEL affects only a single line in the buffer. This line is called the current line. It is marked with an asterisk when you list the current command or block.

 Example 3-1 Listing the Buffer Contents

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

  

SQL> LIST
  1  SELECT EMPNO, ENAME, JOB, SAL
  2* FROM EMP WHERE SAL < 2500
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 add a new line to the end of the buffer without removing a semicolon from the line that was previously the last.

 

Editing the Current Line

The SQL*Plus CHANGE command allows you to edit the current line. Various actions determine which line is the current line:

 

Example 3-2 Making an Error in Command Entry

 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:

  

SQL> SELECT DPTNO, ENAME, SAL
  2 FROM EMP
  3 WHERE DEPTNO = 10;
You see this message on your screen:

  

SELECT DPTNO, ENAME, SAL
       *
ERROR at line 1:
ORA-0904:  invalid column name
Examine the error message; it indicates an invalid column name in line 1 of the query. The asterisk shows the point of error--the mistyped column DPTNO.

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

 Example 3-3 Correcting the Error

 To change DPTNO to DEPTNO, change the line with the CHANGE command:

  

SQL> CHANGE /DPTNO/DEPTNO
The corrected line appears on your screen:

  

1* SELECT DEPTNO, ENAME, SAL
Now that you have corrected the error, you can use the RUN command to run the command again:

  

SQL> RUN
SQL*Plus lists the command, and then runs it:

  

1  SELECT  DEPTNO, ENAME, SAL
2  FROM    EMP
3* WHERE   DEPTNO = 10
 DEPTNO ENAME      SALARY
------- ---------- -------
     10 CLARK       $2,450
     10 KING        $5,000
     10 MILLER      $1,300
Note that the column SAL retains the format you gave it in Example 2-4. (If you have left SQL*Plus and started again since performing Example 2-4, the column has reverted to its original format.)

 For information about the significance of case in a CHANGE command and on using wildcards to specify blocks of text in a CHANGE command, refer to CHANGE[*].

 

Adding a New Line

To insert a new line after the current line, use the INPUT command.

 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.

  

SQL> 0 SELECT EMPNO
Example 3-4 Adding a Line

 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:

  

SQL> INPUT
  4
Enter the new line. Then press [Return]. SQL*Plus prompts you again for a new line:

  

  4  ORDER BY SAL
  5
Press [Return] again to indicate that you will not enter any more lines, and then use RUN to verify and re-run the query.

 

Appending Text to a Line

To add text to the end of a line in the buffer, use the APPEND command:

 

Example 3-5 Appending Text to a Line

 To append a space and the clause DESC to line 4 of the current query, first list line 4:

  

SQL> LIST 4
  4* ORDER BY SAL
Next, enter the following command (be sure to type two spaces between APPEND and DESC):

  

SQL> APPEND  DESC
  4* ORDER BY SAL DESC
Use RUN to verify and re-run the query.

 

Deleting Lines

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.

  

SQL> DEL * LAST
DEL makes the following line of the buffer (if any) the current line.

 For more information, see the DELETE command[*].

 

Editing Commands with a System Editor

Your host computer's operating system has 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:

  

SQL> EDIT
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:

  

SQL> DEFINE _EDITOR = EDT
You can also define the editor to be used by EDIT in your user or site profile. See "Setting Up Your SQL*Plus Environment"[*] and DEFINE and EDIT[*] for more information.

 

Saving Commands for Later Use

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.

 

Storing Commands in Command Files

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:

 

Because SQL*Plus commands are not stored in the buffer, you must use one of the latter two methods to save SQL*Plus commands.

 

Creating a Command File by Saving the Buffer Contents

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

 Note that within SQL*Plus, you separate the extension from the filename with a period. Your operating system may use a different character or a space to separate the filename and the extension.

 Example 3-6 Saving the Current Command

 First, LIST the buffer contents to see your current command:

  

SQL> LIST
  1  SELECT DEPTNO, ENAME, SAL
  2  FROM EMP
  3  WHERE DEPTNO = 10
  4* ORDER BY SAL DESC
If the query shown is not in your buffer, re-enter the query now. Next, enter the SAVE command followed by the filename DEPTINFO:

  

SQL> SAVE DEPTINFO
Created file 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:

  

SQL> HOST your_host's_file_listing_command
You can use the same method to save a PL/SQL block currently stored in the buffer.

 

Creating a Command File by Using INPUT and SAVE

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.

 You can also store a set of SQL*Plus commands you plan to use with many different queries by themselves in a command file.

 Example 3-7 Saving Commands Using INPUT and SAVE

 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:

  

SQL> CLEAR 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[*].

 Now use the SAVE command to store your query in a file called SALES with the extension SQL:

  

SQL> SAVE SALES
Created file SALES
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 file.)

 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:

  

INPUT /

Creating Command Files with a System Editor

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:

  

SQL> EDIT SALES
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.

 You must include a semicolon at the end of each SQL command and a period on a line by itself after each PL/SQL block in the file. (You can include multiple SQL commands and PL/SQL blocks.)

 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.

 

Placing Comments in Command Files

You can enter comments in a command file in one of three ways:

 

Anything that is identified in one of these ways as a comment is not parsed or executed by SQL*Plus.

 Note: You cannot enter a comment on the same line after a semicolon.

 

Using the REMARK Command

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'

Using /*...*/

Enter the SQL comment delimiters, /*...*/, on separate lines in your command file, on the same line as a SQL command, or on a line in a PL/SQL block. 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. */
If you enter a SQL comment directly at the command prompt, SQL*Plus does not store the comment in the buffer.

 

Using - -

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:

  

-- Commissions report to be run monthly
DECLARE  --block for reporting monthly sales
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:

  

SET LONG 777 -- set maximum width for LONG to 777
SET -- set maximum width for LONG to 777 LONG 777
If you entered the following SQL*Plus command, it would be treated as a comment and would not be executed:

  

-- SET LONG 777

Retrieving Command Files

If you want to place the contents of a command file in the buffer, you must retrieve the command from the file in which it is stored. You can retrieve a command file using the SQL*Plus command GET.

 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:

  

SQL> GET file_name
When appropriate to the operating system, SQL*Plus adds a period and the extension SQL to the filename unless you type a period at the end of the filename followed by a different extension.

 Example 3-8 Retrieving a Command File

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

 

Running Command Files

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 word START with the name of the file:

  

START file_name
If the file has the extension SQL, you need not add the period and the extension SQL to the filename.

 Example 3-9 Running a Command File

 To retrieve and run the command stored in SALES.SQL, enter

  

SQL> START SALES
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:

  

SQL> @SALES
The @ command lists and runs the commands in the specified command file in the same manner as START. SET ECHO affects the @ command as it affects the START command.

 START, @ and @@ leave the last SQL command or PL/SQL block in the command file in the buffer.

 

Running a Command File as You Start SQL*Plus

To run a command file as you start SQL*Plus, use one of the following four options:

 

Nesting Command Files

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:

  

START Q1SALES
START Q2SALES
START Q3SALES
START Q4SALES
START YRENDSLS
Note: The @@ command may be useful in this example. See the @@ command[*] for more information.

 

Modifying Command Files

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:

  

SQL> EDIT PROFIT
Remember that EDIT assumes the file extension SQL if you do not specify one.

 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 SAVE command.

 Note that if you want to replace the contents of an existing command file with the command or block in the buffer, you must use the SAVE command and follow the filename with the word REPLACE.

For example:

  

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:

  

SQL> SAVE file_name APPEND

Exiting from a Command File with a Return Code

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 WHENEVER SQLERROR[*] for more information.

 Similarly, the WHENEVER OSERROR command may be used to exit if an operating system error occurs. See WHENEVER OSERROR[*] for more information.

 

Setting Up Your SQL*Plus Environment

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 the SQLPLUS command[*] for more information on the relationship of Site and User Profiles.)

 

Modifying Your LOGIN File

You can modify your LOGIN file just as you would any other command file. You may wish to add some of the following commands to the LOGIN file:

 
SET COMPATIBILITY  Followed by V7or V8, sets compatibility to the version of Oracle you specify. Setting COMPATIBILITY to V7 allows you to run command files created with Oracle7.
SET NUMFORMAT  Followed by a number format (such as $99,999), sets the default format for displaying numbers in query results.
SET PAGESIZE  Followed by a number, sets the number of lines per page.
SET PAUSE  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).
SET SHIFTINOUT  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.
SET TIME  Followed by ON, displays the current time before each command prompt.
See the SET command[*] for more information on these and other SET command variables you may wish to set in your SQL*Plus LOGIN file.

 

Storing and Restoring SQL*Plus System Variables

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

  

SQL> STORE SET file_name
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.

 

Restoring the System Variables

To restore the stored system variables, enter

  

SQL> START file_name
If the file has the default extension (as specified by the SET SUFFIX command), you do not need to add the period and extension to the file name.

 You can also use the @ ("at" sign) or the @@ (double "at" sign) commands to run the command file.

 Example 3-10 Storing and Restoring SQL*Plus System Variables

 To store the current values of the SQL*Plus system variables in a new command file "plusenv.sql":

  

SQL> STORE SET plusenv
Created file plusenv
Now the value of any system variable can be changed:

  

SQL> SHOW PAGESIZE
pagesize 24
SQL> SET PAGESIZE 60
SQL> SHOW PAGESIZE
pagesize 60
The original values of the system variables can then be restored from the command file:

  

SQL> START plusenv
SQL> SHOW PAGESIZE
pagesize 24

Writing Interactive Commands

The following features of SQL*Plus make it possible for you to set up command files that allow end-user input:

 

Defining User Variables

You can define variables, called user variables, for repeated use in a single command file by using the SQL*Plus command DEFINE. 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).

 Example 3-11 Defining a User Variable

 To define a user variable EMPLOYEE and give it the value "SMITH", enter the following command:

  

SQL> DEFINE EMPLOYEE = SMITH
To confirm the definition of the variable, enter DEFINE followed by the variable name:

  

SQL> DEFINE EMPLOYEE
SQL*Plus lists the definition:

  

DEFINE EMPLOYEE       = "SMITH" (CHAR)
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.

 To delete a user variable, use the SQL*Plus command UNDEFINE followed by the variable name.

 

Using Substitution Variables

Suppose you want to write a query like the one in SALES (see Example 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 way.

 By using a substitution variable in place of the value SALESMAN in the WHERE clause, you can get the same results you would get if you had written the values into the command itself.

 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

  

SQL> BREAK ON &SORTCOL
SQL> SELECT &SORTCOL, SAL
  2  FROM &MYTABLE
  3  ORDER BY &SORTCOL;
as if they were

  

SQL> BREAK ON JOB
SQL> SELECT JOB, SAL
  2  FROM EMP
  3  ORDER BY JOB;
(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"[*].)

 

Where and How to Use Substitution Variables

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.

 Example 3-12 Using Substitution Variables

 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 as shown below to the prompts for values:

  

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:

  

JOB           MAXIMUM
---------- ----------
ANALYST          3000
CLERK            1300
MANAGER          2975
PRESIDENT        5000
SALESMAN         1600
If you wish to append characters immediately after a substitution variable, use a period to separate the variable from the character. For example:

  

SQL> SELECT * FROM EMP WHERE EMPNO='&X.01';
Enter value for X:  123
will be interpreted as

  

SQL> SELECT * FROM EMP WHERE EMPNO='12301';

Avoiding Unnecessary Prompts for Values

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

 Example 3-13 Using Double Ampersands

 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:

  

SQL> SET VERIFY OFF
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:

  

SQL> SET VERIFY ON

Restrictions

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 REMARK. The buffer editing commands, APPEND, CHANGE, and INPUT, treat text beginning with "&" or "&&" literally, as any other text string.

 

System Variables

The following system variables, specified with the SQL*Plus SET command, affect substitution variables:

 
SET DEFINE  Defines the substitution character (by default the ampersand "&") and turns substitution on and off.
SET ESCAPE  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 (\).
SET VERIFY ON  Lists each line of the command file before and after substitution.
SET CONCAT  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 (.).
Refer to SET[*] for more information on these system variables.

 

Passing Parameters through the START Command

You can bypass the prompts for values associated with substitution variables by passing values to parameters in a command file through the START command.

 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:

  

SELECT * FROM EMP
WHERE JOB='&1'
AND SAL=&2
In the following START command, SQL*Plus would substitute CLERK for &1 and 7900 for &2 in the command file MYFILE:

  

SQL> START MYFILE CLERK 7900
When you use arguments with the START command, SQL*Plus DEFINEs each parameter in the command file with the value of the appropriate argument.

 Example 3-14 Passing Parameters through START

 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> START ONEJOB 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 can use any number of parameters in a command file. Within a command file, you can refer to each parameter any number of times, and can include the parameters in any order.

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

  

SQL> COLUMN ENAME CLEAR

Communicating with the User

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.

 

Prompting for and Accepting User Variable Values

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.

 Example 3-15 Prompting for and Accepting Input

 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:

  

SQL> CLEAR 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
The TTITLE command sets the top title for your report. This command is covered in detail[*].

 Finally, run the command file, responding to the prompt for the title as shown:

  

SQL> START PROMPT1
Enter a title up to 30 characters long.
Title:  Department Report as of 1/1/95
SQL*Plus displays the following output:

  

Department Report as of 1/1/95
    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:

  

SQL> TTITLE OFF

Customizing Prompts for Substitution Variable Values

If you want to customize the prompt for a substitution variable value, use PROMPT and ACCEPT in conjunction with the substitution variable, as shown in the following example.

 Example 3-16 Using PROMPT and ACCEPT in Conjunction with Substitution Variables

 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:  7123, 7456, 7890
  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:

  

SQL> START PROMPT2
Enter a valid employee number
For example:  7123, 7456, 7890
Emp. No.:
Try entering characters instead of numbers to the prompt for "Emp. No.":

  

Emp. No.:  ONE
"ONE" is not a valid number
Emp. No.:
Because you specified NUMBER after the variable name in the ACCEPT command, SQL*Plus will not accept a non-numeric value. Now enter a number:

  

Emp. No.:  7521
old   3: WHERE EMPNO = &ENUMBER
new   3: WHERE EMPNO =       7521
SQL*Plus displays the following output:

  

ENAME             MGR JOB           SALARY
---------- ---------- --------- ----------
WARD             7698 SALESMAN      $1,250

Sending a Message and Accepting [Return] as Input

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:

  

PROMPT Before continuing, make sure you have your account card.
PAUSE Press RETURN to continue.

Clearing the Screen

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:

  

CLEAR SCREEN
Before continuing to the next chapter, reset all columns to their original formats and headings by entering the following command:

  

SQL> CLEAR COLUMNS

Using Bind Variables

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.

 Because bind variables are recognized by SQL*Plus, you can display their values in SQL*Plus or reference them in other PL/SQL subprograms that you run in SQL*Plus.

 

Creating Bind Variables

You create bind variables in SQL*Plus with the VARIABLE command. For example

  

VARIABLE ret_val NUMBER
This command creates a bind variable named ret_val with a datatype of NUMBER. See VARIABLE[*]. (To list all of the bind variables created in a session, type VARIABLE without any arguments.)

 

Referencing Bind Variables

You reference bind variables in PL/SQL by typing a colon (:) followed immediately by the name of the variable. For example

  

:ret_val := 1;
This command assigns a value to the bind variable named ret_val.

 

Displaying Bind Variables

To display the value of a bind variable in SQL*Plus, you use the SQL*Plus PRINT command. For example

  

PRINT ret_val
This command displays a bind variable named ret_val. See PRINT[*].

 Example 3-17 Creating, Referencing, and Displaying Bind Variables

 To declare a local bind variable named id with a datatype of NUMBER, enter

  

VARIABLE id NUMBER
Next, put a value of "1" into the bind variable you have just created:

  

BEGIN
:id := 1;
END;
If you want to display a list of values for the bind variable named id, enter

  

PRINT id
Try creating some new departments using the variable:

  

EXECUTE :id := dept_management.new('ACCOUNTING','NEW YORK')
EXECUTE :id := dept_management.new('RESEARCH','DALLAS')
EXECUTE :id := dept_management.new('SALES','CHICAGO')
EXECUTE :id := dept_management.new('OPERATIONS','BOSTON')
PRINT id
COMMIT
Note: dept_management.new refers to a PL/SQL function, "new", in a package (dept_management). The function "new" adds the department data to a table.

 

Using REFCURSOR Bind Variables

SQL*Plus REFCURSOR bind variables allow SQL*Plus to fetch and format the results of a SELECT statement contained in a PL/SQL block.

 REFCURSOR bind variables can also be used to reference PL/SQL cursor variables in stored procedures. This allows you to store SELECT statements in the database and reference them from SQL*Plus.

 A REFCURSOR bind variable can also be returned from a stored function.

 Note: You must have Oracle7, Release 7.3 or above to assign the return value of a stored function to a REFCURSOR variable.

 Example 3-18 Creating, Referencing, and Displaying REFCURSOR Bind Variables

 To create, reference and display a REFCURSOR bind variable, first declare a local bind variable of the REFCURSOR datatype

  

SQL> VARIABLE dept_sel REFCURSOR
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 variables.

 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
The PRINT statement also closes the cursor. To reprint the results, the PL/SQL block must be executed again before using PRINT.

 Example 3-19 Using REFCURSOR Variables in Stored Procedures

 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 successfully completed.
Execute the procedure with a SQL*Plus bind variable as the parameter.

  

SQL> VARIABLE odcv REFCURSOR
SQL> EXECUTE dept_rpt(:odcv)
PL/SQL procedure successfully completed.
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
Example 3-20 Using REFCURSOR Variables in Stored Functions

 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.

  

SQL> VARIABLE rc REFCURSOR
SQL> EXECUTE :rc := dept_fn
PL/SQL procedure successfully completed.
Now print the bind variable.

  

SQL> PRINT rc
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
4 rows selected.
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
4 rows selected.

Tracing Statements

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.

 

Controlling the Report

You can control the report by setting the AUTOTRACE system variable.

  
SET AUTOTRACE OFF
 No AUTOTRACE report is generated. This is the default.  
SET AUTOTRACE ON EXPLAIN
 The AUTOTRACE report shows only the optimizer execution path.  
SET AUTOTRACE ON STATISTICS
 The AUTOTRACE report shows only the SQL statement execution statistics.  
SET AUTOTRACE ON
 The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.  
SET AUTOTRACE TRACEONLY
 Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any. 
To use this feature, you must have the PLUSTRACE role granted to you and a PLAN_TABLE table created in your schema. For more information on the PLUSTRACE role and PLAN_TABLE table, see the AUTOTRACE variable of the SET command[*].

 

Execution Plan

The Execution Plan shows the SQL optimizer's query execution path. Both tables are accessed by a full table scan, sorted, and then merged.

 Each line of the Execution Plan has a sequential line number. SQL*Plus also displays the line number of the parent operation.

 The Execution Plan consists of four columns displayed in the following order:

 
Column Name  Description 
ID_PLUS_EXP  Shows the line number of each execution step. 
PARENT_ID_PLUS_EXP  Shows the relationship between each step and its parent. This column is useful for large reports. 
PLAN_PLUS_EXP  Shows each step of the report. 
OBJECT_NODE_PLUS_EXP  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

  

SQL> COLUMN PARENT_ID_PLUS_EXP NOPRINT
The default formats can be found in the site profile (for example, glogin.sql).

 The Execution Plan output is generated using the EXPLAIN PLAN command. For information about interpreting the output of EXPLAIN PLAN, see the Oracle8 Server Tuning guide.

 

Statistics

The statistics are recorded by the server when your statement executes and indicate the system resources required to execute your statement.

 The client referred to in the statistics is SQL*Plus. SQL*Net refers to the generic process communication between SQL*Plus and the server, regardless of whether SQL*Net is installed.

 You cannot change the default format of the statistics report.

 For more information about the statistics and how to interpret them, see the Oracle8 Server Tuning guide.

 Example 3-21 Tracing Statements for Performance Statistics and Query Execution Path

 If the SQL buffer contains the following statement:

  

SQL> SELECT D.DNAME, E.ENAME, E.SAL, E.JOB
  2  FROM EMP E, DEPT D
  3  WHERE E.DEPTNO = D.DEPTNO
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 SQL*Net to client
        256  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sort (memory)
          0  sort (disk)
         14  rows processed
Note: Your output may vary depending on the version of the server to which you are connected and the configuration of the server.

 Example 3-22 Tracing Statements Without Displaying Query Data

 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 SQL*Net to client
        256  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sort (memory)
          0  sort (disk)
         14  rows processed
This option is useful when you are tuning a large query, but do not want to see the query report.

 Example 3-23 Tracing Statements Using a Database Link

 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
The Execution Plan shows the table being accessed on line 1 is via the database link MY_LINK.DB_DOMAIN.

 

Tracing Parallel and Distributed Queries

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 Oracle8 Server Tuning guide for more information on parallel and distributed operations.

 The second section of this report consists of three columns displayed in the following order:

 
Column Name  Description 
ID_PLUS_EXP  Shows the line number of each execution step. 
OTHER_TAG_PLUS_EXP  Describes the function of the SQL statement in the OTHER_PLUS_EXP column. 
OTHER_PLUS_EXP  Shows the text of the query for the parallel server or remote database. 
The format of the columns may be altered with the COLUMN command. The default formats can be found in the site profile (for example, glogin.sql).

 Note: You must have Oracle7, Release 7.3 or greater to view the second section of this report.

 Example 3-24 Tracing Statements With Parallel Query Option

 To trace a parallel query running the parallel query option:

  

SQL> CREATE TABLE T2_T1 (UNIQUE1 NUMBER) PARALLEL -
>    (DEGREE 6);

Table created.

SQL> CREATE TABLE T2_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
Line 0 of the Execution Plan shows the cost based optimizer estimates the number of rows at 263, taking 5786 bytes. The total cost of the statement is 1.

 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 identifier Q8200.

 


 
Go to previous file in sequence Go to next file in sequence
Prev Next		 Oracle
Copyright © 1997 Oracle Corporation. 
All Rights Reserved.		 Go to Product Documentation Library
Library		 Go to books for this product
Product		 Go to Contents for this book
Contents		 Go to Index
Index