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.


     



Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151
Simiral book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net