< previous page page_47 next page >
Page 47
the CHANGE command had to be retyped each time. SQL*Plus does not have any command recall capability.
Now that line 3 is fixed up, take another look at line 1. This time, omit the L command, and just type the line number in
order to list the line:
SQL> 1
1* SELECT employee_name, time_log_name, project_name
Line 1 contains a bad column name, which needs to be deleted. A variation of the CHANGE command, where you don't
supply any replacement text, is used to do this. The following example shows how:
SQL> C /time_log_name,//
1* SELECT employee_name, project_name
At first glance, the use of the CHANGE command to delete text may not seem very intuitive. It may help to think in
terms of searching for a string, in this case for time_log_name, and replacing it with nothing.
With the CHANGE command, you can use delimiters other than the forward slash character. You just need to be
consistent within the command. SQL*Plus interprets the first nonspace character following the CHANGE command as
the delimiter character. The following commands, for example, are all equivalent:
C /FRUB/FROM/
C *FRUB*FROM*
C XFRUBXFROMX
The only time you would ever need to use a delimiter other than / is if you needed to include a/as part of the text to be
searched for or replaced. You also have the option of leaving off the trailing delimiter, so long as you aren't trying to
include trailing spaces in your substitution string. The following two commands are equivalent:
C /FRUB/FROM/
C /FRUB/FROM
However, if your substitution strings contain spaces, you do need to include the trailing delimiter. The following two
commands will not produce equivalent results:
C / FRUB / FROM /
C / FRUB / FROM
It's probably easiest to be in the habit of including the trailing delimiter all the time. You'll make fewer mistakes that
way.

< previous page page_47 next page >
< previous page page_48 next page >
Page 48
DEL
The DEL command is used to erase one or more lines from the buffer. Used by itself, it erases the current line. You may
also specify a line, or a range of lines, as arguments to the DEL command. Unlike the other line-editing commands,
DEL cannot be abbreviated. This is perhaps a safety measure to keep you from accidentally deleting a line.
Be careful that you do not spell out the command as DELETE
instead of DEL. SQL*Plus will interpret DELETE as a new SQL command, and
will place it in the buffer in place of the command that you are editing.
List the buffer. If you have been following along through all the line-editing examples, you should see the following
output:
SQL> L
1 SELECT employee_name, project_name
2 FROM employee, project, project_hours
3 WHERE employee.employee_id = project_hours.employee_id
4 HAVING employee_name = project_name
5 AND time_log.project_num = project.project_num
6* GROUP BY employee_name, project_name
Line 4, with its HAVING clause, is completely spurious. It can be erased by using the DEL command as follows:
SQL> DEL 4
SQL> L *
4* AND time_log.project_num = project.project_num
SQL*Plus doesn't echo anything back at you, but line 4 has been erased. Notice that L * was used following the delete
to list the current line, which is now line 4. Why line 4? Because 4 was the number of the line most recently touched by
an editing command. In this case, the original line 4 was erased, what was line 5 became line 4, and the new line 4
becomes current.
The DEL command may also be used to erase a range of lines. As with LIST, the keywords LAST and * may be used to
specify the last line in the buffer and the current line, respectively. The next example shows how to erase lines 3 through
the current line. Remember that line 4 is current.

SQL> DEL 3 *
SQL> L *
3* GROUP BY employee_name, project_name
Because line 4 was current, the DEL command just shown erased lines 3 and 4. The new current line would ordinarily
still be line 4, because that was the last line

< previous page page_48 next page >
< previous page page_49 next page >
Page 49
number touched (erased), but in this case, because only three lines are left in the buffer, the last line becomes current.
INPUT
The INPUT command is used to insert one or more lines of text into the buffer. The INPUT command with a text
argument allows you to insert only one line, which is placed into the buffer following the current line. The INPUT
command with no arguments puts you into a multiline input mode where you can type as many lines as desired, ending
with a blank line. These lines are also inserted into me buffer following the current line.
List the buffer again. You can see that we have done serious damage to our SELECT statement by deleting the entire
WHERE clause.
SQL> L
1 SELECT employee_name, project_name
2 FROM employee, project, project_hours
3* GROUP BY employee_name, prokect_name
The statement listed above is actually syntactically correct and would execute. However, it would simply return all
possible combinations of employees and projects. The original intent was to list each employee together with all
projects to which the employee actually charged hours. In order to do that, the WHERE clause deleted earlier needs to
be put back in. The following example shows how to insert the first line of the WHERE clause by using INSERT with a
text argument:
SQL> L 2 Make line 2 current, so we can insert after it
2* FROM employee, project, project_hours
SQL> I WHERE employee.employee_id = project_hours. employee_id
SQL L

