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:
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_nameHere 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
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 Data Warehouse Maintenance
Bohdan Khmelnytsky TCP/IP Implementation
Bohdan Khmelnytsky 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 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_name2FROM 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>C /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
This is a very convenient feature if you have entered a long command and have only made one or two small mistakes.
Tip
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 AS2BEGIN3bad_statement;4END;5/Warning: Procedure created with compilation errors. SQL>SHOW ERRORSErrors 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.
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.
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_name2FROM employee3WHERE employee_id = 107;EMPLOYEE_NAME ---------------------------------------- Bohdan Khmelnytsky
A quick change to line 3 will let you see the name for employee ID 110:
SQL>33* WHERE employee_id = 107 SQL>c /107/1103* WHERE employee_id = 110 SQL>/EMPLOYEE_NAME ---------------------------------------- Ivan Mazepa
At this point, line 3 is still current. Since no error occurred, 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/1113* WHERE employee_id = 111 SQL>/EMPLOYEE_NAME ---------------------------------------- Taras Shevchenko
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.