Unit II

Preparing for OCP Exam 2: Developing PL/SQL Program Units

Chapter 6

Procedures and Functions in PL/SQL development

In this chapter, you will cover the following areas of PL/SQL development:

• Developing stored procedures and functions
• Using SQL*Plus to develop procedures and functions
• Using Procedure Builder to develop procedures and functions
• Handling exceptions in PL/SQL

At the end of the last section, you covered several key introductory areas of developing PL/SQL. Items like cursor for loops and block structure are the building blocks for understanding how to code using Oracle’s procedural extension to SQL. However, these items are like I-beams, strong enough to hold together a building, but by themselves they are just a ton of metal. This chapter will introduce you to the creation of blueprints that hold PL/SQL building blocks together in the same way that an architectural blueprint holds a building together. These blueprints are called stored procedures and functions. Stored procedures and functions are similar to anonymous PL/SQL blocks in that they have declaration sections, execution sections, and exception handlers. However, stored procedures and functions have at least two significant differences from anonymous PL/SQL blocks. The first difference is that a stored procedure or function has a name, while an anonymous PL/SQL block has no name, thus being anonymous. You simply submit the anonymous PL/SQL block, and Oracle runs it, while a stored procedure or function is stored in the database, and can be called upon to do its thing without having to be resubmitted by the user. The second key difference is that stored procedures and functions allow you to pass in parameters, while anonymous PL/SQL blocks allow no parameter passing. This feature makes stored procedures and functions flexible and usable again and again without the need to recompile the code. There are also some differences between stored procedures and functions themselves. A stored procedure can accept one or many parameters, or none at all, and return one or many values, or none at all. In contrast, a function can accept one or many parameters, or none at all, but a function must always return one and only one value, without exception. In this chapter, you will learn more about developing stored procedures and functions using different Oracle products. The use of SQL*Plus for developing stored procedures and function is covered. Additionally, the use of Procedure Builder to develop both client-side and server-side PL/SQL procedures and functions will be covered. Since this exam is the same for both 1.6 and 2.0 OCP Application Developer certification tracks, you will focus your attention on Procedure Builder 2.0 in these chapters. Overall, the contents of this chapter comprise about 24% of OCP Exam 2 test content.

In this section, you will cover the following points on developing stored procedures and functions:

• Permissions and PL/SQL programs
• Creating PL/SQL procedures
• Parameter passing
• Creating PL/SQL functions

This section will cover several important areas of client-side and server-side PL/SQL development. There are many ways to create PL/SQL code, and several ways to invoke it as well. This section will discuss the server-side creation of procedures and functions with Oracle’s workhorse interface, SQL*Plus. In recognition of the ungainliness of a command line interface for everything, however, Oracle offers the PL/SQL developer a tool for writing and working with client-side PL/SQL code. This tool is called Procedure Builder, and it is part of the overall Oracle development package covered by the OCP Application Developer track. Procedure Builder has many features that other tools for PL/SQL development have, plus a few that others don’t. In addition to introducing the reader to Procedure Builder, this section will discuss the invocation of procedures and functions from both the SQL command line in SQL*Plus, within Procedure Builder, and within other PL/SQL programs. Finally, a discussion on handling exceptions will be offered.

Procedures and functions are the plan through which the power of PL/SQL constructs is delivered. Many books offer a discussion of the importance of using procedures and functions to encapsulate complex programming logic beneath a simple procedure or function call. For certification purposes, you will need to know what modularization is and how procedures and functions offer it. However, more important for passing OCP is that you know how to apply the appropriate methods to create procedures and functions. First, we explore the methods for creating procedures and functions in SQL*Plus.

A system privilege called create procedure governs the user’s ability to compile stored procedures and functions and store them on the Oracle database server. Without this privilege, the user may develop PL/SQL code in anonymous blocks, compile those blocks and run them, but will not be able to compile and load functions and procedures into Oracle for later use. Usually, this privilege is granted by the DBA for the system. The statement the DBA will use for granting create procedure looks similar to the following code block.

GRANT create procedure TO athena;

Once developed, anyone can invoke a stored procedure or function provided they have security permissions set to do so. The issuance of security privileges and/or roles is usually owned by either a security administrator or the DBA in most IS organizations. However, during periods of intense development, it is beneficial to allow developers to manage their own security. From an administration perspective, the ability to run a stored procedure or function is an object privilege called execute. To allow a user the ability to execute a procedure or function, either the procedure owner or privileged user (such as the DBA) should issue the following command from SQL*Plus. In this block, the ability to execute the delete_employee( ) function is granted to lucky user ATHENA.

SQL> GRANT execute ON delete_employee TO athena;

Important to note is that, if user SPANKY owns the delete_employee( ) procedure for which execute privileges were just granted to user ATHENA, then user ATHENA executes delete_employee( ) with all the same object access privileges granted to user SPANKY. This is a general rule in PL/SQL, with one exception. If user SYS grants user ATHENA access to develop dynamic SQL with the use of the DBMS_SQL package, this does not mean ATHENA can execute any possible SQL statement on the access authority of SYS. But other than that situation, the user executing the PL/SQL block does so with the object access privileges of the owner of the procedure, not their own.

Recall in chapter 5 the discussion of anonymous PL/SQL blocks. When a user submits an anonymous block to Oracle for processing, the RDBMS parses the code into something it can process and then processes it. When Oracle is done processing the code, the parsed PL/SQL block eventually goes away either if the user doesn’t execute the anonymous block again or when the user ends their session. Unless the user kept the PL/SQL in a file stored external to the Oracle database, the user loses all that programming time and effort. Consider the following block of code.

DECLARE
my_raise_empid VARCHAR2(20) := ‘40593’;
my_delete_empid VARCHAR2(20) := ‘59384’;
my_employee_rec employee%ROWTYPE;
BEGIN
SELECT *
INTO my_employee_rec
FROM employee
WHERE empid = my_raise_empid;

INSERT INTO process_emp_raise (empid, lastname, firstname, salary)
VALUES (my_raise_empid, my_employee_rec.lastname,
my_employee_rec.firstname, my_employee_rec.salary+10000);

DELETE FROM employee
WHERE empid = my_delete_empid;
END;

This anonymous PL/SQL block will be forgotten by Oracle unless the user creates a way for the code to be remembered. Procedures and functions are this way for Oracle to remember. The keywords create procedure allow the user to define the preceding anonymous PL/SQL block in such a way that Oracle will not execute the block when the user submits it. Rather, Oracle will compile it and store it so that the user can execute the code later or whenever she wants. The result is a stored procedure in the Oracle database. The following code block demonstrates the creation of a procedure using the PL/SQL from the anonymous block, with modifications necessary for this purpose.

CREATE PROCEDURE raiseanddelete(
p_raise_empid IN VARCHAR2,
p_delete_empid IN OUT VARCHAR2
) AS
my_employee_rec employee%ROWTYPE;
BEGIN
SELECT *
INTO my_employee_rec
FROM employee
WHERE empid = p_raise_empid;

INSERT INTO process_emp_raise (empid, lastname, firstname, salary)
VALUES (p_raise_empid, my_employee_rec.lastname,
my_employee_rec.firstname, my_employee_rec.salary+10000);

