SQL*Plus User's Guide and Reference Release 9.0.1 Part Number A88827-02 |
|
Manipulating Commands, 2 of 6
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.
You will find these commands useful if you mis-type a command or wish to modify a command you have entered.
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.
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.)
LIST SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY 2 FROM EMP_DETAILS_VIEW 3* WHERE SALARY>12000
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 JOB_ID column but mistakenly enter it as JO_ID. Enter the following command, purposely misspelling JOB_ID in the first line:
SELECT EMPLOYEE_ID, LAST_NAME, JO_ID, SALARY FROM EMP_DETAILS_VIEW WHERE JOB_ID='SA_MAN';
You see this message on your screen:
SELECT EMPLOYEE_ID, LAST_NAME, JO_ID, SALARY * ERROR at line 1: ORA-00904: 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 mis-typed column JOB_ID.
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. You do not need to use the CHANGE command to re-enter an entire line. Re-enter the line by typing the line number followed by a space and the new text and pressing Return.
To change JO_ID to JOB_ID, change the line with the CHANGE command:
CHANGE /JO_ID/JOB_ID
The corrected line appears on your screen:
1* SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, SALARY
Now that you have corrected the error, you can use the RUN command to run the command again:
RUN
SQL*Plus correctly displays the query and its result:
1 SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY 2 FROM EMP_DETAILS_VIEW 3* WHERE JOB_ID='SA_MAN' EMPLOYEE_ID LAST_NAME JOB_ID MONTHLY SALARY ----------- ------------------------- ---------- -------------- 145 Russell SA_MAN $14,000 146 Partners SA_MAN $13,500 147 Errazuriz SA_MAN $12,000 148 Cambrault SA_MAN $11,000 149 Zlotkey SA_MAN $10,500
Note that the column SALARY 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 the CHANGE command in Chapter 8.
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.
0 SELECT EMPLOYEE_ID
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.
INPUT
SQL*Plus prompts you for the new line:
4
Enter the new line. Then press Return.
4 ORDER BY SALARY
SQL*Plus prompts you again for a new line:
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.
1 SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY 2 FROM EMP_DETAILS_VIEW 3 WHERE JOB_ID='SA_MAN' 4* ORDER BY SALARY EMPLOYEE_ID LAST_NAME JOB_ID MONTHLY SALARY ----------- ------------------------- ---------- -------------- 149 Zlotkey SA_MAN $10,500 148 Cambrault SA_MAN $11,000 147 Errazuriz SA_MAN $12,000 146 Partners SA_MAN $13,500 145 Russell SA_MAN $14,000
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:
LIST 4 4* ORDER BY SALARY
Next, enter the following command (be sure to type two spaces between APPEND and DESC):
APPEND DESC 4* ORDER BY SALARY DESC
Type RUN
to verify the query:
1 SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY 2 FROM EMP_DETAILS_VIEW 3 WHERE JOB_ID='SA_MAN' 4* ORDER BY SALARY DESC EMPLOYEE_ID LAST_NAME JOB_ID MONTHLY SALARY ----------- ------------------------- ---------- -------------- 145 Russell SA_MAN $14,000 146 Partners SA_MAN $13,500 147 Errazuriz SA_MAN $12,000 148 Cambrault SA_MAN $11,000 149 Zlotkey SA_MAN $10,500
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.
DEL * LAST
DEL makes the following line of the buffer (if any) the current line.
For more information, see the DEL command in Chapter 8.
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
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:
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" later in this chapter and the DEFINE and EDIT commands in Chapter 8 for more information.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|