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.

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.