DELETE FROM employee
WHERE empid = p_delete_empid;

p_delete_empid := ‘00000’;
END;

When the user submits this statement, Oracle will parse and compile this program into an executable form, and store the executable form in the database as a named procedure. Oracle will not execute the select, insert, or delete statements, only store them as SQL statements that will execute when the user calls the procedure. The procedure is then available for usage.

Notice a few differences in the named procedure and the anonymous block. First, there is no keyword declare. Does that mean there is no declaration section? No, instead the declaration section is signified by the keyword as, highlighted in the fourth line of the preceding code block. This keyword must be used when the developer wants to declare variables other than those being passed in as parameters to the procedure. If the developer has no additional variables to declare, the developer can substitute the keyword is for as.

To alter an existing program unit, the or replace syntax is a useful addition to the more basic create procedure, create function, create package, or create package body commands. The following code block shows how to use or replace in the definition of a procedure that already exists in Oracle.

CREATE OR REPLACE PROCEDURE raiseanddelete(
p_raise_empid IN VARCHAR2,
p_delete_empid IN VARCHAR2
) AS
my_employee_rec employee%ROWTYPE;
BEGIN
SELECT *
INTO my_employee_rec
FROM employee
WHERE empid = p_raise_empid;

INSERT INTO process_emp_raise (empid, lastname, firstname, salary)
VALUES (p_raise_empid, my_employee_rec.lastname,
my_employee_rec.firstname, my_employee_rec.salary+10000);

DELETE FROM employee
WHERE empid = p_delete_empid;
END;

Notice also the keyword in, highlighted in the second and third lines of the preceding block. This keyword indicates that the procedure will accept a value passed in for that parameter, but will not return a value. Other keywords that can be substituted for in are out and in out. Their meanings are:

• In Value passed in for this parameter, but not returned
• Out Value passed out for this parameter, but none passed in
• In Out Values are passed in and returned for this parameter

The keywords used in the declaration of variables that will have values passed in as parameters to the procedure call are highly important. An in parameter will specify that the variable named will be assigned that value passed into the procedure in the appropriate place specified. The out keyword specifies that the caller of the procedure will put a variable in the procedure call. When the procedure is done processing, whatever value is in that variable will be returned to the caller of the procedure. The in out keyword combination states that the procedure should expect to receive a value into the variable defined to handle that parameter, and whatever value is in that variable within the stored procedure when the procedure is done executing will be passed back to the caller. The following block illustrates the principles behind parameter passing and the in, out, and in out keywords referring to the raiseanddelete( ) procedure that has already been defined. Since the second parameter variable is defined as an in out variable, a value may not be passed in as a parameter, but rather a variable must be defined at the procedure caller level that contains a value passed in and can hold a value passed out.

raiseanddelete(‘50398’,’54984’); /* No, 2nd parm won’t accept return value */
raiseanddelete(‘59483’,delete_variable); /* OK! */

Shortly, you will cover the three ways or modes parameter values can be passed to a block, which are: positional, named association, and a combination of the two.

The next type of named PL/SQL block we will discuss is the function. Like a procedure, you can create a function to execute a series of PL/SQL statements. Once the function is compiled and stored in the system, those granted execute privileges on that function can then use it. The main clause for changing an anonymous PL/SQL block to a stored function is create function. It is used in the same way as create procedure. The following code block demonstrates the code of a sample function.

CREATE FUNCTION find_mouse(
p_mouse_name IN VARCHAR2,
p_house_name IN OUT VARCHAR2 -- In/out parm in functions not advised
) RETURN VARCHAR2 IS
my_room mouse_house.room%TYPE;
BEGIN
SELECT room /* get the data */
INTO my_room
FROM mouse_house
WHERE mouse_name = p_mouse_name
AND house_name = p_house_name;
RETURN my_room; /* return the result */
END;

The function above has one purpose – it accepts two values and returns a value. There are two variables defined after the create function keywords to store the values users will pass into the function as parameters. The second to last line in the block contains a new keyword – return. This keyword highlights an important point you should remember about function process structure. A function accepts one or many values as parameters, or none at all. But, a function always returns exactly one result. You must remember to code a function call to handle a result when you call it. More information will be provided on procedure and function calls later. For now, simply recall that a function always returns a value.

Although it will compile and run, there is an error in the find_mouse( ) function! Can you find out where? The error relates back to the original definition of a function. A function accepts zero or more parameters, and returns one. Notice that the second variable defined for accepting values in as parameters is defined as an in out variable. Although, this definition is permitted by the PL/SQL engine, it is not generally advised to define functions with in out parameters. A function should return only the value defined for the variable specified in the return statement in the last line of the function in order to conform to a higher purity level for optimization and prevention of ill side effects. The correct listing for this stored function is listed in the following code block.

CREATE FUNCTION find_mouse(
p_mouse_name IN VARCHAR2,
p_house_name IN VARCHAR2
) RETURN VARCHAR2 IS
my_room mouse_house.room%TYPE;
BEGIN
SELECT room /* get the data */
INTO my_room
FROM mouse_house
WHERE mouse_name = p_mouse_name
AND house_name = p_house_name;
RETURN my_room; /* return the result */
END;

Notice finally that the return command is the last statement in the function. This design choice is done for a reason. First, once the return command is issued the function will return control to the caller. Also, this command defines what value will be returned to the caller. Say, for example, the developer wants the function to do a series of tests. Execution of a subsequent test depends on whether the previous test failed, and return different values based on the result of a test. The function may contain a series of tests, each with its own return value. The following code block contains an example to illustrate.

CREATE FUNCTION test_mouse_type (
p_fur_color IN VARCHAR2,
p_tail_length IN VARCHAR2
) RETURN VARCHAR2 IS
BEGIN
IF p_fur_color = ‘ORANGE’ AND p_tail_length = ‘SHORT’ THEN
RETURN ‘SHORT-TAILED ORANGEY ONE’;
ELSIF p_fur_color = ‘RED’ AND p_tail_length = ‘LONG’ THEN
RETURN ‘LONG-TAILED FIREY ONE’;
END IF;
END;

In this section, you will cover the following points about using SQL*Plus to create procedures and functions:

• Entering PL/SQL code in SQL*Plus
• Invoking procedures and functions in SQL*Plus
• Code compilation in Oracle using SQL*Plus

Once your PL/SQL code has been developed, it must be placed somewhere to be effective. This place may be on the Oracle server or within a client application. You must have some mechanism to get PL/SQL from the typed word to executable code. This section covers one method for doing it. The tool you will use in this section is SQL*Plus, Oracle’s workhorse application for interfacing with the database. This application will allow you to enter PL/SQL code for compilation and execution on the Oracle database server, making it a solution for server-side PL/SQL development. You will cover the creation of PL/SQL in SQL*Plus as well as compiling code with it, along with how to run the named code you compile with SQL*Plus.

With an idea of the keywords required in mind, shift your attention now to the process of entering the procedure or function into Oracle. For that purpose, the developer may choose to use SQL*Plus. The entry of PL/SQL at the SQL prompt is the same as entry of SQL statements, presented in chapter 1. However, entry of PL/SQL in this way falls victim to the same difficulties as entry of SQL statements. Once you press enter on the line of code you keyed in, you can no longer edit that line without going through a lengthy process of referencing the line number and using the change command. The challenge of entering PL/SQL code directly from the SQL prompt in SQL*Plus is shown in figure 6-1.

