Using SQL*Plus, you can execute any SQL query or command that you desire. This includes data manipulation commands such as INSERT, UPDATE, DELETE, and SELECT. This also includes data definition commands such as CREATE TABLE, CREATE INDEX, CREATE USER, etc. Essentially, you can execute any command listed in the Oracle SQL reference manual.

Here is an example of a simple SELECT statement against the PROJECT table:

SQL> SELECT *  /* All Columns */
 2    FROM project;

PROJECT_ID PROJECT_NAME                             PROJECT_BUDGET
---------- ---------------------------------------- --------------
      1001 Corporate Web Site                              1912000
      1002 Year 2000 Fixes                               999998000
      1003 Accounting System Implementation                 897000
      1004 Data Warehouse Maintenance                       294000
      1005 TCP/IP Implementation                            415000

Look again at the SELECT query shown above. Notice that the statement spans more than one line. Notice that it contains an embedded comment. Notice that it ends with a semicolon. All of these things are important because they illustrate the following rules for entering SQL statements:

Pay close attention to the three ways to terminate a SQL statement. The reason you have to worry about this at all is because they can span multiple lines, and when you press ENTER for a new line, SQL*Plus needs some way of knowing whether you are done with the statement or whether you just want to continue it on another line. Until you enter a semicolon, a forward slash, or a blank line, SQL*Plus assumes that you are continuing your statement from one line to the next.

I usually recommend terminating SQL statements with semicolons, because I think that’s the simplest and cleanest-looking method. The SELECT statement above shows a semicolon at the end of the line, but if you forget and hit ENTER too quickly, you can also put it on the next line by itself. For example:

SQL> INSERT INTO project
  2    /* all columns */ 
  3  (project_id, project_name, project_budget)
  5    VALUES (1006,'Mainframe Upgrade',456789)
  6  ;

1 row created.

You may also use the forward slash character to terminate a SQL statement, but remember that it must be on a line by itself and must be the first and only character on that line. Here is an example:

SQL> UPDATE project
  2     SET project_budget = 1000000
  3   WHERE project_id = 1006
  4  /

1 row updated.

You will read more about the forward slash character later in this chapter because it’s also used to execute the SQL statement, or PL/SQL block, currently in the SQL buffer.

The final option for terminating an SQL statement is to simply press ENTER on a blank line. There is a catch to this method, though. Here’s an example:

SQL> DELETE
  2    FROM project
  3   WHERE project_id = 1006
  4  
SQL>

Look carefully at the preceding example. Notice that nothing happened! You typed in the DELETE command, pressed ENTER on a blank line, got back another SQL> prompt, but SQL*Plus said nothing about deleting the row that you just asked to delete. Why? Because when you terminate an SQL query with a blank line, SQL*Plus stores that command in an internal buffer but does not execute it. You’ll read more about this later in this chapter in the section titled Section 2.5. For now, though, if you haven’t entered any other commands after the DELETE statement shown above, just type a forward slash on a line by itself and press ENTER.

SQL> /

1 row deleted.

Now the DELETE statement has been executed, and the row deleted. The forward slash tells SQL*Plus to execute the SQL command most recently entered.

If you have been following along with SQL*Plus, and entering the examples while reading this section, you’ve probably noticed a couple of things. First, it’s a pain when you make a mistake. Second, it’s even worse when that mistake is on a previous line. If you were using the GUI version of SQL*Plus, you may have even tried to arrow up to correct a mistyped line. Well, don’t waste your time — you can’t do that. However, SQL*Plus does have some built-in line-editing capabilities, and it can also call the text editor of your choice. You’ll read about these options in just a bit, after the next section on executing PL/SQL blocks.