Error Correction in SQL*Plus
There are several SQL*Plus commands which may help you edit and run queries without re-entering them.
When you enter a SQL statement, SQL*Plus stores each line in a buffer. To retrieve it from the buffer for modification type LIST or the letter L at the SQL> prompt. Case is not important in SQL*Plus, so it is not necessary to capitalize these commands. Suppose the following query had been entered:
SQL> SELECT EMPO
FROM EMPLOYEE
WHERE JOB = 'Clrk';
By executing the LIST command at the SQL*Plus prompt, this is what is returned:
SQL> LIST
1 SELECT EMPO
2 FROM EMPLOYEE
3* WHERE JOB = 'Clrk'
The LIST or L will return the total query to you for viewing. If you want to return a specific row of the query for modification, use LIST<line number>. For example to return only line one:
SQL> L1
1* SELECT EMPO <====note EMPO should be EMPNO
Now that you have retrieved the line, suppose you note a typing error in that line and wish to correct it. CHANGE or C allows you to modify the text. For example the generic syntax would be as follows:
C/old text/new text
To correct the above query, first list the line, (L1), then enter the change using the change command format as follows:
SQL> L1
1* SELECT EMPO
SQL> C/EMPO/EMPNO press <enter> and your line comes back to you.
1* SELECT EMPNO
Note: Only the current line will be affected by the CHANGE command.
List the query again to view the change:
SQL> L
1 SELECT EMPNO
2 FROM EMPLOYEE
3* WHERE JOB = 'Clrk'
Now that the query is corrected you can execute it. Type 'RUN', 'R' or '/':
SQL> RUN
1 SELECT EMPNO
2 FROM EMPLOYEE
3* WHERE JOB = 'Clrk'
The following result table should be displayed.
EMPNO
----------
100
110
213
If you wanted to add another line to the query, use the INPUT or I command. For example your query reads:
SQL> SELECT EMPNO
FROM EMPLOYEE
WHERE JOB = 'Clrk';
If you want to add a new line which states 'AND DEPTNO = 10'. Use the INPUT command to add the line as follows:
SQL> INPUT
4 AND DEPTNO = 10
5
SQL>
Since SQL*Plus does not know how many lines you wanted to add, it will return as many lines as you need. When it encounters a blank line it will then return you to the SQL> prompt. List the query again to check it and then rerun it:
SQL> L
1 SELECT EMPNO
2 FROM EMPLOYEE
3 WHERE JOB = 'Clrk'
4* AND DEPTNO = 10
SQL> RUN
1 SELECT EMPNO
2 FROM EMPLOYEE
3 WHERE JOB = 'Clrk'
4* AND DEPTNO = 10
The results table from the query is as follows:
EMPNO
----------
100
If you want to insert a line between the lines you have already typed, first LIST the line by its line number then follow with the INPUT command and your text. SQL*Plus will re-number the balance of the lines of the query.
The DELETE or DEL command works in much the same way as the INPUT command. To delete a line in your queries or scripts, first issue the SQL*Plus LIST command with the line number, then simply enter DEL at the SQL> prompt and the line will be deleted.
The APPEND or A command places text at the end of the current line. To APPEND something onto the end of a line, use the LIST command with the line number, then enter A followed by the text to be added. For example, to include name in the query above, type
SQL> L1
1* SELECT EMPNO
SQL> A , NAME
1* SELECT EMPNO, NAME
SQL> L
1 SELECT EMPNO, NAME
2 FROM EMPLOYEE
3 WHERE JOB = 'Clrk'
4* AND DEPTNO = 10
SQL> RUN
1 SELECT EMPNO, NAME
2 FROM EMPLOYEE
3 WHERE JOB = 'Clrk'
4* AND DEPTNO = 10
The results table displayed should look like this.
|
EMPNO |
NAME |
|
100 |
Wilson |
Note: Each of the following commands used for editing: LIST, CHANGE, APPEND, and INPUT can be abbreviated to its own first letter. DELETE is abbreviated DEL.
Last revised 10/20/97 by J. Warren, Kennesaw State University
Department of Computer Science and Information Systems