There are alternatives. The first is to edit the PL/SQL code with the edit command from the SQL*Plus prompt. This command invokes a text editor and places the code currently being written into that editor. This allows the developer to write code in a more user-friendly environment. This process is described in chapter 1 and demonstrated in figure 6-2. The other alternative is to write the entire PL/SQL block using a text editor and save the file with a .sql extension. Then, in SQL*Plus, the code can be loaded using the get command. Assuming there is a file containing a PL/SQL block called delete_employee.sql, the following example shows how the user can load that script into SQL*Plus for modification or execution. The get command loads the contents of the script into the SQL*Plus buffer, while the @ command actually loads the contents of the script into SQL*Plus and then submits the code to Oracle for compilation. Once compiled, the PL/SQL code is stored as part of the Oracle database.

SQL> get test_mouse_type.sql
1>CREATE FUNCTION test_mouse_type (
2>p_fur_color IN VARCHAR2,
3>p_tail_length IN VARCHAR2
4>) RETURN VARCHAR2 IS
5>BEGIN
6> IF p_fur_color = ‘ORANGE’ AND p_tail_length = ‘SHORT’ THEN
7> RETURN ‘SHORT-TAILED ORANGEY ONE’;
8> ELSIF p_fur_color = ‘RED’ AND p_tail_length = ‘LONG’ THEN
9> RETURN ‘LONG-TAILED FIREY ONE’;
10> END IF;
11>END;
SQL> /
Function created.

Or,

SQL> @test_mouse_type
Function created.

Recall at the end of the lesson on parameter passing that some mention of the modes for parameter passing was made. In this lesson, you will expand your understanding of the three modes for parameter passing. Once execute privileges are established, the procedure or function can be invoked in several ways. These methods include invocation from SQL*Plus or from other PL/SQL procedures and functions. You will explore invoking stored procedures and functions from SQL*Plus first. Executing stored procedures standalone in SQL*Plus is handled with the execute command. The syntax is execute procedurename(val1,…) followed by a semicolon. The val1 and val2 items refer to values that are passed into the procedure. If there are no values passed in, there needn’t be any parentheses. From the SQL prompt, the commands issued may look something like the following code block.

SQL> EXECUTE delete_employee(‘49384’);
PL/SQL procedure successfully completed.
SQL> EXEC raise_salary_for_employee(‘49849’,’10’,’PERCENT’);
PL/SQL procedure successfully completed.
SQL> EXECUTE cmpare_apple_to_orange(‘APPLE59385’,‘ORANGE59438893’);
PL/SQL procedure successfully completed.

Note that in this example, the procedures all use positional parameter passing mode. In other words, the values passed into the procedure will be assigned to parameters according to the position of the value and the position of the parameter. The first value gets assigned to the first parameter, the second to the second, and so on. Alternately, for procedures that accept multiple parameters, you can use the parameter name and a reference pointer to identify passed values that are not in the order specified by the procedure. The following code block demonstrates.

SQL> EXECUTE cmpare_apple_to_orange(p_orange=>‘ORANGE59438893’,p_apple=> ‘APPLE59385’);

Notice in the previous block that the user can abbreviate the execute command with exec. However, Oracle gives no indication of how the procedure performed, or even if it did what it was supposed to do. In a later section, "Managing Procedures and Functions," you will explore how to make Oracle identify its progress and other pertinent information about the performance of the procedures and functions you execute. Notice also that in the code block an actual value must be passed. Finally, an interesting error will occur if you attempt to refer to a procedure from the SQL prompt without the execute command – Oracle treats the procedure name as though it is a reference to a variable, and reports that the variable has not been declared.

If the developer wants to use variables and call the PL/SQL procedure or function repeatedly, there will need to be some additional development. This leads the developer to considering how to call PL/SQL procedures and functions from within PL/SQL. Functions are called most effectively from other PL/SQL blocks. At the very least, the developer must code an anonymous PL/SQL block to declare a variable to hold the return value that the function must produce. The developer does not call a function directly and independently as she does with a procedure, rather the call is made usually in reference to a variable, or as a function in a select statement, as with any other SQL statement. Consider the following function call. A variable is defined in the anonymous block’s declaration section, and populated with a result from a function, which accepts two numeric value and returns one.

DECLARE
my_return_var NUMBER;
BEGIN
my_return_var := return_hypotenuse(3,4);
END;

Or,

SELECT return_hypotenuse(3,4)
FROM dual;

Or even,

DECLARE
my_return_var NUMBER;
BEGIN
SELECT return_hypotenuse(3,4)
INTO my_return_var
FROM DUAL;
END;

Procedures can be called from anonymous or named PL/SQL blocks as well. The syntax is actually quite simple for calling a PL/SQL procedure from another procedure. Simply name the procedure and pass in either the variables or appropriate values the procedure expects. There is not even a need to use the execute command, the name of the procedure being called will suffice.

BEGIN
process_junk(x,y);
END;

Compiling PL/SQL code using SQL*Plus and PL/SQL stored in text files on your operating system can be a challenge. After running your create procedure statements as demonstrated, if there are problems, Oracle will return with a message saying "Warning: Procedure created with compilation errors." Your problems arise when you attempt to find out where the problem resides exactly. There is a view in the Oracle data dictionary called USER_ERRORS or ALL_ERRORS that lists the errors encountered in this compilation. The following code block shows you trying to obtain your errors from the USER_ERRORS view.

SQL> select * from user_errors;

One of the biggest problems you will encounter in trying to find and correct your errors is when you select your errors from USER_ or ALL_ERRORS. The line number Oracle places in this errors table relates directly to the line in the buffer just executed containing your PL/SQL code. The problem is that SQL*Plus saves only the most recent statement in the memory buffer, which at this point is your select statement. You are essentially left to your own devices to figure out where the problem in your PL/SQL code resides, and unfortunately for this debugging method will need to modify your code and recompile it several times before the thing works. More discussion on compiling and debugging your code with SQL*Plus and Procedure Builder will come shortly.

One popular method for getting your compilation errors out of SQL*Plus is to use the show errors command. This command gives you the errors the PL/SQL compilation engine encountered when compiling your program unit – with the added bonus of not losing your buffer, making it easier to locate and correct your errors from SQL*Plus. The following code block illustrates.

SQL> create procedure flibber as
2> begin
3> select * where my_thing = 6;
4> end;
5> /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE FLIBBER

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/10 PLS-00103: Encountered the symbol "WHERE" when expecting one of
the following:
from into

Shortly, you will also see how Procedure Builder allows you to compile your code more easily than in SQL*Plus, even when you use the show errors command, because of Procedure Builder’s GUI that shows you the errors on the same screen where you manipulate the code.

Describe a problematic situation you encounter when compiling code in SQL*Plus. What are some ways to resolve it within SQL*Plus?

In this section, you will cover the following points about creating procedures and functions with Procedure Builder:

• Using Procedure Builder command line to develop PL/SQL
• Parts of the Procedure Builder GUI
• Using Procedure Builder GUI to develop client-side PL/SQL
• Using Procedure Builder GUI to develop server-side PL/SQL
• Running PL/SQL programs in Procedure Builder

