RDBMS

What is an Exception?

In PL/SQL, errors and warnings are called exceptions. When a predefined error occurs (division by zero → ZERO_DIVIDE, SELECT finds no row → NO_DATA_FOUND) PL/SQL raises the exception automatically. Users can also create their own exceptions. An application is not complete without exception handling.

If an exception is not handled, the block is aborted with an error message. When it is handled, control moves to the exception-handling part; after the handler runs, control goes to the next statement of the enclosing block (or back to the host if there is none).

declare
   v_fee courses.fee%type;
begin
   select fee into v_fee from courses where ccode = 'c';
   update courses set fee = v_fee where ccode = 'c++';
exception
   when no_data_found then          -- course C does not exist
      update courses set fee = (select avg(fee) from courses) where ccode = 'c++';
end;

SQLCODE and SQLERRM

Most Oracle errors have no predefined exception name. In WHEN OTHERS, SQLCODE returns the error number and SQLERRM the error message of the most recent error. They cannot be used directly in an SQL statement – assign them to variables first.

declare
   newccode varchar2(5) := null;
begin
   update courses set ccode = newccode where ccode = 'c';
exception
   when dup_val_on_index then
      dbms_output.put_line('Duplicate course code');
   when others then
      dbms_output.put_line(sqlerrm);
end;

ORA-01407: cannot update ("BOOK"."COURSES"."CCODE") to NULL

Predefined Exceptions

The PDF lists further predefined exceptions: ACCESS_INTO_NULL, COLLECTION_IS_NULL, NOT_LOGGED_ON, PROGRAM_ERROR, ROWTYPE_MISMATCH, SELF_IS_NULL, STORAGE_ERROR, SUBSCRIPT_OUTSIDE_LIMIT, SYS_INVALID_ROWID and TIMEOUT_ON_RESOURCE.

When is NO_DATA_FOUND not raised?

• When the SELECT uses a group function: SUM, AVG etc. return NULL (COUNT returns 0) instead of raising the exception – check the result for NULL.
• When UPDATE or DELETE affects no rows (use cursor attributes – Chapter 18).

User-defined Exceptions

1. Declare it in the declarative part: out_of_stock exception; (available in that block).
2. Raise it explicitly with RAISE out_of_stock; when your condition occurs.
3. Handle it like a predefined exception: WHEN out_of_stock THEN …

declare
   out_of_stock exception;
begin
   begin                                   -- sub-block
      if qty < 10 then
         raise out_of_stock;
      end if;
   exception
      when out_of_stock then
         -- partial handling here, then
         raise;                            -- re-raise the current exception
   end;
exception
   when out_of_stock then                  -- handled again in the outer block
      null;
end;

RAISE without a name re-raises the current exception and is valid only inside an exception handler.

Associating an exception with an Oracle error

declare
   null_value_error exception;
   pragma exception_init(null_value_error, -1407);
   newccode varchar2(5) := null;
begin
   update courses set ccode = newccode where ccode = 'c';
exception
   when null_value_error then
      dbms_output.put_line('trying to set null value to a not null column');
end;

Exception Propagation

• An exception handled in the block that raised it is not propagated; control resumes in the enclosing block after the inner block.
• If the current block has no handler, the exception is propagated to the enclosing blocks, one by one from inside out, until a handler is found.
• If no block handles it, it reaches the host (SQL*Plus, Oracle Forms, Oracle Server …), which decides the action.
• An exception raised in the declaration of a block is propagated immediately to the enclosing block – that block’s own handlers do not catch it (e.g. assigning 'abcdef' to VARCHAR2(5) gives ORA-06502 in the outer handler).

Success or failure of a block

To find which of several SELECTs raised NO_DATA_FOUND, set a counter variable (n := 1, n := 2 …) after each SELECT and test it in the handler.

Chapter 17 Notes (PDF)

Question / Answer (15 questions)

Moderate-level questions based on the Chapter 17 notes (PDF). Click a question to see the answer.

MCQ Test : Check Your Understanding (5 questions)

Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.

Bibliography/References

The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.

1. Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
2. Oracle Corporation. (n.d.). Oracle Database PL/SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/
3. Oracle Corporation. (n.d.). Oracle Database Error Messages, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/errmg/
4. Feuerstein, S., & Pribyl, B. (2014). Oracle PL/SQL Programming (6th ed.). O’Reilly Media.