1 SELECT employee_name, project_name
2 FROM emeployee, project, project_hours
3 WHERE employee. employee_id = project_hours.employee_id
4* GROUP BY employee_name, project_name
An easier alternative, when you have several lines to insert, would be to use the INPUT command with no arguments.
This throws you into input mode, where you can type as many lines as you like. Pressing a blank line exits input mode,
terminating entry. Here's how to put in the second line of the WHERE clause using this method:
SQL> L 3 Make line 3 current, so new lines follow it
3* WHERE employee.emplouyee_id = project_hours.employee_id
SQL> I Enter input mode and type the new line
4i AND time_log.project_num = project.project_num
5i
SQL> L
1 SELECT employee_name, project_name
2 FROM employee, project, project_hours

< previous page page_49 next page >
< previous page page_50 next page >
Page 50
3 WHERE employee.employee_id = project_hours.employee_id
4 AND time_log.project_num = project.project_num
5* GROUP BY employee_name, project _name
The LIST command was used to make line 3 current, so that new lines will be inserted after it. Then the INPUT
command was used by itself to enter input mode, and a new line was entered to be added to the WHERE clause. Notice
that the prompt included an i following the line number, to remind you that you were inserting lines into an existing
statement.
If you are picky about formatting, use the second form of the INPUT command shown above. That will let you enter
leading spaces in order to make things line up nicely. INSERT text will trim off leading spaces before text is inserted.
To add lines at the end of a buffer, first do a LIST or a LIST LAST to make the last line current. Then use the INPUT
command to put yourself into input mode. Any lines you type will be appended onto the end of the buffer.

There is no way to insert a line prior to the first line in the buffer.
That's because inserted lines follow the current line, and there is no line prior to
line 1 that you could make current. If you do need to insert a line prior to the
first line, insert it as line 2, then retype the first line as line 3, and finally delete
the original first line.
Retyping a line
Using the line editor, you can completely replace a line in the buffer by entering the desired line number followed by a
new version of the line. Assume that you have the following SELECT statement in the buffer:
SQL>L
1 SELECT employee_name, project_name
2 FROM employee, project, project_hours
3 WHERE employee_employee_id = project_hours.employee_id
4 AND time_log.project_num = project.project_num
5* GROUP By employee_name, project_name
With respect to the sample tables used for this book, you can see that line 4 has several mistakes. There is no
TIME_LOG table, and the primary key for a project is projec_id, not project_num. The CHANGE command could be
used to correct these items, but you might find it faster and easier just to retype the entire line. Here's how you can do
that:
SQL > 4 AND project_hours.project_id = project.project_id
SQL> L
1 SELECT employee_name, project_name
2 FROM employee, project, project_hours
3 WHERE employee.employee_ud = project_hours.employee_id

< previous page page_50 next page >
< previous page page_51 next page >
Page 51
4 AND project_hours.project_id = project.project_id
5* GROUP By employee_name, project_name
Notice that line 4 has been replaced by the text that was typed after the numeral 4 on the first line of this example. You can

replace any line in this way. If you want to preserve the indenting, you can insert extra spaces following the line number.
Executing the Statement in the Buffer
Once you get a statement into the buffer and have edited it the way you want it, the next step is to execute that statement.
That can be done with one of the following two methods:
1. Type a forward slash on a line by itself, then press ENTER.
2. Use the RUN command, which may be abbreviated to R.
The only difference between using RUN and / is that the RUN command lists the contents of the buffer before executing
it, while the / command simply executes the command without relisting it. Assume that you have the SQL statement
shown next in the buffer, which you will if you have followed through all the examples in this chapter.
SQL>L
1 SELECT employee_name, project_name
2 FROM employee, project, project_hours
3 WHERE employee.employee_id = project_hours.employee_id
4 AND project_hours.project_id = project.project_id
5* GROUP BY employee_name, project_name
Here is how you would run it using the / command:
SQL> /

EMPLOYEE_NAME PROJECT_NAME

