Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)

There have always been exceptions in programming. The first exception in any computer program was probably a divide-by-zero and at that time there was no mechanism to trap such an exception. A zero divide exception can be captured in PL/SQL with the following. This block prints the error when it occurs.

DECLARE n NUMBER; BEGIN n:= 2/0; EXCEPTION WHEN ZERO_DIVIDE THEN dbms_output.put_line('Caught a zero divide'); END;

The ZERO_DIVIDE is one of the PL/SQL built-in exceptions. The language has several predefined exceptions. We looked at DUP_VAL_ON_INDEX in Chapter 3. This exception is raised from a duplicate insert against a primary key or unique constraint. You can declare your own exceptions. These are called user -defined exceptions.

Excessive use of user-defined exceptions can lead to choppy code. You always want to consider ways to simplify the PL/SQL code that will use your package. A balanced use of user-defined exceptions can simplify the logic, level of nesting, and readability of application code that interfaces with your package specification. In general, user-defined exceptions should be declared and raised within an application when an "exceptional condition" occurs.

11.18.1 User-Defined Exceptions

Exceptions have the following characteristics.

• Exceptions are declared in your program. You can declare an exception in any declarative region: package specification, package body, or subprogram declarative part. The following is an exception you might declare in the specification of a package that contains subprograms to registered students for a class.

  class_full EXCEPTION;

• You raise an exception with the RAISE statement. Consider a student registration package (introduced on p. 267). A procedure for adding students would first check if the class is full. The logic of the application to add a student could contain PL/SQL similar to the following:

  IF num_std >= 30 THEN RAISE class_full; END IF;

• The user of your package must write code to handle the exception, similar to the ZERO_DIVIDE exception handler.

Style is a concern. There are always options with resolving the unexpected condition. You can return a status to indicate the reason for the unexpected condition. The choice to use an exception has an impact on the code using your package. If your package frequently raises exceptions, the code using your package will be choppy. You should raise an exception only when the exception condition is extremely rare. You can combine exceptions with status returns. For example, you can return a status if the class is full, but raise an exception if the caller attempts to register a student for a nonexistent class.

From an applications perspective, the following package illustrates the use of an exception. This package adds numbers . The package raises an exception if the sum is zero. In this example, a zero is considered an invalid result and the package throws the exception.

CREATE OR REPLACE PACKAGE add_pkg IS zero_amount EXCEPTION; FUNCTION add(A NUMBER, B NUMBER) RETURN NUMBER; END add_pkg; CREATE OR REPLACE PACKAGE BODY add_pkg IS FUNCTION add(A NUMBER, B NUMBER) RETURN NUMBER IS result NUMBER; BEGIN result := A+B; IF (result = 0) THEN raise zero_amount; END IF; RETURN result; END add; END add_pkg;

The user of the package has a choice in how to write the code. The code can include an exception handler or not include an exception handler. When an exception is raised, execution of the immediate block ends. "Block" means the immediate BEGIN-END block or subprogram body. If the immediate block has an exception handle, then control passes to that point. If there is no exception handler, the exception propagates back to the next -most-outer block or calling subprogram.

The following table shows two procedures; one handles the exception, the other does not.

11.18.2 Blocks with Exception Handlers

Modify the preceding procedure PROC_1 so that it loops . PROC_1 below calls ADD_PKG once per loop iteration. Theoretically, any iteration can raise an exception. The behavior of this code is that a single exception terminates processing. The first pass will add a minus one to a one. This will raise the exception. The resolution of the exception is to print using DBMS_OUTPUT.

When ADD_PKG raises an exception, the procedure itself, ADD, has no exception handler. Therefore, the exception propagates to the calling procedure, in this case PROC_1. This procedure has an exception handler; hence, control goes to the handler, which prints a zero.

CREATE OR REPLACE PROCEDURE proc_1 IS N NUMBER; BEGIN FOR VAL IN -1..5 LOOP N := add_pkg.add(1,VAL); dbms_output.put_line(N); END LOOP; EXCEPTION WHEN add_pkg.zero_amount THEN dbms_output.put_line(0); END;

The code in PROC_1 can alternatively capture each possible failed addition by embedding the ADD_PKG call in an exception block. This is shown next. This design allows additions to continue while capturing those that fail. The following exception handler exists within a BEGIN-END block. When the exception occurs, control jumps to the end of the current block. The block is within the loop, hence iteration continues.

