Using SQL*Plus , you can execute any SQL query or statement that you desire . This includes data manipulation language (DML) statements such as INSERT, UPDATE, DELETE, MERGE, and SELECT. This also includes data definition language (DDL) statements such as CREATE TABLE, CREATE INDEX, CREATE USER, and so on. Essentially, you can execute any statement listed in the Oracle SQL Reference manual.
Example 2-1 shows a simple SELECT statement against the project table.
Example 2-1. A simple SELECT statement
SQL> SELECT * /* All columns */ 2 FROM project; PROJECT_ID PROJECT_NAME PROJECT_BUDGET ---------- ---------------------------------------- -------------- 1001 Corporate Web Site 1912000 1002 Enterprise Resource Planning System 9999999 1003 Accounting System Implementation 897000 1004 Data Warehouse Maintenance 294000 1005 VPN Implementation 415000
Look again at the query shown in the example. Notice that the statement spans more than one line. Notice that it contains an embedded comment and 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 an SQL statement. You have to worry about this because statements 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 in Example 2-1 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, as shown in Example 2-2.
Example 2-2. The terminating semicolon on a line by itself
SQL> INSERT INTO project 2 /* All columns */ 3 (project_id, project_name, project_budget) 4 VALUES (1006,'Mainframe Upgrade',456789) 5 ; 1 row created.
You can also use the forward slash (/) character to terminate a SQL statement, but it must be on a line by itself and must be the first and only character on that line. Example 2-3 demonstrates this usage.
Example 2-3. A statement terminated by a forward slash
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 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. This is shown in Example 2-4. There is a catch to this method, though.
Example 2-4. A statement terminated by a blank line
SQL> DELETE 2 FROM project 3 WHERE project_id = 1006 4 SQL>
Look carefully at Example 2-4. Nothing happened . You typed in the DELETE statement, 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 not? Because when you terminate an SQL query with a blank line, SQL*Plus stores that statement in an internal buffer but does not execute it. You'll read more about this later in this chapter in the section Section 2.6. For now, though, if you haven't entered any other statements after the DELETE statement shown above, just type a forward slash on a line by itself and press Enter:
SQL> / 1 row deleted.
The DELETE has been executed and the row deleted. The forward slash tells SQL*Plus to execute the SQL statement 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 because you can't do that. However, SQL*Plus does have some built-in line-editing capabilities, and it can 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.
Executing Unrecognized SQL Statements
Under "Running SQL Queries," I stated that SQL*Plus may be used to execute any SQL statement. That's sort of true. You do need to have a version of SQL*Plus recent enough to "know" about whatever SQL statement you are attempting to execute. For example, SQL*Plus 8.1.7 does not recognize MERGE:
SQL> MERGE INTO project dest SP2-0734: unknown command beginning "merge into..." - rest of line ignored.
SQL*Plus looks only at the first word of the statement before returning the error shown in this example. MERGE was introduced in Oracle9 i Database, and no previously existing statements began with that keyword. New ALTER statements don't cause the same problem because ALTER something has been around forever.
If you find that you must execute an unrecognized SQL statement from an older version of SQL*Plus, and if that statement does not return any kind of result set that SQL*Plus would otherwise try to display, you likely can execute the statement from a PL/SQL block. For example:
BEGIN MERGE INTO project dest USING (SELECT * FROM project) source ON (dest.project_id = source.project_id) WHEN MATCHED THEN UPDATE SET dest.project_name = source.project_name; END; /
See the Section 2.5 for information on executing PL/SQL from SQL*Plus.
Introduction to SQL*Plus
A Lightning SQL Tutorial
Generating Reports with SQL*Plus
Creating HTML Reports
Writing SQL*Plus Scripts
Extracting and Loading Data
Exploring Your Database
Tuning and Timing
The Product User Profile
Customizing Your SQL*Plus Environment
Appendix A. SQL*Plus Command Reference
Appendix B. SQL*Plus Format Elements
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
Authors: Jonathan Gennick
Secure Programming Cookbook for C and C++: Recipes for Cryptography, Authentication, Input Validation & More