exception
...
when others THEN
error_code := SQLCODE;
error_msg := SQLERRM;
INSERT into user_exceptions_table (error_message)
values (to_char(error_code) || `: ` || error_msg)
COMMIT;
END;

You can now perform a SELECT on the user_exceptions_table to view the exception error code and message.

You cannot use SQLCODE and SQLERRM directly in a SQL statement. Instead, you must assign their values to local variables and then use these variables in the SQL statement.

If PL/SQL cannot find an exception handler for an error, it turns the exception over to the host environment for handling.

Continue Processing After an Exception

Exception handlers let you handle errors that normally would be fatal to your application. But exception handlers as described so far in this chapter create a logic processing problem. Look at the following PL/SQL block:

DECLARE
invalid_fam_code      exception;           -- user-defined exception
part_fam_code varchar2(2);
BEGIN
...
IF part_fam_code not in (`AU','UC','BG') THEN  -- error trap
RAISE invalid_fam_code;                  -- raise user-defined exception
END IF;
INSERT....
UPDATE....
....
exception                    -- handle user-defined exception
when invalid_fam_code THEN
ROLLBACK;
END

When the invalid_fam_code exception occurs, process control is transferred to the handler. The INSERT and UPDATE commands are never executed in this scenario. This could pose a problem for your application. To get around the problem of code being bypassed due to exceptions, embed the exception handler in its own sub-block, as shown in the following example:

DECLARE
invalid_fam_code      exception;               -- user-defined exception
part_fam_code varchar2(2);
BEGIN
...
BEGIN                                         -- sub-block begin
IF part_fam_code not in (`AU','UC','BG') THEN  -- error trap
exception                               -- handle user-defined exception

when invalid_fam_code THEN
ROLLBACK;
END IF;
END;                                     -- sub-block end
INSERT...
UPDATE...
...
END;

In this example, the exception invalid_fam_code is handled locally in its own sub-block. This arrangement allows the INSERT, UPDATE, and any other following statements to be executed.

Retry After an Exception

Sometimes you'll want to retry a transaction after some exception. For example, say you are creating new identification numbers for new parts coming out of the factory. You make an attempt to create a new part number and receive a message stating that that particular part number already exists. What would you do? Normally, you create a different part number and retry the task. The following example uses this same scenario. You would not want to cease transaction processing when you get a duplicate part number message. Fortunately, you know enough about the causes of the problem to add corrective logic to your program.

DECLARE
max_count       integer;
part_number     integer := 1;
BEGIN
SELECT count(*) into max_count from new_parts
LOOP  FOR I in 1..max_count loop
BEGIN                            -- create a sub-block
savepoint top_loop;              -- establish rollback point
part_number :=part_number+1;     -- create new part number
INSERT into inventory value (part_number);
COMMIT;
EXIT;
exception
when dup_val_on_index THEN
part_number :=part_number+1;   -- create a newer part number
ROLLBACK to top_loop;          -- force us to top of loop
END;                           -- end sub-block
END LOOP;                      -- end loop
END;                           -- end block

Reraising an Exception

At times, you might want to handle an exception locally and pass the exception to an enclosed block. The following example raises the exception out_of_stock in the sub-block and passes the exception handling to the enclosed block:

DECLARE
out_of_stock    exception;    -- declare exception
BEGIN    -- beginning of sub-block to check qty of order
IF qty_ordered > qty_on_hand THEN
RAISE out_of_stock;           -- raise the exception
END IF;
exception

-- for exception indicated that order_qty is invalid
when out_of_stock THEN
...
RAISE;                     -- reraise the current exception
...
END;
--  end of sub-block
exception
-- handle the exception differently than earlier.
when out_of_stock THEN
...
END;

Exception Scope Rules

You should be aware of the following guidelines with regard to the scope of an exception declaration:

• An exception cannot be declared twice in the same block, but the same exception can be declared in two different blocks.
• Exceptions are local to the block where they were declared and global to all of its sub-blocks. Enclosing blocks cannot reference exceptions that were declared in any of their sub-blocks.
• Global declarations can be redeclared at the local sub-block level. If this occurs, the local declaration takes precedence over the global declaration.

Propagate Exceptions

When an error is encountered, PL/SQL looks in the current block for the appropriate exception handler. If no handler is present, then PL/SQL propagates the error to the enclosing block. PL/SQL searches the enclosing block for the correct error handler. If no handler is found there, the error is continually propagated to the enclosing blocks until a handler is found. This process can continue until the host environment receives and handles the error. For example, if SQL*Plus received an unhandled error from a PL/SQL block, the way SQL*Plus handles this error is to display the error code and message on the user's screen.

Summary

You covered a lot of material in the chapter, including Oracle procedures and packages and error-handling capabilities. Procedures and packages offer the programmer powerful constructs for your PL/SQL blocks. The Oracle procedure is a concise and logically grouped set of statements to perform a specific task. A package is an encapsulated collection of related schema objects such as functions and procedures that achieve a common purpose. Runtime errors should be anticipated and planned for in your application, using exception-handling code to prevent the unwanted termination of your application.

Q&A

Q Do I have to use a package when I have only one function in my application?

A No. You do not have to use a package when you have only one function. I would suggest that you think seriously about the future growth of your application. If you think the application will grow and include additional functions and procedures, then I recommend that you start off using a package from the beginning.

Q Why do I need to write extra code to process errors detected by Oracle?

A If your code does not explicitly trap for any and all exceptions, your PL/SQL processing will halt when the error is detected.

Q Can I build my procedure prior to building my database?

A Not usually. Because of dependency issues, you might have to have the tables and views (or other schema objects) in place prior to compiling your procedure.

Q What if I don't know what error to anticipate from Oracle?

A Fortunately, Oracle provides you with the when others clause to handle this exception. This exception will trap all exceptions.

Workshop

The following workshop will test your understanding of Oracle procedures and packages and PL/SQL's capability to trap and handle processing exceptions. The answers to the quiz and exercises are provided in Appendix A, "Answers."

Quiz

1. What statement is used to recompile a procedure?
2. How do you invoke a procedure?
3. Name at least four predefined Oracle exception errors.
4. How do you call a module of a package?

Exercises

1. Write a package specification for the functions written in previous chapters. Additionally, include in the specification one or two of the procedures used in this chapter.
2. Write an exception-handling piece of code to trap the error of receiving more rows than you expected as well as an unknown error.