To the point guide to PL/SQL

PL/SQL exception is a run time error during execution of a program. To handle exception PL/SQL supports developers using EXCEPTION block in the program. There are built in exception or user can also define exception. Built in exception are array out of bound, out of memory, division by zero.

Types of Exceptions:

• System-defined exceptions
• User-defined exceptions

System-defined exceptions:

Syntax:

DECLARE
   	Declaration part; 
BEGIN 
   	statements; 
EXCEPTION
	WHEN built-in_exception1 THEN
		User defined statement to handle exception;
	WHEN built-in_exception2 THEN
		User defined statement to handle exception;	
END;

Example:

DECLARE
	tmp enum%rowtype;
BEGIN
	SELECT * INTO tmp FROM enum
		WHERE id=3;
EXCEPTION
	WHEN no_data_found THEN
	dbms_output.put_line("Table doesn’t have any data");
END;
/

Result:

Table doesn’t have any data
PL/SQL procedure successfully operation.

Adding Exceptions:

In PL/SQL user can also define exception.

Declare exception:
user_define_exception_name EXCEPTION;

RAISE exception:

This statement to raised defined exception name and control transfer to an EXCEPTION block.

RAISE name _of_user_define_exception;

Exception condition implementation:

In PL/SQL WHEN condition to implement user define EXCEPTION block is added.

WHEN name_of_user_define_exception THEN
User defined statement;

Syntax:

DECLARE
	Name_of_user_define_exception_ EXCEPTION;
BEGIN
	statements;
	IF condition THEN
	RAISE name_of_user_define_exception;
	END IF;
EXCEPTION
	WHEN name_of_user_define_exception THEN
		User defined statement;	
END;
/

Example:

DECLARE
	exceptionexp EXCEPTION;
	y NUMBER;
BEGIN
	FOR y IN (SELECT * FROM enum) LOOP
		IF y.id = 2 THEN
			RAISE exceptionexp;
		END IF;
	END LOOP;
EXCEPTION
	WHEN exceptionexp THEN
	 dbms_output.put.line('This id already exist in table enum.');
END;
/

Result:

This id already exist in table enum
PL/SQL procedure successfully operation.

Some Pre-defined Exceptions: