MyHowTo.org

Using custom exceptions in Oracle PL/SQL


Exception handling mechanism is a very important feature of PL/SQL. In most of the cases it is much simpler to just attempt an operation and properly handle the expected failure rather than to check for the conditions before attempting an operation. For example: it is much easier to unconditionally attempt to drop a table and just ignore the situation with non-existing table than to check the presence of the table first and then drop it.

Oracle offers a number of predefined exceptions. However, this list does not cover all potentially useful errors. There is a simple mechanism that allows the developer to define a custom exception. For example, if we want to drop a partition and do not care if it does not exist, we can do something like this:

declare
	partition_does_not_exist EXCEPTION;
	PRAGMA EXCEPTION_INIT (partition_does_not_exist, -2149);
...
begin
...
	begin
		alter table drop partition my_partition;
	exception
		when partition_does_not_exist then
			-- expected, nothing needs to be done
			null;
		when others then
			-- something else went wrong, handling the
			-- error
			...
	end;
...

The exception we have created with the code “-2149” corresponds to the error ORA-02149 that gets raised in case of an attempt to drop a non-existing table partition.

It is also possible to create custom errors that are not defined in Oracle. Error numbers in the range -20000..-20999 are reserved for the user-defined errors. So, you can easily raise your own error and catch it later as an exception:

	--- ops - will throw ORA-20001!!!
	raise_application_error(-20001, 'Operation failed');

 

References

  1. Summary of Predefined PL/SQL Exceptions
blog comments powered by Disqus

Published

03 July 2008

Tags