Oracle provides powerful error handling framework in PL/SQL to handle the PLSQL Exception errors. It provides us with predefined PLSQL Exception to handle these errors and also defines individual exceptions to take care of business logic validations. It occurs in the PL/SQL Exception block called as Exception Handling. By using this, the code can be executed and errors can be prevented. PL/SQL Exception is categorized into three types, they are:

Oracle Internal PL/SQL Exceptions are defined exceptions that start with the ORA keyword followed by the negative numeric error code. These type of errors are raised automatically by Oracle when the error condition occurs.

• By defining a integer variable of type PLS_INTEGER and assigning a value outside its range. This will cause oracle to raise a run time exception with error code ORA-01426 for numeric variable.
• The first error code, ORA-6512 is a common error code and gives the line number where blunders are raised.
• At the top of the stack, the actual error code is listed as ORA-01426 and the bottom defines the list of error codes, the causes and actions to fix them.

Oracle has got better with these errors and documenting causes & actions wherever applicable to help the defects. As there is nothing to handle, it will simply throw errors to host the environment.

NO_LOGGED_ON is another type of predefined exception that arises when the SQL statements are not connected with the database. TOO_MANY_ROWS is another type of predefined exception where in select statement only one row has to be selected, but it is selecting more than one row and throws the exception by displaying SQL error message ORA-01422.

The below example defines predefined exceptions. [c]SQL> declare 2 c_id customer.id%type:=8; 3 c_name customers.name%type; 4 5 c_addr customers.address%type; 6 begin 7 select name,address into c_name,c_addr 8 from customers 9 where id=c_id; 10 dbms_output.put_line('name:'||c_name); 11 dbms_output.put_line('address:'||c_addr); 12 exception 13 when no_data_found then 14 dbms_output.put_line('no such customer!'); 15 when other then 16 dbms_output.put_line('error!'); 17 end; 18 / no such customer PL/SQL successfully executed [/c] In the above example, it will check the values of both customer id and customer name. If both values match, it will display the output or else it will show, no such customer id and customer name exists in the table.

User named PLSQL Exception allow naming exceptions specifically. However, errors will be raised when validation fails for business logic. Exceptions that are internally defined by Oracle do not contain any predefined naming exceptions. At this point, predefined exceptions cannot handle. For this, oracle defines user defined exceptions. User defined exceptions are defined in business logic validations, and can be declared simply by giving a name to the exception followed by exception keyword. Also, these can be declared as variables in the declaration sections. Unlike normal variables, it cannot assign values to these exceptions. Internally defined exceptions are consequently raised by oracle and explicitly unless they are mapped to oracle internal errors. Exceptions that are defined by users are declared and raised explicitly using either through DBMS_STANDARD.RAISE _APPLICATION_ERROR procedure or RAISE statement.

The below example defines user defined exceptions. [c]SQL> DECLARE 2 c_id customer,id%type:=&cc_id; 3 c_addr customers.address%type; 4 --user defined execption 5 ex_invalid_id EXCEPION; 6 BEGIN 7 IF c_id<=0 THEN 8 RAISE ex_invalid_id; 9 ELSE 10 SELECT name,address INTO c_name,c_addr 11 FROM customers 12 WHERE id=c_id; 13 DBMS_OUTPUT.PUT_LINE('Name:'||c_name); 14 DBMS_OUTPUT.PUT_LINE('Address:'||c_addr); 15 END IF; 16 EXCEPTION 17 WHEN ex_invalid_id THEN 18 dbms_output.put_line('ID must be greater than zero!'); 19 WHEN no_data_found THEN 20 dbms_output.put_line('No such customer!'); 21 WHEN other THEN 22 dbms_output.put_line('Error!'); 23 END; 24 / Enter value for cc_id: -5 old 2: c_id customer,id%type:=&cc_id; new 2: c_id customer,id%type:=-5; c_id customer,id%type:=-5; cc_id must be greater than zero.[/c] In the above example, cc_id should be assigned between zero and any positive value. If any negative value is assigned, then it gives an error.

• PLSQL Exception - Exception is a run time error.
• Oracle internal exception - Defines exception with ORA keyword.
• User named exception - These are system defined exceptions that occur internally.
• User defined exception - Declared simply by defining the name of the exception followed by the keyword.