Oracle Magazine - March/April 2012

makes no distinction. If a rollback is performed because of the error, the INSERT into the log table will also be rolled back.

•;Brittle code. If I ever need to change the structure of the error_log table, I will have to change all the INSERT statements to accommodate this change.

A much better approach is to “hide” the table behind a procedure that does the INSER T for you, as shown in Listing 2.

All I’ve done is move the INSERT statement inside a procedure, but that simple action has important consequences. I can now very easily get around the problem of rolling back my error log INSER T along with my business transaction. All I have to do is make this procedure an autonomous transaction by adding the pragma statement and the COMMI T, as shown in Listing 3.

By declaring the procedure to be an autonomous transaction, I can commit or roll back any of the changes I make to tables inside this procedure without affecting other changes made in my session. So I can now save the new row in my error log, and a later rollback of the business transaction will not wipe out this information.

With this logging procedure defined in my schema, I can now very easily and quickly write an exception handler as follows:

EXCEPTION
WHEN OTHERS
THEN
record_error();
RAISE;

It takes me much less time to write my exception handler, and its functionality is more robust. A win-win situation! Exceptions;raised;while;declaring. If an exception is raised in the declaration section of a block, the exception will propagate to the outer block. In other words, the exception section of a block can catch only exceptions raised in the executable section of the block.

The following block includes a WHEN OTHERS handler, which should trap any exception raised in the block and simply display the error code:

DECLARE l_number NUMBER ( 1) := 100;

BEGIN
statement1;
...
statementN;

When I execute the block, Oracle Database will try to assign the value 100 to l_number. Because it is declared as NUMBER ( 1), however, 100 will not “fit” into the variable. As a result, Oracle Database will raise the ORA-06502 error, which is predefined in PL/SQL as VALUE_ERROR.

Because the exception is raised in the process of declaring the variable, the exception handler will not catch this error. Instead I’ll see an unhandled exception:

ORA-06502: PL/SQL: numeric or value error: number precision too large ORA-06512: at line 2

Consequently, you should avoid assigning values to variables in the declaration section

Take the Challenge!

Each PL/SQL 101 article offers a quiz to test your knowledge of the information provided in the article. The quiz questions appear below and also at PL/SQL Challenge ( plsqlchallenge.com), a Website that offers online quizzes for the PL/SQL language. You can read and take the quiz here in Oracle Magazine and then check your answers in the next issue. If, however, you take the quiz at PL/SQL Challenge, you will be entered into a raffle to win an e-book from O’Reilly Media ( oreilly.com).

Question;1

Which of these blocks will result in an unhandled ORA-00001 exception?

a.

BEGIN
RAISE DUP_VAL_ON_INDEX;
END;

/

b.

BEGIN RAISE - 1;

END;

/

c.

CREATE TABLE plch_tab (n NUMBER PRIMARY KEY)
/
BEGIN
INSERT INTO plch_tab
VALUES ( 1);

INSERT INTO plch_tab VALUES ( 1);

END;

/

d.

BEGIN RAISE DUP_VAL_ON_INDEX;

EXCEPTION
WHEN OTHERS
THEN
END;

/

Question;2

Assume that the plch_tab table has been created with a single numeric column. What change can I make in the following procedure so that it will compile without error?