Bohdan Khmelnytsky Accounting System Implementation
Bohdan khmelnytsky Corporate Web Site
Bohdan Khmelnytsky Data Warehouse Maintenance
Bohdan khmelnytsky TCP/IP Implementation
Bohdan khmelnytsky Year 2000 Fixes
Now, here is how you would execute it using the RUN command, which in the following example is abbreviated to R:
SQL> R
1 SELECT employee_name, project_name
2 FROM employee, project, project_hours


< previous page page_51 next page >
< previous page page_52 next page >
Page 52
3 WHERE employee.employee_id = project_hours.employee_id
4 AND project_hours.project_id = project.project_id
5* GROUP BY employee_name, project_name

EMPLOYEE_NAME PROJECT_NAME

Bohdan Khmelnytsky Accounting System Implementation
Bohdan khmelnytsky Corporate Web site
Bohdan khmelnytsky Date Warehouse Maintenance
Bohdan khmelnytsky TCP/IP Implementaiton
Bohdan khemlnytsky Year 2000 Fixes
Notice this time that the SQL statement in the buffer was first displayed on the screen, and then executed. I almost always use
the forward slash to execute commands, but RUN is useful if you are printing an ad-hoc report, or sending the query results
to a file, and wish to have a copy of the SQL statement included for future reference.
If Your Statement Has an Error
If a SQL statement fails to execute, SQL*Plus does three things. It makes the line containing the error current, displays that
line for you to edit, and displays the error message returned by Oracle. Look at the following example of a SQL SELECT
statement with a invalid column name:
SQL> SELECT employee_name
2 FROM project;
SELECT employee_name
*
ERROR at line 1:
ORA-00904; invalid column name
SQL*Plus displays the error returned by Oracle, which tells you that your column name is bad. The offending line is
displayed, and an asterisk points to the incorrect column name. You can quickly edit that line, change employee_name to
project_name, and re-execute the command as follows:

SQL> /employee_name/project_name
1* SELECT project_name
SQL> /

PROJECT_NAME

Corporate Web Site
Year 2000 Fixes
Accounting System Implementation
Data Warehouse Maintenance
TCP/IP Implementation

< previous page page_52 next page >
< previous page page_53 next page >
Page 53
This is a very convenient feature if you have entered a long command and have only made one or two small mistakes.
When debugging SQL statements (or PL/SQL blocks), don't get too
hung up on where Oracle thinks the error is. When SQL*Plus displays an error
line with an asterisk under it, that asterisk is pointing to where Oracle was
looking when the problem was detected. Depending on the nature of the error,
you may need to look elsewhere in your statement. Getting the table name
wrong, for example, may lead to spurious invalid column errors. The error in
the example just shown could also have been corrected by changing the table
name from PROJECT to EMPLOYEE. Know what results you are after, and be
prepared to look beyond the specific error message that you get from Oracle.
If you are trying to create a stored object, such as a stored procedure, you will need to use the SHOW ERRORS
command to see where any errors lie. Here is an example:
SQL> CREATE PROCEDURE example1 AS
2 BEGIN
3 bad_statement;

4 END;
5 /

Warning: Procedure created with compilation errors.

SQL> SHOW ERRORS
Errors for PROCEDURE EXAMPLE1:

LINE/COL ERROR

3/3 PLS-00201; identifier BAD_STATEMENT must be declared
3/3 PL/SQL; Statement ignored
The reason for this difference is that when you compile code for a stored object, such as a procedure or function, Oracle
parses all the code, and reports all the errors it finds. This is quite convenient, because if you have a large code block,
you certainly don't want to have to find and correct errors one at a time.
Doing It Again.
Three other things worth knowing about the RUN (or /) command are:
Unless an error occurs, the current line is not changed.
Executing a command does not remove it from the buffer.
Executing a SQL*Plus command also leaves the buffer intact.

< previous page page_53 next page >
< previous page page_54 next page >
Page 54
These three features make it easy to rerun an SQL statement either as it stands or with minor changes. Take a look at the
following example, which displays the name for employee number 107:
SQL> SELECT employee_name
2 FROM employee
3 WHERE employee_id =107;


EMPLOYEE_NAME

Bohdan Khmelnytsky
A quick change to line 3 will let you see the name for employee ID 110:
SQL> 3
3* WHERE employee_id = 107
SQL> c/107/110
3* WHERE employee_id = 110
SQL> /