CREATE OR REPLACE PROCEDURE proc_1 IS N NUMBER; BEGIN FOR VAL IN -1..5 LOOP BEGIN N := add_pkg.add(1,VAL); dbms_output.put_line(N); EXCEPTION WHEN add_pkg.zero_amount THEN NULL; END; END LOOP; END;

11.18.3 The EXCEPTION Clause

The exception clause can be at the end of a procedure, function, or declare block. Previous examples of the procedure PROC_1 illustrate both cases.

The following exception syntax illustrates code that is willing to handle one of two exceptions. Any PL/SQL code can execute within the exception handler.

EXCEPTION WHEN exception_name THEN do_something; WHEN another_exception_name THEN do_something; END;

An exception handler can contain a WHEN OTHERS clause. This program logic handles any exception that occurs.

EXCEPTION WHEN OTHERS THEN do_something; END;

A WHEN OTHERS can be combined with other exceptions. Use WHEN OTHERS to catch exceptions not previously listed in the exception handler. The following example prints a specific message when the ZERO_AMOUNT exception is raised. For any "other" exception, a generic message is displayed.

EXCEPTION WHEN add_pkg.zero_amount THEN dbms_output.put_line('Got zero_amount exception'); WHEN OTHERS THEN dbms_output.put_line('Got some other exception'); END;

A program can have multiple exception handlers. The next procedure illustrates this. The procedure, PROC_1, has two exception handlers. One exception handler exists within the BEGIN-END block. This captures the ZERO_AMOUNT exception. The other exception handler is at the procedure level and will capture a VALUE_ERROR.

A VALUE_ERROR is an Oracle predefined exception raised on conversion errors such as:

N NUMBER := TO_NUMBER('ABC');

This version of PROC_1 illustrates exception handling but is also an example of choppy code.

CREATE OR REPLACE PROCEDURE proc_1 IS N NUMBER; BEGIN FOR VAL IN -1..5 LOOP BEGIN N := add_pkg.add(1,VAL); dbms_output.put_line(N); EXCEPTION WHEN add_pkg.zero_amount THEN NULL; END; END LOOP; EXCEPTION WHEN VALUE_ERROR THEN dbms_output.put_line('Got value error'); END;

Why does PROC_1 have an exception handler for VALUE_ERROR? The only reason is to illustrate the various placements of exception handlers. There is nothing in this code that could possibly raise such an exception. That is, there are no PL/SQL statements that can cause the type of error we would see if we executed this statement:

N NUMBER := TO_NUMBER('ABC');

Exception handlers that can never be invoked should be removed from the code. But here is a version of PROC_1 that should have a VALUE_ERROR exception handler.

What if we changed PROC_1 as follows ? In the following, PROC_1 receives a VARCHAR2 string that is converted to a NUMBER within the loop. The TO_NUMBER conversion could potentially raise a VALUE_ERROR exception.

CREATE OR REPLACE PROCEDURE proc_1(END_POINT IN VARCHAR2) IS N NUMBER; BEGIN FOR VAL IN -1..TO_NUMBER(END_POINT) LOOP BEGIN N := add_pkg.add(1,VAL); dbms_output.put_line(N); EXCEPTION WHEN add_pkg.zero_amount THEN NULL; END; END LOOP; EXCEPTION WHEN VALUE_ERROR THEN dbms_output.put_line('Got value error'); END;

Having shown the various places where exception handlers can be declared, the justification for each of these exception handlers is questionable. A better PROC_1 procedure would define the parameter END_POINT as a NATURAL subtype. This constrains the parameter to zero or greater. If this were done, the VALUE_ERROR exception would not be necessary and could then be removed.

11.18.4 SQLCODE and SQLERRM

SQLCODE and SQLERRM are built-in functions that only have meaning within an exception handler. They evaluate to the current exception error number and error message. These functions are frequently used with DBMS_OUTPUT as a debugging mechanism. They are usually in a WHEN OTHERS part of an exception handler.

A procedure with many DML statements can be difficult to debug. There can be many reasons for failure. A procedure may fail because of a duplicate insert. It may fail with a conversion error when it inserts text into a numeric field.

The following example captures an invalid insert. A WHEN OTHERS exception handler is included that prints SQLCODE and SQLERRM.

This block simulates inserting character text into a column with a NUMBER type.