You may be a little surprised that, at the onset, it looks like OCP covers a fair amount of Procedure Builder usage. It doesn’t. Instead, you should focus your attention on learning core concepts about developing PL/SQL programs first, then learning Procedure Builder second. That said, it is still not a waste of your time to learn Procedure Builder as it is covered in this text. Procedure Builder is a good tool, because as shown, entering procedures and functions with SQL*Plus is possible, if a little ungainly. Instead, you can use components of the Developer/2000 program development tools. In particular, Oracle’s Procedure Builder product allows the user to develop stored procedures and functions in Oracle. Many developers have found the SQL*Plus and favorite text editor approach cumbersome. Oracle addresses issues of ease of program development, debugging, and version control with various Procedure Builder features. This discussion will describe the use of Procedure Builder to develop procedures and functions in PL/SQL on both the client side and the server as well.

There are two ways for developing code in Procedure Builder, line mode and with the GUI. Procedure Builder line mode is similar to SQL*Plus in that you must enter all your code and operations via the command line interface. This method is used mainly for development of PL/SQL client-side procedures and functions. There are several commands for Procedure Builder on the command line that are different both from SQL and SQL*Plus. In general, you can use the help command to find out what the commands in PL/SQL are and the help command_name syntax for information about a specific command. Figure 6-3 demonstrates Procedure Builder’s command line interface.

Creating a procedure at the command line in Procedure Builder is a simple process. When you initiate Procedure Builder in line mode, you actually put yourself into an interactive PL/SQL coding environment where any PL/SQL statement is valid. It is virtually identical to writing PL/SQL code in SQL*Plus, without some of the additional overhead. For example, whereas if the user wanted to write the classic "Hello, World." program in PL/SQL using SQL*Plus, she may write the following.

SQL> set serveroutput on;
SQL> execute dbms_output.put_line(‘Hello, World.’);
Hello, World.

PL/SQL program successfully completed.
SQL>

You execute a valid PL/SQL statement, but the problem is clear – you need to write a minimum of two lines in order to obtain only one result. Thus, although SQL*Plus is capable of Procedure Builder functionality, you can use Procedure Builder to execute the same process more easily. Consider the same program done in Procedure Builder.

PL/SQL> text_io.put_line(‘Hello, World.’);
Hello, World.
PL/SQL>

Notice another difference – the procedure call used to produce output is different. In chapter 5, you learned a little bit about the DBMS_OUTPUT package. It is a PL/SQL package available on the Oracle server that contains procedures and functions designed to allow the Oracle database server to produce output. TEXT_IO is a package built-in to Procedure Builder and available only when you run Procedure Builder for development of client-side PL/SQL code. Later in this unit you will learn more about what packages are and how to create them, and also about the packages that are built into both the Oracle database and into Developer/2000.

However, the capabilities of Procedure Builder in line mode are very similar to SQL*Plus, with a few differences. For example, if you have some PL/SQL stored in a text file called find_mouse.sql, you can load it into the SQL*Plus execution buffer with the get command, as demonstrated in the following code block.

SQL> get find_mouse.sql

There are actually two different types of PL/SQL code in Procedure Builder, and they are files and libraries. A file can be thought of as the equivalent of a standalone procedure or function in a file in SQL*Plus, while a PL/SQL library is more like a package in that it can contain several different procedure and functions for use. The equivalent command in Procedure Builder to the get command in SQL*Plus would be load file. If, however, you want to use a library of PL/SQL procedures and functions, you would attach the library for use. Both methods are demonstrated in the following block.

PL/SQL> .load file find_mouse.sql
PL/SQL> .attach library mouse_lib_01a.pll

Notice both in the code block and in Figure 6-3 that the command for loading files in Procedure Builder is preceded by a period(.). This period is important to remember, because otherwise Procedure Builder will return an error stating it was expecting you to treat the word "load" as if it were the name of a variable.

A new type of file introduced here is the PL/SQL library file, suffixed by .pll as noted in the code block above. One final point to remember about Procedure Builder in command line mode is that you don’t necessarily need to be logged into an Oracle database in order for your PL/SQL to work. This is because Procedure Builder line mode has a PL/SQL interpreter built into it. This feature works well if you want to write PL/SQL and execute it interactively with Oracle, but if you want to write stored procedures and functions, you must be connected to the Oracle database so that Procedure Builder has somewhere to store it. This fact is true of SQL*Plus as well, but since the user views database login as an implicit part of SQL*Plus, it’s easy to forget that the stored procedures are actually kept in the Oracle database and not available to you when not logged in.

The second way to use Procedure Builder is with the graphical user interface. The Procedure Builder GUI consists of five different modules to help you develop code. Figure 6-4 features the Procedure Builder GUI with two modules displayed, the object navigator and the PL/SQL interpreter. The use of Procedure Builder in GUI mode requires understanding the different modules available. Here we will cover a tour of viewing and using each of the modules, along with some key facts on their usage.

The object navigator is a module that allows the developer to look at all PL/SQL program units, attached and built-in libraries, debug options, stacks, and database objects available for use. This interface treats all PL/SQL program objects like directories in a filesystem, and allows you to drill down into different code types to see exactly what’s out there for your use.

The program unit editor is a module that allows the developer to rapidly develop client-side PL/SQL procedures, functions, package specifications and package bodies. If you write a lot of PL/SQL, this tool will become your favorite quickly, because it builds the basics for different types of PL/SQL blocks automatically, and allows you to compile your code and see errors interactively so that you can cut the time you spend debugging. More on the challenges of debugging PL/SQL using SQL*Plus and the Oracle database and how Procedure Builder simplifies that process later.

The interpreter is a module that allows the developer to run and debug client- and server-side PL/SQL commands interactively with a standalone PL/SQL interpreter engine. Having already used the Procedure Builder in Line Mode, you should already find this component of the GUI to be familiar or at least recognizable. This component is essentially the same as Procedure Builder line mode, with its ability to read PL/SQL statements interactively and the need to place a period before each special command like load. Notice, however, some key differences in the GUI approach with the availability of buttons at the top of the window. These buttons are used for debugging, which we will cover shortly.

The database trigger editor is a module that allows the developer to develop, compile, and run server-side database triggers. Triggers are objects in the database that work in conjunction with tables. They execute or "fire" automatically when a specified activity takes place. For example, a delete trigger will fire every time a user attempts to delete data from the table the trigger is attached to. If you are not connected to the database, this option will be grayed out.

The stored program unit editor is a module that allows the developer to code and modify server-side PL/SQL code of the Oracle database. To use it, you must be connected to a database and able to browse through the database stored procedures, functions and packages. The main difference between this editor and the program unit editor is that this editor is for procedures and functions stored on the database and the program unit editor is for works in progress or for PL/SQL code that exists outside the Oracle database. If you are not connected to the database, this option will be grayed out.

Probably the easiest way to develop a named PL/SQL block in Procedure Builder is to use the program unit editor. If this module is not running already, you can open it by clicking on Program>>Program Unit Editor on the Procedure Builder menu bar. Review figure 6-4 to find the location of the Program pull-down menu. Alternately, you may go into the object navigator module, highlight the Program Units node and click on the create button in the vertical toolbar on the left-hand side of the screen.

