Purpose Syntax Terms and Clauses
Usage Notes
Example
LIST
Lists one or more lines of the SQL buffer.
L[IST] [n|n m|n *|n LAST|*|* n|* LAST|LAST]
Refer to the following list for a description of each term or clause: Lists line n.
Lists lines n through m.
Lists line n through the current line. Lists line n through the last line. Lists the current line.
Lists the current line through line n. Lists the current line through the last line. Lists the last line.
Enter LIST with no clauses to list all lines.
The last line listed becomes the new current line (marked by an asterisk).
To list the contents of the buffer, enter
SQL> LIST
You will see a listing of all lines in the buffer, similar in form to the following example:
1 SELECT ENAME, DEPTNO, JOB 2 FROM EMP
3 WHERE JOB = ’CLERK’ 4* ORDER BY DEPTNO
The asterisk indicates that line 4 is the current line. To list the second line only, enter
SQL> LIST 2
You will then see this:
2* FROM EMP n n m n * n LAST * * n * LAST LAST
To list the current line (now line 2) to the last line, enter
SQL> LIST * LAST
You will then see this:
2 FROM EMP
3 WHERE JOB = ’CLERK’ 4* ORDER BY DEPTNO
Purpose
Syntax Terms and Clauses
Usage Notes
Example
PASSWORD
Allows you to change a password without echoing the password on an input device.
PASSW[ORD] [username]
Refer to the following for a description of the clause or term: Specifies the user. If you do not specify a username, username defaults to the current user. To change the password of another user, you must have been granted the appropriate privilege.
For more information about changing your password, see the CONNECT command in this chapter.
Suppose you are logged on as scott/tiger, and want to change the password to tigertiger
SQL> passw
Changing password for scott Old password: tiger
New password: tigertiger
Retype new password: tigertiger Password changed
SQL>
Suppose you are logged on as a DBA, and want to change the password for user usera (currently identified by passa) to passusera
SQL> passw usera
Changing password for usera New password: passusera
Retype new password: passusera Password changed
SQL> username
Purpose
Syntax Terms and Clauses
Usage Notes
Example
PAUSE
Displays an empty line followed by a line containing text, then waits for the user to press [Return], or displays two empty lines and waits for the user’s response.
PAU[SE] [text]
Refer to the following for a description of the clause or term: Represents the text you wish to display. Enter PAUSE followed by no text to display two empty lines.
Because PAUSE always waits for the user’s response, it is best to use a message that tells the user explicitly to press [Return].
PAUSE reads input from the terminal (if a terminal is available) even when you have designated the source of the command input as a file. For information on pausing between pages of a report, see the PAUSE variable of the SET command later in this chapter.
To print “Adjust paper and press RETURN to continue.” and to have SQL*Plus wait for the user to press [Return], you might include the following PAUSE command in a command file:
SET PAUSE OFF
PAUSE Adjust paper and press RETURN to continue. SELECT ...
Purpose
Syntax Terms and Clauses
Usage Notes
Example
Displays the current value of bind variables. For more information on bind variables, see your PL/SQL User’s Guide and Reference.
PRI[NT] [variable ...]
Refer to the following for a description of the clause or term:
Represents the names of the bind variables whose values you wish to display.
Enter PRINT with no variables to print all bind variables.
Bind variables are created using the VARIABLE command. For more information and examples, see the VARIABLE command in this chapter.
You can control the formatting of the PRINT output just as you would query output. For more information, see the formatting techniques described in Chapter 4.
To automatically display bind variables referenced in a successful PL/SQL block or used in an EXECUTE command, use the
AUTOPRINT clause of the SET command. For more information, see the SET command in this chapter.
The following example illustrates a PRINT command:
SQL> VARIABLE n NUMBER SQL> BEGIN 2 :n := 1; 3 END; SQL> PRINT n N –––––––––– 1 variable ...
Purpose Syntax Terms and Clauses
Usage Notes
Example
PROMPT
Sends the specified message or a blank line to the user’s screen.
PROMPT [text]
Refer to the following for a description of the term or clause: Represents the text of the message you wish to display. If you omit text, PROMPT displays a blank line on the user’s screen.
You can use this command in command files to give information to the user.
The following example shows the use of PROMPT in conjunction with ACCEPT in a command file called ASKFORDEPT. ASKFORDEPT contains the following SQL*Plus and SQL commands:
PROMPT
PROMPT Please enter a valid department PROMPT For example: 10, 20, 30, 40 ACCEPT NEWDEPT NUMBER PROMPT ’DEPT:> ’ SELECT DNAME FROM DEPT
WHERE DEPTNO = &NEWDEPT
Assume you run the file using START or @:
SQL> @ASKFORDEPT
SQL*Plus displays the following prompts:
Please enter a valid department For example: 10, 20, 30, 40 DEPT:>
You can enter a department number at the prompt DEPT:>. By default, SQL*Plus lists the line containing &NEWDEPT before and after
substitution, and then displays the department name corresponding to the number entered at the DEPT:> prompt.
Purpose
Syntax Usage Notes
Example
REMARK
Begins a comment in a command file. SQL*Plus does not interpret the comment as a command.
REM[ARK]
The REMARK command must appear at the beginning of a line, and the comment ends at the end of the line. A line cannot contain both a comment and a command.
For details on entering comments in command files using the SQL comment delimiters, /* ... */, or the ANSI/ISO comment delimiter, – –
..., refer to “Placing Comments in Command Files” in Chapter 3.
The following command file contains some typical comments:
REM COMPUTE uses BREAK ON REPORT to break on end of table.
BREAK ON REPORT
COMPUTE SUM OF ”DEPARTMENT 10” ”DEPARTMENT 20” – ”DEPARTMENT 30” ”TOTAL BY JOB” ON REPORT
REM Each column displays the sums of salaries by job for REM one of the departments 10, 20, 30.
SELECT JOB,
SUM( DECODE( DEPTNO, 10, SAL, 0)) ”DEPARTMENT 10”, SUM( DECODE( DEPTNO, 20, SAL, 0)) ”DEPARTMENT 20”, SUM( DECODE( DEPTNO, 30, SAL, 0)) ”DEPARTMENT 30”, SUM(SAL) ”TOTAL BY JOB”
FROM EMP GROUP BY JOB