DECLARE N varchar2(30) := 'not a number'; BEGIN UPDATE professors SET salary=TO_NUMBER(n); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('SQLCODE:'SQLCODE); dbms_output.put_line('SQLERRM:'SQLERRM); END;

The output from this block is:

SQLCODE:-1722 SQLERRM:ORA-01722: invalid number

An INVALID_NUMBER is an Oracle predefined exception raised if conversion fails during a SQL statement. For example:

UPDATE professors SET salary=TO_NUMBER('abc');

SQLCODE and SQLERRM are usually used in WHEN OTHERS handlers to catch unexpected exceptions. If you know in advance that you will have to catch an INVALID_NUMBER exception, you will code the following:

EXCEPTION WHEN INVALID_NUMBER THEN dbms_output.put_line('Number conversion in SQL'); END;

Using %TYPE is a practice that helps avoid run-time-type conversions. To implement the aforementioned PL/SQL block as a procedure that updates the PROFESSORS table, pass the salary using a %TYPE. This eliminates the possibility of an INVALID_NUMBER exception and the need for an exception handler.

CREATE OR REPLACE PROCEDURE update_salaries(new_sal professors.salary%TYPE) IS BEGIN UPDATE professors SET salary=new_sal; END update_salaries;

11.18.5 The RAISE Statement

An exception condition occurs with the following conditions:

• Application code declares an exception and the PL/SQL code raises that exception. This is the case with the ADD_PKG package code on p. 366. It raises an exception when the result of the operation is zero.

• Oracle raises an exception. This occurs if you insert a duplicate into a primary key column. Another example is a divide by zero.

This section covers the topic of reraising an exception inside an exception handler.

You can capture an exception, handle it, and then raise it. Why? You may want to write exception-handling code for the primary purpose of capturing the error recording it but want to throw it back to the procedure that called you. The procedure that called you may need to know such an exception occurred. Consider the following code, used earlier. This procedure includes the exception handler plus RAISE statement in the BEGIN-END block.

CREATE OR REPLACE PROCEDURE proc_1 IS N NUMBER; BEGIN FOR VAL IN -1..5 LOOP BEGIN N := add_pkg.add(1,VAL); dbms_output.put_line(N); EXCEPTION WHEN add_pkg.zero_amount THEN dbms_output.put_line('zero_amount'); RAISE; END; END LOOP; EXCEPTION WHEN VALUE_ERROR THEN dbms_output.put_line('Got value error'); END;

What is the execution path when ADD_PKG raises the ZERO_AMOUNT exception?

• First, the exception is originally raised in the ADD procedure of ADD_PKG. This procedure has no exception handler, so control jumps to an exception handler in the block of PROC_1.

• The code in the exception handler is executed. This includes DBMS_OUTPUT. Then there is a RAISE statement. This exits the current block as an exception. The exception propagates to the nearest outer scope block or procedure.

• There is an exception handler for the procedure, but it is specific for a VALUE_ERROR. The RAISE statement within the local BEGIN-BLOCK goes directly to the procedure that called PROC_1.

What if PROC_1 included a WHEN OTHERS exception at the procedure level? In this case, the RAISE statement would be caught in the WHEN OTHER exception part of the procedure exception handler.

Within the body of a PL/SQL block or procedure a RAISE statement must be followed by an exception name . This syntax is:

RAISE exception_name;

The RAISE statement inside an exception handler may be just the RAISE keyword, that is:

RAISE;

When RAISE does not identify an exception name, the same exception is raised. However, an exception handler may execute a RAISE statement naming an exception. It can name the same exception of another exception.

11.18.6 Unhandled Exceptions and Exception Propagation

When an application raises an exception that is not caught, it propagates back to the application as an Oracle error. The Oracle error is:

ORA-06510: PL/SQL: unhandled user-defined exception

This is a serious problem because it does not indicate the exception name. Consider the scenario in which a Java program calls procedure P1. P1 calls P2, which calls the procedure ADD in the package ADD_PKG. Assume this results in a run-time ZERO_AMOUNT exception. The behavior of the exception propagation is shown in Figure 11-11.

What happens if P2 has any of the following exception handlers:

11.18.7 RAISE_APPLICATION_ERROR