Once in the program unit editor, you can either click on the new button at the top and center of the module, or will be automatically prompted to type in a name for your new PL/SQL block, and to select a block type using the radio buttons. The options are procedure and function, the use for both of which you should understand by now. The other two may be a bit less familiar. They are the package specification and the package body. You will cover package development and use later in the unit, but for now you should understand that a package is basically a control structure for other PL/SQL code like stored procedures and functions. Other PL/SQL blocks can be bundled together into a package for portability and encapsulation of global variables that may be shared by several procedures and functions, and for other reasons. There are two parts to every package, a package specification and a package body. The specification contains a list of procedures and functions along with the required parameters for each, and optionally some comments about the functionality and use of each package component. The package body contains the source code for each procedure and function in the package.

Once you have named your PL/SQL block and defined the block type, click ok. Procedure Builder then builds the basic components of the PL/SQL block you selected. If you want to build a function called find_mouse( ) using the program unit editor, figure 6-5 displays what you put into the new program unit interface and figure 6-6 displays the result Procedure Builder provides for you. Although you need to code the rest of the function yourself, Procedure Builder saves you the time it takes to debug trivial problems that may arise from incorrect create function statements.

Once you have developed your PL/SQL code, it should be compiled. In the same area at the top of the program unit editor as the new button is another button title compile. Pressing this button causes Procedure Builder to compile your PL/SQL block. If there are any errors, Procedure Builder notifies you and you can correct them. Otherwise, if the PL/SQL compilation works, then you can close the PL/SQL program unit with the close button at the top of the program unit editor display and the program unit will be stored as a program units in the object navigator module, as shown in Figure 6-7. After developing the program unit, you can place that unit into a library by simply dragging and dropping the created program unit from under program units and placing it under PL/SQL libraries in the object navigator module.

Finally, a note about files containing client-side PL/SQL code in Procedure Builder. When you save a program unit, Procedure Builder stores your code in a binary file with the extension .pll attached to it. If you want to dump your PL/SQL code to flat file, you can do so by choosing the file>> Export command from the menu. This way, you have a copy of the PL/SQL you just developed if you want to load the code into Oracle via SQL*Plus later.

What module is used for rapid PL/SQL program unit development in Procedure Builder GUI mode, and how is it used?

Using Procedure Builder to develop server-side stored procedures and functions is slightly different. First of all, you must be connected to the database in order to work with server-side PL/SQL. This is done by clicking on menu options file>>connect or ctrl+j, and you provide an Oracle login ID and password, along with a SQL*Net connection string so Procedure Builder knows where to look for its network connection information.

Once logged in, you will see a plus (+) sign in the box next to the Database Objects item in the object navigator. If you click on the box, you will see a list of database objects available to Procedure Builder categorized by schema owner. If you drill down into your schema owner, Procedure Builder will show you a further breakdown of objects owned by this schema by database objects, program units, PL/SQL libraries, tables, and views. Further drill-down into program units will list out all stored procedures in the database already. Rather than working with an existing procedure or function, you will create a new one. If you pull down the program menu, notice that now all options including database trigger editor and stored program unit editor are now darkened in and ready for use. The appearance of the Procedure Builder GUI at this point is shown in figure 6-8.

Now you will develop a server-side stored procedure. You can either click on program>>stored program unit editor menu item to bring up the stored program unit editor module to develop a stored procedure, or double click on the Database Objects node in the object navigator module, then again on the Stored Program Units node. This may take a moment as Procedure Builder obtains source code for your existing PL/SQL code. To modify an existing program, click on the down arrow next to the name pull-down menu at the top-right of the stored program unit editor window. You may then scroll through to find your stored program unit. To create a new stored procedure, click on the new button above the owner pull-down menu at the topmost-left of the stored program unit editor window. As with developing client-side program units, you are prompted to enter a name for your new PL/SQL block along with its type. When finished, click ok, and you will then be brought to the stored program unit editor module window, where you can modify the code provided until your procedure is developed.

Compiling your stored PL/SQL code in the stored program unit editor is almost as simple as compiling client-side program units, yet there is no compile button in the stored program unit editor module. So, instead you must click the save button, and Procedure Builder attempts to save your code in the Oracle database. However, if there is an error, Procedure Builder will still notify you in a new bottom window of the stored program unit editor, allowing you to change it. In figure 6-9, you will see how Procedure Builder responds to your entry of procedure elaborate_calc( ), which refers to the TEXT_IO package. However, recall that TEXT_IO is a client-side package only, and its equivalent is DBMS_OUTPUT on the database side. So, the stored program unit editor reports an error. If you then modify the code and try saving it again with the save button, the error and entire bottom window goes away. The code is now written, saved in the database, and you can click the close button to send the stored program unit editor module away.

PL/SQL can be invoked in Procedure Builder with slightly less effort than in SQL*Plus. Recall that unnamed or anonymous PL/SQL code can run against the PL/SQL interpreter without being compiled. Individual PL/SQL statements run interactively as well at the PL/SQL interpreter prompt. Named PL/SQL blocks on the client side can be invoked in a different way. Once the program unit is compiled and stored, you can invoke it simply by typing in the name of the procedure at the interactive PL/SQL prompt within the Procedure Builder GUI. The code that would appear in the PL/SQL interpreter window for a procedure called list_available_mice( ) that prints all mice available at a pet store after a certain date is in the following block.

PL/SQL> LIST_AVAILABLE_MICE(‘09-NOV-2000’);
MOUSE AVAILABILITY
----- ------------
BILLY 20-NOV-2000
MILLY 21-NOV-2000
JILLY 05-DEC-2000
EEEKY 26-DEC-2000
GRUB 04-JAN-2001

Invoking a PL/SQL block with Procedure Builder in line mode is accomplished in the same way. However, when working in Procedure Builder from the command line you usually must first attach the library containing the PL/SQL block or Procedure Builder won’t know what to execute. Assume for this example that you have a library called MICE_LIB_01a, containing procedure list_available_mice( ). To invoke this procedure from the command line in Procedure Builder, you must first attach the library to Procedure Builder and then you may execute the PL/SQL block. The following code listing demonstrates.

PL/SQL> .attach library MICE_LIB_01a
Attaching library in file MICE_LIB_01a.pll, BEFORE other attached libs...
PL/SQL> LIST_AVAILABLE_MICE(‘09-NOV-2000’);
MOUSE AVAILABILITY
----- ------------
BILLY 20-NOV-2000
MILLY 21-NOV-2000
JILLY 05-DEC-2000
EEEKY 26-DEC-2000
GRUB 04-JAN-2001

You can also open or attach libraries in the object navigator node of the Procedure Builder GUI. Do this by clicking either on the PL/SQL Libraries node for opening libraries or on the Attached Libraries node for attaching libraries, respectively, and then clicking on the open button on the vertical toolbar of the module. Remember an important difference in working with PL/SQL libraries in Procedure Builder. If your library is open, that is to say it is located as a drill down item under the PL/SQL libraries item in the object navigator, that means it is open and the PL/SQL code bodies are available for modification. Those PL/SQL blocks are not necessarily ready to be run, however. An example is when you have opened your PL/SQL library with the .open library library_name command. When your PL/SQL library is attached, however, the code is available for execution in the PL/SQL interpreter module. You use the .attach library library_name command to do this.

