Running a SQL Statement

In the console version of SQL*Plus, the query:

SELECT * FROM books;

produces output similar to that shown in Figure 2-1. (Well, I cheated a bit in that figure, because I used some column formatting commands. If this were a book about SQL*Plus or how to display database data, I would expound on the many ways SQL*Plus lets you control the appearance of the output by setting various formatting and display preferences. You can take my word for it, though; there are more options than you can shake a stick at.)

If you want "pretty" output, you may be better off with iSQL*Plus. Here, you enter the statement in the "Enter statements" field and press the "Execute" button. Output appears in a table in your browser, as in Figure 2-4.

Figure 2-4. Query with result in iSQL*Plus

You must terminate SQL statements in the console or pseudo-GUI with asemicolon, but the terminator is not required when entering a single statement in iSQL*Plus.

2.1.3 Running a PL/SQL Program

So, here we go (drum roll please). Let's type a short PL/SQL program into SQL*Plus:

SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE('Hey look, ma!'); 3 END; 4 / PL/SQL procedure successfully completed. SQL>

Oops. While "successful," this particular program is supposed to invoke PL/SQL's built-in program that echoes back some text. SQL*Plus's somewhat annoying default behavior is to suppress such output. To get it to display properly, you must use a SQL*Plus command to turn on SERVEROUTPUT.

SQL> SET SERVEROUTPUT ONSQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE('Hey look, Ma!'); 3 END; 4 /Hey look, Ma! PL/SQL procedure successfully completed. SQL>

I generally put the SERVEROUTPUT command in my startup file (see the discussion of login.sql in the later section, Section 2.1.5.5), causing it to be enabled until one of the following occurs:

1. You disconnect, log off, or otherwise end your session

2. You explicitly set SERVEROUTPUT to OFF

3. Oracle discards session state either at your request or due to a compilation error (see Section 20.2.2)

When you enter SQL or PL/SQL statements into the console or pseudo-GUI SQL*Plus, the program assigns a number to each line after the first. Why does it number the lines? Two main reasons: first, to help you designate which line to edit with the built-in line editor (which you might actually use one day); and second, if Oracle detects an error in your code, it will usually report the error accompanied by a line number. You'll have plenty of opportunities to see that behavior in action.

To tell SQL*Plus that you're done entering a PL/SQL statement, you must usually include a trailing slash (see line 4 above). Although mostly harmless, the slash has several important characteristics:

· The meaning of the slash is "execute the most recently entered statement," regardless of whether the statement was SQL or PL/SQL.

· The slash is a command unique to SQL*Plus; it is not part of the PL/SQL language, nor is it part of SQL.

· It must appear on a line by itself—no other commands can be included on the line.

· In most versions of SQL*Plus prior to Oracle9i, if you accidentally precede the slash with any spaces, it doesn't work! Beginning with Oracle9i, SQL*Plus correctly overlooks leading whitespace. Trailing space doesn't matter in any version.

As a convenience feature, SQL*Plus offers PL/SQL users anEXECUTE command, which saves typing the BEGIN, END, and trailing slash. So the following is equivalent to the short program I ran earlier:

SQL> EXECUTE DBMS_OUTPUT.PUT_LINE('Hey look, Ma!')

A trailing semicolon is optional, but I prefer to omit it. As with most SQL*Plus commands, EXECUTE can be abbreviated and is case-insensitive, so most interactive use gets reduced to:

SQL> exec dbms_output.put_line('Hey look, Ma!')