EMPLOYEE_NAME

Ivan Mazega
At this point, line 3 is still current. Since no error occured, SQL*Plus had no reason to change it, so it's even easier to
look at the name for employee number 111:
SQL> c/110/111
3* WHERE employee_id = 111
SQL> /

EMPLOYEE_NAME

Taras Schevchenko
Sometimes it makes sense to execute the same statement over and over again, without making any changes to it. A
SELECT statement that queried one of the V$ tables, perhaps V$ SESSION, to get a list of current users, would be a
good example of this. INSERT statements are often repeatedly executed in order to generate small amounts of test data.
Saving and Retrieving the Buffer
SQL*Plus allows you to save the contents of the buffer to a file and to read that file back again. If you have built up a
long and complicated SQL statement, you can save it for reuse later, and save yourself the bother of figuring it all out
again. Two commands, GET and SAVE, are provided for this purpose.


< previous page page_54 next page >
< previous page page_55 next page >
Page 55
SAVE
The SAVE command allows you to save the current contents of the buffer to a file. Here is the syntax for SAVE:
SAVE filename [CREATE|REPLACE|APPEND]
where:
SAV[E]
May be abbreviated SAV.
filename
Is the name of the file to which you want to save the buffer. The default extension is .SQL, but you may specify another
if you like.
CRE[ATE]
Tells SQL*Plus that you want to create a new file. The save will fail if the file you've specified already exists. This is
the default behavior.
REPLACE
Tells SQL*Plus to save the buffer to the file specified, regardless of whether or not that file already exists. If the file
does exist, it will be overwritten.
APPEND
Tells SQL*Plus to add the contents of the buffer to an existing file.
The following example shows the SAVE command being used to save the contents of a rather long SQL query to a file.
First the query is entered into the buffer without being executed; then the SAVE command is used to write the buffer to
a file.
SQL> SELECT employee_name, project_name
2 FROM employee_name, project, project_hours
3 WHERE employee, employee_id = project_hours.employee_id
4 AND project_hours.project_id = project.project_id
5 AND employee_billing_rate in (
6 SELECT MAX (employee_billing_rate
7 FROM employee

8 )
9 GROUP BY employee_name, project_name
10
SQL> SAVE C:/A/ HIGHEST_BILLED_EMP PROJECTS
Created file C:/A/HIGHEST_BILLED_EMP_PROJECTS
The SAVE command shown above created a new file, with the default extension of .SQL, and saved the contents of the
buffer to that file. SQL*Plus also terminated the statement in the buffer with a trailing forward slash on a line by itself,
so the resulting output file looks like this:
SELECT employee_name, project_name
FROM employee, project, project_hours

< previous page page_55 next page >
< previous page page_56 next page >
Page 56
WHERE employee.employee_di = project_hours.employee_id
AND project_hours.project_id = project.project_id
AND employee_billing_rate in (
SELECT MAX (employee_billing_rate)
FROM employee
)
GROUP By employee_name, project_name
/
SQLL*Plus will not automatically replace an existing file. Had the file already existed, and had we wanted to replace it,
the REPLACE option would have been needed on the SAVE command. For example:
SAVE C: \A\HIGHEST_BILLED_EMP_PROJECTS REPLACE
The APPEND option adds the contents of the buffer onto the end of an existing file. If you append multiple statements
to a file, you won't be able to load that file back into the buffer and execute those commands. However, you will be able
to execute the file using the START command.
Try to use descriptive filenames when saving your SQL statements.
You want the filename to jog your memory later when you need to retrieve that

statement. The query shown above returns a list of projects worked on by the
employee (or employees) with the highest billing rate; thus the filename of
HIGHEST_BILLED_EMP_PROJECTS seemed appropriate. The length of a
filename is governed by what your operating system allows.
GET
The GET command is the opposite of SAVE. It retrieves the contents of a file to the buffer. Here is the syntax:
GET filename [LIST][NOLIST]
where:
GET
May not be abbreviated.
filename
Is the name of the file containing the text you want to load into the buffer. The default extension is .SQL, but you may
specify another if you like.
LIS[T]
May be abbreviated to LIS, and tells SQL*Plus to list the contents of the buffer after loading the file.

< previous page page_56 next page >