Server-side PL/SQL is executed in the same way from the PL/SQL interpreter module, by referring to the PL/SQL block by name, followed by a semicolon. However, to execute server-side PL/SQL properly, you must be connected to the database.

In this section, you will cover the following points about handling PL/SQL exceptions:

• Handling Oracle-defined exceptions
• Handling user-defined exceptions
• Using the raise_application_error( ) procedure

Error handling in PL/SQL is performed using the exception handler. In the last chapter, you covered the basic coding constructs of an exception handler. There are three different types of exceptions, Oracle-defined, user-defined, and internal exceptions. Several exceptions are already built into PL/SQL, such as no_data_found and others. The best part about Oracle-defined exceptions is that they are raised automatically. In addition, it was shown that you create your own exceptions by declaring and raising them appropriately yourself in the PL/SQL block. Finally, internal exceptions allow you to associate an exception name with an Oracle error condition. In this section, you will learn more about how to handle Oracle-defined and your own exceptions. You will also learn how to use an important procedure in PL/SQL – the raise_application_error( ) procedure – for reporting PL/SQL errors.

Recall from chapter 5 the presence of Oracle-defined exceptions within the Oracle database. These exceptions are raised automatically whenever they occur in your PL/SQL code. There is a tip in chapter 5 stating that, if you do not code an exception handler (either the specific exception name or when others then clause) for predefined exceptions, you will not trap them in your code. This is true, what’s more, your users will receive the error message if you don’t attempt to trap all possible exceptions that may occur. So, make sure you code your PL/SQL exception handlers to detect and resolve Oracle-defined exceptions. Remember, Oracle always raises its own exceptions when they occur.

To review, some of the exceptions that can occur are when your select into statement returns no data or too much data, or when data selected into a variable does not match the variable’s type declaration. Also, Oracle will raise an exception if an attempt to divide by zero is made, a primary key or unique constraint violation is made, or if a cursor manipulation is made on a closed cursor. There are some others, but these several should be enough to refresh your memory. Refer back to chapter 5 if you’re still confused, or if you want the exact names of the Oracle-defined exceptions.

Assume your PL/SQL block contains a select into statement, similar to select_emp( ) in the following code block. If no data is returned from the EMPLOYEE table related to the EMPID passed into the procedure, Oracle will raise its own exception.

CREATE PROCEDURE select_emp(
p_empid IN VARCHAR2
) AS
my_employee_rec employee%ROWTYPE;
BEGIN
SELECT *
INTO my_employee_rec
FROM employee
WHERE empid = p_raise_empid;
END;

What’s more, since there is no coded exception handler for Oracle’s own exception, you will receive an error when you execute it. The following code block illustrates what will happen if you compile and execute the select_emp( ) procedure from the select_emp.sql file within SQL*Plus as it is written above.

SQL> @select_emp
Procedure created.
SQL> execute select_emp(‘593485’,’593939’);
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at SPANKY.SELECT_EMP, line 5
ORA-06512: at line 1
SQL>

Not exactly the type of thing you want to pass to your users, is it? So, you must add an exception handler. This procedure block requires only a simple one. You can do it in one of two ways; first you can identify the Oracle-defined exception by name in your exception handler and define how it should be handled. Alternately, you can identify the special catch-all exception, others, and define something special to happen in that way. Consider the first option, shown in the following code block.

CREATE PROCEDURE select_emp(
p_empid IN VARCHAR2
) AS
my_employee_rec employee%ROWTYPE;
BEGIN
SELECT *
INTO my_employee_rec
FROM employee
WHERE empid = p_raise_empid;
EXCEPTIONS
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘select_emp(): Found no data in EMPLOYEE.’);
END;

Alternately, the catch-all exception can be used, with appropriate change to the output. Remember, others will be the exception handler for any one of the variety of things that can go wrong. Observe in the following code block.

CREATE PROCEDURE select_emp(
p_empid IN VARCHAR2
) AS
my_employee_rec employee%ROWTYPE;
BEGIN
SELECT *
INTO my_employee_rec
FROM employee
WHERE empid = p_raise_empid;
EXCEPTIONS
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘select_emp(): An error occurred.’);
END;

Of course, you can code any number of exceptions to be handled in a multitude of different ways, just remember that an unhandled exception is passed to the caller of the PL/SQL block. Adding a when others clause to your exception handler to trap your Oracle-defined exceptions is usually a small preventive measure that yields a more robust application. Better still, code exception handlers for all Oracle-defined exceptions to identify exactly what has occurred, although in some cases you may find this to be overkill. Finally, you should code a special catch-all exception handler called when others then as the last handler to take care of all situations not explicitly coded for in other exception handlers.

In addition to Oracle-defined exceptions, PL/SQL allows you to define your own exceptions to be raised by the application. Recall from chapter 5 that user-defined exceptions must have code in all parts of the PL/SQL block, including declarations, execution, and exceptions sections. This is different from Oracle-defined exceptions, which need only have code in the exceptions section of the block in order to be handled properly. Observe the following version of select_emp( ). This version has a check in the code to see if this employee is active. If the STATUS column contains an "R" or "L" that means the employee is retired or on leave, respectively.

CREATE PROCEDURE select_emp(
p_empid IN VARCHAR2
) AS
my_employee_rec employee%ROWTYPE;
bad_status exception;
BEGIN
SELECT *
INTO my_employee_rec
FROM employee
WHERE empid = p_raise_empid;
IF my_employee_rec.status = ‘R’ OR my_employee_rec.status = ‘L’ THEN
RAISE bad_status;
END IF;
EXCEPTIONS
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘select_emp(): Found no data in EMPLOYEE.’);
WHEN BAD_STATUS THEN
DBMS_OUTPUT.PUT_LINE(‘select_emp(): Employee on leave or retired.’);
END;

Note that all components of the PL/SQL block have something in them addressing the user-defined exception. If the exception is not defined, the raising it will itself produce an error at compile time. If the exception is not raised, then Oracle will never behave appropriately in the presence of that exception. If there is no exception handler for the user-defined exception, control will pass to the exception handler of the calling block in search of code that will handle the exception, in the same way as happens with an Oracle-defined exception.

One final note on handling user-defined exceptions – the others exception handler will handle a user-defined exception as well as it will handle Oracle-defined exceptions. The same conditions about unhandled exceptions passing control to their callers applies in this situation as well. In general, user-defined exceptions are useful in that you employ Oracle’s overall process of exception handling for situations where Oracle errors might not occur to signal an application-defined problem. However, they do require some coding overhead, such as a condition check and an explicit raise statement.

Sometimes in PL/SQL programming you may want error handling that is more robust than simple one-line output. In situations where you have dozens, or even hundreds of PL/SQL programs working together as one application, it helps if the error handling methods being used allow you to detect which procedure or function experienced the error and why in order to speed resolution time on specific issues. You can create unique ID numbers for the errors themselves as well. In PL/SQL, there is a special built-in procedure available that helps you build a more robust application whose errors meld well into Oracle’s own methods for notifying you that an error condition exists.

