SQLite provides a powerful error handling framework to handle the Exceptional errors. SQLite Exceptions are predefined to handle these errors and also defines individual exceptions to take care of business logic validations. It occurs in the SQLite Exceptions block called as Exception Handling. By using this, the code can be executed and errors can be prevented. SQLite Exceptions are categorized into three types, they are:

User named exceptions are internally defined SQLite Exceptions that are defined with a name character. It declared these exceptions in a package and DBMS standards, which defines the SQLite environment available globally. NO_DATA_FOUND Exception is used to fetch a column value such as dept_id=10 from the department's table. If the value does not exists in the table, predefined NO_DATA_FOUND exception is raised and can catch the value by having specific exception handler. So, in the SQLite Exceptions block use NO_DATA_FOUND exception handler. It is an internally defined error and can see from its SQLCODE and SQLERRM. It is very useful to execute specific actions for specific exceptions. In pre-defined exceptions, duplicate values such as index exceptions are raised when storing duplicate values in the database.

where, Exception => It is a key word. 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 SQLite error message ORA-01422.

The below example defines user defined exceptions. [c]sqlite> 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 SQLite 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 exceptions 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 predefined exceptions. [c]sqlite> 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.

• SQLite Exceptions - Exception is a run time error.
• User named exception - Declared simply by defining the name of the exception followed by the keyword.
• Predefined exception - These are system defined exceptions that occur internally.