The first method involves running Windows Notepad, typing your PL/SQL code (or SQL queries) into it, and then copying and pasting from Notepad into SQL*Plus to execute the desired code. This method is ideal for experimenting with short snippets of PL/SQL code and SQL queries. You can keep several related items in the same text file where you can easily call them up when you want to work on them.

The second method makes use of a SQL*Plus command to execute a file. For example, if you have a text file named test.sql with the code from Listing 1.2, you could execute that file by typing this command:

SQL> @c:\a\test

The resulting SQL*Plus screen output would look like Figure 1.5.

Figure 1.5.
Executing a PL/SQL
block from a file.

Executing commands from a file like this is most useful in cases where you are re-creating a stored procedure, function, or database trigger and you have the definition already stored in its own text file.

The third option involves using the SQL*Plus EDIT command to invoke your system's text editor. Under Windows, this will again be Notepad unless you have specifically defined a different editor. When you issue the EDIT command, SQL*Plus will launch Notepad and automatically place in it the text of the most recently executed PL/SQL or SQL command. See Figure 1.6 for an example of this.

Figure 1.6.
Using the SQL*Plus
EDIT command.

After you've brought up Notepad, you can edit the PL/SQL block to your satisfaction and then exit from Notepad, being sure to save the file. When you save your file, SQL*Plus will not immediately reexecute it. It is merely placed in an internal buffer. You must use the / command, by typing / on a line by itself, in order to execute the code you just edited.

Using the EDIT command works well as long as you keep a couple of important things in mind. The entire file must be executable, so unlike the first method, you cannot keep scraps of PL/SQL lying around in the file for later reference. Another thing to keep in mind is that SQL*Plus remembers only the most recent SQL command or PL/SQL block.

Which of these three methods you choose to use is up to you, and depends in part on your personal preferences. You are likely to find the first method, copying and pasting between Notepad and SQL*Plus, most useful during the first few chapters of this book. As you write ever larger PL/SQL functions and procedures, you will find yourself gravitating toward keeping each in its own file.

Writing Your First Function

Perhaps one of the most useful things you can do with your knowledge of PL/SQL is to use it to write stored functions and stored procedures. Encapsulating the code you wrote earlier into a stored function enables you to compile it once and store it in the database for future use. Then the next time you want to run that PL/SQL block, all you need to do is invoke the function. Using SQL*Plus, type in the input code shown in bold in Listing 1.3, which will create a PL/SQL function to return the value that was output by Listing 1.2.

INPUT/OUTPUT
Listing 1.3. The SS_THRESH function.

1: CREATE OR REPLACE FUNCTION ss_thresh
2: RETURN NUMBER AS
3:   x     NUMBER(9,2);
4: BEGIN
5:   x := 65400;
6:   RETURN x;
7: END;
8: /
9: Function created

ANALYSIS
Compare the code in Listing 1.3 to that in Listing 1.2. Notice that the keyword DECLARE has been replaced in lines 1 and 2 by the words CREATE OR REPLACE FUNCTION ss_thresh RETURN NUMBER AS. This will be explained further on Day 3. Also notice that the calls to dbms_output.put_line() have been replaced by the RETURN command (line 6), which returns the value of the variable X to the caller. The only output from Listing 1.3 is a confirmation that the function has been successfully created, which is shown in line 9. Figure 1.7 shows how your SQL*Plus screen will look after creating the SS_THRESH function.

OUTPUT
Figure 1.7.
Creating the
SS_THRESH function.

Notice that Oracle has created the function. SQL*Plus indicates this by displaying the words Function created.

Finding Compilation Errors

You probably were able to type in the code from Listing 1.3 and create the SS_THRESH function with no errors. However, that might not have been the case. To show you how to deal with an error, Listing 1.4 contains the same code as Listing 1.3, but with one small error.

INPUT/OUTPUT
Listing 1.4. The SS_THRESH function with an error.

 1: CREATE OR REPLACE FUNCTION ss_thresh
 2: RETURN NUMBER AS
 3:   x     NUMBER(9,2);
 4: BEGIN
 5:   x = 65400;
 6:   RETURN x;
 7: END;
 8: /
 9: Warning: Function created with compilation errors.

Unlike most compilers, which will display a listing of errors found in source code, Oracle stores any errors it finds in a database table named USER_ERRORS. If you want to see the specific details, and you probably do, then you need to retrieve the error listing yourself. Use the SQL*Plus command SHOW ERRORS, as shown in Listing 1.5, to do this.

INPUT/OUTPUT
Listing 1.5. The SHOW ERRORS command.

 1: SHOW ERRORS
 2: Errors for FUNCTION SS_THRESH:
 3:
 4: LINE/COL ERROR
 5: -------- ---------------------------------------------------------------
 6: 5/5      PLS-00103: Encountered the symbol "=" when expecting one of the
 7:          following:
 8:          := . ( @ % ;
 9:          The symbol ":= was inserted before "=" to continue.

ANALYSIS
As you can see, the error listing has two columns of output. The first column contains the line number where the error occurred and also the character position within that line. The second column contains the specific error message. In this example, the error occurred in line 5 at the fifth character position. The error message tells you that Oracle encountered an equal sign when it was really expecting something else. That "something else," in this case, is the assignment operator, represented by :=.

Figure 1.8 shows the SQL*Plus screen as it would look after executing Listings 1.4 and 1.5.

Figure 1.8.
Error listing for
SS_THRESH.