The raise_application_error( ) procedure allows for that more seamless integration. This procedure allows you to define more descriptive error messages in conjunction with a given error situation. In addition, this procedure accepts a user-defined error number that Oracle will report when an error arises that calls raise_application_error( ) in such a way that the error message and error number will look like it’s coming directly from Oracle. To summarize, the following items are passed to this procedure:

• Error_Num An integer value between –20,000 and –20,999 that should be used to identify uniquely each procedure and/or error in the application, allowing up to 1,000 procedures, functions, or errors in the application, package, or library to be so uniquely identified.
• Error_Msg A text string of no more than 512 characters that describes the error that occurred.
• Keep_Recs (Optional, default FALSE)A TRUE or FALSE value stating whether Oracle should keep previous exceptions that may have been raised in conjunction with this one.

By incorporating raise_application_error( ) into your exception handler, you can design a higher level of application error handling, which offers more information about the error and the exact procedure that raised it. The following code block shows incorporation of the raise_application_error( ) procedure to allow you to see exactly what the problem was in the procedure.

CREATE PROCEDURE select_emp(
p_empid IN VARCHAR2
) AS
my_employee_rec employee%ROWTYPE;
bad_status exception;
BEGIN
SELECT *
INTO my_employee_rec
FROM employee
WHERE empid = p_raise_empid;
IF my_employee_rec.status = ‘R’ OR my_employee_rec.status = ‘L’ THEN
RAISE bad_status;
END IF;
EXCEPTIONS
WHEN NO_DATA_FOUND THEN
raise_application_error(-20100,‘Found no data in EMPLOYEE for ‘ || to_char(p_empid) || ‘.’);
WHEN BAD_STATUS THEN
raise_application_error(-20100,‘Employee ‘ || to_char(p_empid) || ‘ is on leave or retired.’);
END;

In this example, the error number identifies this procedure uniquely. Another option is for you to identify uniquely a specific user-defined exception like BAD_STATUS. In that case, the numbers passed to raise_application_error( ) in each portion of the exception handler should be different. Consider how Oracle now replies to an error situation where employee with EMPID 40394 is on maternity leave, with its new calls to raise_application_error( ).

SQL> execute select_emp(40394);
*
ERROR at line 1:
ORA-20100: Employee 40394 is on leave or retired.
ORA-06512: at SPANKY.SELECT_EMP, line 5
ORA-06512: at line 1
SQL>

In this chapter, you have covered a substantial amount of information on how to develop PL/SQL programs. Several topics are covered, including the required keywords and components in procedures and functions, the development of server-side procedures and functions with SQL*Plus and your favorite text editor, the development of server- and client-side procedures and functions with Procedure Builder. In addition, the execution of PL/SQL code was covered in various ways, along with the development and requirements of exception handlers in your PL/SQL code. All in all, this chapter comprises about 24% of material tested on OCP exam 2.

The first area you covered was the development of PL/SQL code. The permissions involved in PL/SQL programming were identified to be the create procedure system permission for server-side PL/SQL coding, none for client-side PL/SQL coding. Execution of a server-side PL/SQL block created by a user in the database is granted both to the owner of the code and to anyone who has been granted the execute object permission. Issuing both of these permissions is handled with the grant command.

The creation of procedures and functions is handled with either the create procedure or create function statement. Packages have their own creation statement, to be covered in chapter 9. There are special syntactic conditions to be met in creation of procedures. The first is an overall syntax flow, similar to create procedure proc_name (var direction datatype) is begin … end. The next is the syntax requirements for PL/SQL statements within the procedure. Once the user has created the procedure, she can submit the procedure to the Oracle database for compilation. Once compiled, if there are no errors, Oracle will store the procedure in the database, ready for execution. One main difference between anonymous PL/SQL blocks and named blocks is in the declaration of variables. Anonymous blocks use a declare keyword explicitly, while an implicit area for variable declaration exists between the is or as keyword and begin.

Parameter passing is an important area to understand in PL/SQL that is most directly attributed to use in procedures. Parameters can be passed in three directions. They can be passed into a procedure only, out of a procedure only, or into and out of a procedure. These directions are represented in the parameter variable definition area after the create procedure keywords with the use of the in, out, or in out keywords, respectively.

Functions are created with slightly different syntax from procedures. First, the keywords create function are used to define the function. Next, recall that a function always returns a value. As such, a return datatype must be defined for the function. The general syntax for function creation is create function name (var in datatype) return datatype is begin …return something … end. Note that you should define your variable parameters as in variables. Although it is technically possible for PL/SQL functions to return values in out or in out variables, good coding practice stipulates that the only out variable is the one returned by the function. At some point in the function, the return command must be used to define the value the function will return to the caller.

After establishing the basic premises of PL/SQL development, some of which was review, your attention turned to the use of various tools like SQL*Plus and Procedure Builder to develop PL/SQL code for the client and server. SQL*Plus can be used to enter stored or server-side procedures in the same way as it is used to enter server SQL statements. You can code in anonymous blocks or named blocks directly at the SQL prompt and submit them for compilation. Or, you can write the PL/SQL code in a flat file using your favorite text editor, load the file into SQL*Plus with the get command and compile it into your Oracle database by typing a slash (/). Alternately, after writing the PL/SQL code in a flat file using your favorite text editor, you can load and compile it into Oracle with the @ character affixed to the front of the filename that contains the PL/SQL code, sans the suffix of the file (usually .sql).

Once created and compiled successfully, the procedure is considered a part of the Oracle database, thus the term "stored procedure" applies. You can then execute the stored procedure from SQL*Plus using the execute procedure_name; statement. Alternately, this command can be abbreviated exec. In the case of executing a stored function from SQL*Plus, a small consideration must be made. Since a value will be returned from the function execution, a variable assignment statement must accompany (encompass, really) a stored function call. Thus, you wouldn’t say execute function_name from the SQL prompt, you would write an anonymous block (or named procedure) that declared a variable to store the return value from the stored function. The actual line within that PL/SQL block may look like variable_name := function_name(var1, var2);. The following code block illustrates.

DECLARE
my_var NUMBER(10);
BEGIN
my_var := sqrt(3);
END;

Compiling your server-side stored procedures is a bit ungainly in SQL*Plus when you encounter an error. This is because Oracle stores your compilation errors in either the USER_ or ALL_ERRORS dictionary views. Oracle also stores the line number where the error was found in these views, however, this line information is based on the location of the error in the SQL*Plus statement buffer (known as the file afiedt.buf), and that buffer only stores the most recent SQL or PL/SQL statement. Thus, as soon as you type select * from user_errors, you lose your buffer containing the location of your PL/SQL block. One solution is to have two sessions open at once, one for code compilation and the other for selecting errors. The other solution is to use show errors. However, SQL*Plus is not a particularly effective tool for server-side PL/SQL development.

Oracle combats the situation with Developer/2000. In particular, this development toolset includes a program called Procedure Builder. Procedure Builder runs in two modes, line and GUI. When at the Procedure Builder line mode, you can enter any valid PL/SQL statement, procedure or function name, and Procedure Builder will run it. Contrast this to SQL*Plus, which requires the use of the execute command. This is because Procedure Builder contains its own PL/SQL execution engine, separate from the one in the Oracle database.

