MattsBits
MattsBits

MattsBits

Handling Errors And Exceptions In Oracle PLSQL  

by Matt Hawkins, 09/09/2010
Categories : Oracle

In PL/SQL, a warning or error condition is called an exception. Certain exceptions can be predefined or they can be user defined. Predefined exceptions include division by zero or out of memory conditions.

Predefined exceptions have predefined names such as ZERO_DIVIDE and NO_DATA_FOUND.

When an error occurs, an exception is raised.

The exception is either handled by the system or user defined exception handlers.

The example below shows the structure of a simple exeception handler :

DECLARE

BEGIN
  -- Start code
  -- if an error occurs it will be handled
  -- by the exception handler below
  
  < CODE GOES HERE >

EXCEPTION WHEN OTHERS THEN
  -- handle error
  null;
END;


You can define your own exceptions which you can raise when you detect certain conditions in your code. In the example below two exceptions have been defined. These can be raised whenever you need to within your code. The exceptions could be the result of an IF statement.

DECLARE

  myException1 EXCEPTION;
  myException2 EXCEPTION;
  
BEGIN

  < CODE GOES HERE >
  RAISE myException1;

  < CODE GOES HERE >
  RAISE myException2;  
  
EXCEPTION
  WHEN myException1 THEN
    < CODE GOES HERE >
  WHEN myException2 THEN
    < CODE GOES HERE >
  WHEN OTHERS THEN  -- handles all other errors
    < CODE GOES HERE >

END;

Author : Matt Hawkins  Last Edit By : Matt Hawkins
PHP Powered  MySQL Powered  Valid XHTML 1.0  Valid CSS  Firefox - Take Back The Web  EUKHost - Recommended Webhosting Solutions

MattHawkins CMS v3.0 - Copyright 2009-2017