SQLite - SPLessons

SQLite Exceptions

Chapter 22

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

SQLite Exceptions

SQLite Exceptions

shape Description

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:

 

shape Conceptual
figure

User named exceptions

shape Description

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.

shape Syntax

BEGIN
Execution section
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line(‘A SELECT ….INTO DID NOT RETURN ANY ROW’);
END;

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.

shape Examples

The below example defines user defined exceptions.

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

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 Defined exceptions

shape Description

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.

shape Syntax

EXCEPTION;

Exception_name => Name of the exception and it’s type.

EXCEPTION => Exception keyword.

shape Examples

The below example defines predefined exceptions.

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.

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.

Summary

shape Key Points

  • 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.