There are two types of client- and server-side code in Procedure Builder, and those types are files and libraries. Files usually contain standalone procedures and functions, while libraries contain multiple procedures, functions, and sometimes even packages. Recall that the concept of packages was introduced. A package is a structure in PL/SQL that allows the developer to bundle several procedures or functions together, offering the code definitions and required variables as a specification and hiding the procedure or function source code in a package body. A new file type was introduced as well that stores PL/SQL libraries. These files carry the .pll suffix. However, these files are stored as binaries. If you want to load a PL/SQL block you develop in Procedure Builder into Oracle server via SQL*Plus, you must export the program unit to a flat file with extension .pls using the File>> Export command as described in the text. The same PL/SQL code developed in a flat file that can be loaded into SQL*Plus can also be loaded into Procedure Builder using the load file command. Alternately, if PL/SQL procedures are stored in client-side libraries, the library itself must be attached using the attach library command. However, you must remember to precede special commands like load and attach with a period (.) in Procedure Builder.

Although it can be run in line mode, more frequently you will find yourself working with the Procedure Builder GUI. The application itself consists of five elements called modules. They are the object navigator, the PL/SQL interpreter, the program unit editor, the stored program unit editor, and the database trigger editor. The latter two modules are for server-side PL/SQL development of PL/SQL blocks and table triggers, respectively. The program unit editor is used to develop client-side PL/SQL code. Either standalone or in a library. The PL/SQL interpreter is used for interactive execution of PL/SQL code on both the client and server. Finally, the object navigator provides a visual representation of an overall development environment for both client- and server-side PL/SQL code.

One major advantage Procedure Builder offers you is to cut the effort to develop code. The easiest way to develop a client-side PL/SQL block is to use the program unit editor module. The module’s graphical interface makes it easy to name the code and define its PL/SQL block type. Once named and its type defined, the program unit editor will open a window containing the basic keywords and constructs of the PL/SQL block you want. All that remains is for you to fill in the loops, variables, if-then statements, and other PL/SQL constructs required for your program. What’s more, the program unit editor window simplifies compilation by offering you a compile button. Once your code is written, you can compile it. If there are errors, the program unit editor will notify you immediately what the problem was, and point the way clearly to the offending code. Note one important point about client-side PL/SQL programming – you needn’t be connected to the database in order to write it, because Procedure Builder has its own built-in PL/SQL execution engine.

For development of server-side PL/SQL code, you must be connected to the database. Creation of stored procedures and functions is done with the stored program unit editor. The process for creating new stored PL/SQL is similar to that of creating client-side PL/SQL units, but with the exception that the stored program unit module has no compile button. Instead, you must click on the save button and Oracle will reply if it was able to compile and store your procedure on the database or not. Other specifics on development of client- and server-side PL/SQL are covered in the text, along with figures that show the status of Procedure Builder at the various stages of development.

Once compiled on either the client or server, your programs can be run in the following way. You can type the name of the procedure into the PL/SQL interpreter module or Procedure Builder command line mode, so long as your client library or file is attached or loaded. If you are trying to run server-side PL/SQL, you must be connected and able to see the compiled and stored PL/SQL in the database. Recall also that an anonymous block can be entered at the PL/SQL interpreter prompt, along with any valid PL/SQL statement, so you can run things that way, too.

The final area covered in this chapter is proper use of the exception handler, and handling exceptions in general in PL/SQL. Several different types of exceptions were covered, including Oracle-defined and user-defined exceptions. Important to remember is that Oracle always raises its own exceptions automatically. Thus, if you for example are to divide by zero in a function, Oracle will raise an exception automatically. If you haven’t coded an appropriate exception handler for the situation, control returns to the caller of the procedure or function. If you are not careful about trapping all possible exceptions and handling them in your code, you may encounter situations where users receive errors they shouldn’t have to deal with, causing your application to seem less robust. Chapter 5 lists the Oracle-defined exceptions; to handle them in your exception handler requires only that you include a when Ora_defnd_exception then clause, where Ora_defnd_exception is either the name of the Oracle-defined exception or a special catch-all exception called others.

The user-defined exceptions are handled slightly differently. Remember, a user-defined exception must be declared, raised, and handled explicitly by the PL/SQL block. You get none of the freebies associated with Oracle-defined exceptions here. However, the importance of handling a raised user-defined exception is no less important than handling an Oracle-defined exception. Recall that in all cases where a raised exception is not handled by the current PL/SQL block, control passes to the exception handler of the calling block.

Last, the use of a special procedure called raise_application_error( ) was covered in this chapter. This procedure allows you to create specially-numbered custom error messages that are reported by Oracle whenever a server-side stored procedure or function hits a handled exception. This procedure accepts three variables as parameters, including error_number between –20,000 and –20,999, error_message of 512 characters or less, and keep_error, a TRUE or FALSE value that tells Oracle to keep or not keep other errors that have come before this one when reporting problems. By incorporating a call to raise_application_error( ) in your exception handler, you allow more unique identification of either the stored PL/SQL block or of the error that occurred.

1. Object navigator – Navigate client and server PL/SQL program modules.
2. PL/SQL interpreter – Interactive prompt for running PL/SQL code.
3. Program unit editor – Create, edit, and compile client PL/SQL.
4. Stored program unit editor – Create, edit, and compile server PL/SQL.
5. Database trigger editor – Create, edit, and compile database triggers.

• You can develop standalone PL/SQL code for the client side in flat files suffixed by .sql and load them into Procedure Builder using .load file filename.sql command from the PL/SQL prompt in line mode or the PL/SQL interpreter in GUI mode.
• Procedure Builder introduces the concept of code libraries, which are files with the suffix .pll. A library contains client-side PL/SQL procedures and functions available for use.

• You can use PL/SQL code that is part of a client-side library after attaching it. To attach a client-side PL/SQL library, use the .attach library libname in the PL/SQL interpreter module or in Procedure Builder line mode. You can also use the file>>attach menu item to attach a library.
• You do not need to be connected to the database to create client PL/SQL in Procedure Builder. You must be connected to the database to create server stored procedures and functions or database triggers in Procedure Builder.
• PL/SQL code is executed by calling it in the PL/SQL interpreter. No execute command is required. Also, you can run any valid PL/SQL statement from the PL/SQL interpreter.
• Errors in PL/SQL code are managed by the exception handler.
• There are two types of exceptions to recall: Oracle-defined exceptions and user-defined exceptions. A third type of exception, an internal exception, is explained in chapter 5.
• Oracle-defined exceptions do not need to be declared in your code. Oracle automatically raises its own exceptions, though, so you must remember to code an exception handler to trap Oracle’s own exceptions.
• User-defined exceptions must be declared, raised, and handled explicitly in your PL/SQL code.
• A special exception handler exists in PL/SQL called others, which allows you to create when clauses in your exception handler that catch all exceptions that are not specifically handled by other exception handlers.
• A special procedure called raise_application_error( ) allows you to integrate stronger error messaging into Oracle’s native error reporting methods.
• The raise_application_error( ) procedure accepts three parameters, error_number, error_message, and optionally keep_records.
• Acceptable values for error numbers are between –20,000 and –20,999.
• Generally, it works best to use error numbers to identify uniquely all stored procedures or functions in the Oracle database. Alternately, use error numbers to identify uniquely the different user-defined errors in your stored procedures.