2.1.7 Why You Will Love and Hate sql*Plus

In addition to the features you just read about, here are some particular features of SQL*Plus that you will come to know and love:

• With SQL*Plus, you can run "batch" programs, supplying application-specific arguments on the sqlplus command line, and referring to them in the script using &1 (first argument), &2 (second argument), etc.

• SQL*Plus provides complete and up-to-date support for all SQL and PL/SQL statements. This can be important when you're using features unique to Oracle. Third-party environments may not provide 100% coverage; for example, some still don't understand Oracle's object types, which were introduced several years ago. (See Chapter 21 for a discussion of using object types in PL/SQL.)

• SQL*Plus runs on all of the same hardware and operating system platforms on which the Oracle server runs.

But as with any tool, there are going to be some irritations:

• In console versions of SQL*Plus, the statement buffer is limited to the most recently used statement; SQL*Plus offers no further command history.

• When you switch users by issuing a CONNECT command in the middle of a session or a script, SQL*Plus does not rerun the login.sql (or glogin.sql) startup script, causing SERVEROUTPUT to get disabled. This also causes no end of trouble for folks who create fancy prompts in their login.sql file—for example, a prompt such as "TEST9i>" will remain even after doing a CONNECT to the PROD9i database. You have to remember to run the startup after every reconnect.

• With SQL*Plus, there are no modern command-interpreter features such as automatic completion of keywords or hints about which database objects are available while typing in a statement.

• Online help consists of minimal documentation of the SQL*Plus command set. (Use HELP command to get help on a specific command.)

• There is no ability to change the current directory once you've started SQL*Plus. This can be annoying when opening or saving scripts if you don't like typing full pathnames. If you discover that you're in an inconvenient directory, you have to quit SQL*Plus, change directories, and restart SQL*Plus.

• SQL*Plus looks only in the startup directory for login.sql; it would be better if it always searched in my home directory, so I wouldn't need a copy of login.sql in every directory where I work.

The bottom line is that SQL*Plus is something of a "real programmer's" tool that is neither warm nor fuzzy. But it is ubiquitous, doesn't crash, and is likely to be supported as long as there is an Oracle Corporation.

2.2 Performing Essential pl/sql Tasks

Let's turn to the highlights of creating, running, deleting, and otherwise managing PL/SQL programs, using SQL*Plus as the front end. Don't expect to be overwhelmed with detail here; treat this section as a glimpse of topics that will be covered in much greater detail in the chapters ahead.

2.2.1 Creating a Stored Program

To build your own stored PL/SQL program, you use one of SQL'sCREATE statements. For example, if you want to create a stored function named wordcount that counts words in a string, you can do so using a CREATE FUNCTION statement:

CREATE FUNCTION wordcount (str IN VARCHAR2)

RETURN PLS_INTEGER

AS

declare local variables go here

BEGIN

implement algorithm here

END;

/

As with the simple BEGIN-END blocks shown earlier, running this statement from SQL*Plus requires a trailing slash on a line by itself.

Assuming that the database administrator has granted you Oracle's CREATE PROCEDURE privilege, this statement will cause Oracle to compile and store this stored function in your schema; if your code compiles, you'll probably see a success message such as:

Function created.

If a table or stored program named wordcount already exists in your Oracle schema, CREATE FUNCTION will fail with the error message ORA-00955: name is already used by an existing object. That is one of the reasons that Oracle provides the OR REPLACE OPTION, which you will want to use probably 99% of the time.

CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2)

RETURN PLS_INTEGER

AS

same as before

The OR REPLACE option avoids the side effects of dropping and re-creating the program; in other words, it preserves any object privileges you have granted to other users or roles. Fortunately, it only replaces objects of the same type, and it won't automatically drop a table named wordcount just because you decided to create a function by that name.

As with anonymous blocks used more than once, programmers generally store these statements in files in the operating system. I could create a file wordcount.fun for this function and use the SQL*Plus @ command to run it:

SQL> @wordcount.fun

Function created.

As this example shows, SQL*Plus does not, by default, echo the contents of scripts. You can, however, SET ECHO ON to see the source code scroll past the screen, including the line numbers that Oracle assigns; this setting can be helpful when troubleshooting. Let's introduce an error into the program by commenting out a variable declaration.

SQL> /* File on web: wordcount.fun */

SQL> SET ECHO ON

SQL> @wordcount.fun

SQL> CREATE OR REPLACE FUNCTION wordcount (str IN VARCHAR2)

2 RETURN PLS_INTEGER

3 AS

4 /* words PLS_INTEGER := 0; ***Commented out for intentional error*** */

5 len PLS_INTEGER := NVL(LENGTH(str),0);

6 inside_a_word BOOLEAN;

7 BEGIN

8 FOR i IN 1..len + 1

9 LOOP

10 IF ASCII(SUBSTR(str, i, 1)) < 33 OR i > len

11 THEN

12 IF inside_a_word

13 THEN

14 words := words + 1;

15 inside_a_word := FALSE;

16 END IF;

17 ELSE

18 inside_a_word := TRUE;

19 END IF;

20 END LOOP;

21 RETURN words;

22 END;

23 /

Warning: Function created with compilation errors.

SQL>

We get a "warning" message that is completely inadequate for debugging the problem; you will want to look at the full text of the error message. The quickest way to do this is with the SHOW ERRORS command, abbreviated as SHOW ERR:

SQL> SHOW ERR

Errors for FUNCTION WORDCOUNT:

LINE/COL ERROR

-------- ----------------------------------------------

14/13 PLS-00201: identifier 'WORDS' must be declared

14/13 PL/SQL: Statement ignored

21/4 PL/SQL: Statement ignored

21/11 PLS-00201: identifier 'WORDS' must be declared

(Why SQL*Plus makes you show errors as a second step, I don't know.) The compiler has detected both occurrences of the variable, reporting the exact line and column numbers. Behind the scenes, SHOW ERRORS is really just querying Oracle's USER_ERRORS view in the data dictionary. To see more detail about any server-based error, you can look it up by its identifier—PLS-00201 in this case—in Oracle's Database Error Messages document.

Show Other Errors Many Oracle programmers know only one form of the SQL*Plus command: SQL> SHOW ERRORS and they incorrectly believe that they must query the USER_ERRORS view directly to see anything but the error messages from the most recent compile. However, you can append to SHOW ERRORS an object category and a name, and it will display the latest errors for any object: SQL> SHOW ERRORS category object For example, to view the latest errors for the wordcount procedure, specify: SQL> SHOW ERRORS PROCEDURE wordcount Use caution when interpreting the output: No errors. This message actually means one of three things: (1) the object did compile successfully; (2) you gave it the wrong category (for example, function instead of procedure); or (3) no object by that name exists! The complete list of categories this command recognizes varies by version, but in Oracle9i it includes the following: DIMENSION FUNCTION JAVA SOURCE JAVA CLASS PACKAGE PACKAGE BODY PROCEDURE TRIGGER TYPE TYPE BODY VIEW

It's common practice to append a SHOW ERRORS command after every scripted CREATE statement that builds a stored PL/SQL program. So, a "good practices" file template for building stored programs in SQL*Plus might begin with the following form:

CREATE OR REPLACE program-type

AS

your code

/

SHOW ERRORS

(I don't usually include SET ECHO ON in scripts, but rather type it at the command line when needed.)

When your program contains an error that the compiler can detect, CREATE will still cause Oracle to store the program in the database, though in an invalid state. If, however, you mistype part of the CREATE syntax, Oracle won't be able to figure out what you are trying to do, and won't store the code.