Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

2.8 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. You can do
that using one of the following two methods:

Type a forward slash on a line by itself, then press Enter.

Use the RUN command, which you may abbreviate to R.

The only difference between using / and RUN is that the RUN command
lists the contents of the buffer before executing it, and the /
command simply executes the command without re-listing 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 JOIN project_hours
3 ON employee.employee_id = project_hours.employee_id
4 JOIN project
5 ON project_hours.project_id = project.project_id
6* GROUP BY employee_name, project_name Here is how you would execute it using the / command:

SQL> /
EMPLOYEE_NAME PROJECT_NAME
------------------------------ ----------------------------------------
Ivan Mazepa Corporate Web Site
Ivan Mazepa VPN Implementation
Ivan Mazepa Data Warehouse Maintenance
...

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 JOIN project_hours
3 ON employee.employee_id = project_hours.employee_id
4 JOIN project
5 ON project_hours.project_id = project.project_id
6* GROUP BY employee_name, project_name
EMPLOYEE_NAME PROJECT_NAME
------------------------------ ----------------------------------------
Ivan Mazepa Corporate Web Site
Ivan Mazepa VPN Implementation
Ivan Mazepa Data Warehouse Maintenance
...

This time, 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.

2.8.1 If Your Statement Has an Error

If a SQL statement fails to
execute, SQL*Plus does three things:

Makes the line triggering the error current Displays that line for you to edit Displays the error message returned by Oracle
Look at the following example of a
SQL SELECT statement with an invalid
column name:

SQL> SELECT employee_name
2 FROM project;
SELECT employee_name
*
ERROR at line 1:
ORA-00904: "EMPLOYEE_NAME": invalid identifier 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> c /employee_name/project_name/
1* SELECT project_name
SQL> /
PROJECT_NAME
----------------------------------------
Corporate Web Site
Enterprise Resource Planning System
Accounting System Implementation
Data Warehouse Maintenance
VPN Implementation This feature is convenient if you have entered a long command and
have 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 employee to project. Know what results you are after, and be prepared to look beyond the specific error message that you get from Oracle.

If you want 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. Example 2-13 demonstrates this.

Example 2-13. Using SHOW ERRORS when stored procedure creation fails

SQL> CREATE PROCEDURE wont_work AS
2 BEGIN
3 bad_statement;
4 END;
5 /
Warning: Procedure created with compilation errors.
SQL> SHOW ERRORS
Errors for PROCEDURE WONT_WORK:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/4 PL/SQL: Statement ignored
3/4 PLS-00201: identifier 'BAD_STATEMENT' must be declared 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 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:

2.8.2 Doing It Again

Three other things are worth knowing about the RUN (or /) command:

Unless an error occurs, the current line is not changed.

Executing a statement does not remove it from the buffer.

Executing a SQL*Plus command leaves the buffer intact.

These three features make it easy to rerun an SQL statement either as
it stands or with minor changes. Take a look at Example 2-14, which displays the name for employee number
107.

Example 2-14. Retrieving an employee's name

SQL> SELECT employee_name
2 FROM employee
3 WHERE employee_id = 107;
EMPLOYEE_NAME
------------------------------
Lesia Ukrainka 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 Mazepa At this point, line 3 is still current. Because no error occurred,
SQL*Plus had no reason to change the current line, 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 Shevchenko Sometimes it makes sense to execute the same statement 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 to generate small
amounts of test data.

As I mentioned earlier, Windows XP supports command-recall. Press F7 from the Windows XP command prompt, or from the command-line SQL*Plus prompt while running under Windows, and you should see a list of commands that you have previously typed. Use the up/down arrows to select one, and press Enter to execute it again. Press Esc to dismiss the command-recall dialog. This technique does not work from the Windows GUI version of SQL*Plus, but only from the Windows command-line version.