RAISE_APPLICATION_ERROR is a procedure in the Oracle built-in package DBMS_STANDARD. Calling this procedure in your code does not raise an exception. This procedure is included under the heading of exceptions because, similar to exceptions, it provides a means to signal an error condition. Exceptions propagate up the PL/SQL call chain. Errors from RAISE_APPLICATION_ERROR do not propagate. The following paragraphs cover the differences between RAISE_APPLICATION_ERROR and exceptions.

A Java application includes error-handling code. The method getErrorCode() is a means by which the Java application realizes that an INSERT or other DML operation failed. There are many reasons why an Oracle error should occur. Some are common, such as a constraint violation, and others occur less frequently, such as a privilege violation. Some are very infrequent, such as writing to a tablespace that is in READ ONLY mode.

Consider the scenario in which a Java program calls a PL/SQL procedure P1. Procedure P1 calls P2. The run-time execution of P2 encounters an error. Procedure P2 tries to write to a READ ONLY tablespace. Control goes to the Java program where it reads the error number as -00372. The error message is: "file 8 cannot be modified at this time." Figure 11-12 shows that behavior of execution.

PL/SQL exceptions propagate within PL/SQL, but do not propagate outside the language. A PL/SQL exception transforms into the Oracle error:

ORA-06510: PL/SQL: unhandled user-defined exception

Suppose you want to communicate an error condition from the PL/SQL world to the Java world. You can't raise an exception, but you can "raise an application error" that assumes the behavior of an Oracle error. The interface for this is:

RAISE_APPLICATION_ERROR(ARG1, ARG2);

As said, the RAISE_APPLICATION_ERROR assumes the behavior of an Oracle error. This means that a Java application can capture your error code number and text message as an application error.

It also means that the execution path immediately leaves the PL/SQL environment and control transfers directly to the calling application, that being the Java, C++, or other language program. In Figure 11-13, procedure P2 raises an error that can be caught by the application. The error number is -20000 and the error text is "Student cannot register. Class is full."

The code in P2 would look like the following:

SELECT COUNT (*) INTO number_of_students FROM students_courses WHERE course_name = v_course_name; IF number_of_students >= 30 THEN RAISE_APPLICATION_ERROR (-20000, 'Student cannot register. Class full.'); END IF;

11.18.8 EXCEPTION_INIT

Suppose you want to capture an Oracle error, locally. A duplicate insert on a primary key constraint violation raises an exception. That exception can be captured. The error from a check constraint violation does not raise an exception. A check constraint error is an Oracle error. The behavior of an Oracle error is different, as discussed in Section 11.18.7, "RAISE_APPLICATION_ERROR."

In PL/SQL, you can stipulate that a particular error be raised as an exception. To do this you first declare an exception. Then map the exception to the error number. You must know the error number. The PL/SQL statement used is a PRAGMA.

The STUDENTS table has a CHECK constraint on the STATUS column. The column value must be "Degree" or "Certificate." The following procedure can capture a check constraint violation. It declares an exception CHECK_CONSTRAINT_VIOLATION. That is mapped to the error number for the check constraint error. Additionally, this procedure includes exception-handling code for a duplicate insert.

CREATE OR REPLACE PROCEDURE insert_student(v_stucent_id VARCHAR2, v_student_name VARCHAR2, v_college_major VARCHAR2, v_status VARCHAR2, v_state VARCHAR2, v_license_no VARCHAR2) IS check_constraint_violation exception; pragma exception_init(check_constraint_violation, -2290); BEGIN INSERT INTO students VALUES (v_stucent_id, v_student_name, v_college_major, v_status, v_state, v_license_no); dbms_output.put_line('insert complete'); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN dbms_output.put_line('PK or unique const violation'); WHEN check_constraint_violation THEN dbms_output.put_line('check constraint violation'); END;

Exceptions do not propagate outside of PL/SQL. When unchecked, they become Oracle error:

ORA-06510: PL/SQL: unhandled user-defined exception

This procedure maps an Oracle check constraint error to an exception. What happens if this procedure raises the exception, CHECK_CONSTRAINT_VIOLATION? That is, what happens if this exception is raised inside of PL/SQL and is uncaught within PL/SQL? The application that called this procedure gets the error code of a check constraint violation, minus 2290. It does not get 06510. It is true that uncaught user-defined exceptions result in Oracle 06510 errors. But Oracle errors mapped to exceptions through PRAGMA EXCEPTION_INIT, when they propagate and are uncaught, result in the original Oracle error.