PL/SQL - SPLessons

PL/SQL Exception

Chapter 21

SPLessons 5 Steps, 3 Clicks
5 Steps - 3 Clicks

PL/SQL Exception

PL/SQL Exception

shape Description

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 PLSQL Exception

shape Description

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.

shape Conceptual
figure

User named exceptions

shape Description

User named exceptions are internally defined exceptions that are defined with a name character. Oracle has declared these exceptions in a package and DBMS standards, which defines the PL/SQL environment available globally in

  • PL/SQL Local subprograms -Is an encapsulation of computation in some forms.
  • Defining a local subprogram – Is a stored procedure and stored function.
  • Roles and privileges – Grants privileges to the user code. These are some of the most common errors that have been given a name by oracle.

NO_DATA_FOUND Exception is used to fetch a column value such as dept_id=10 from the department’s table. If the value doesn’t exist in the table, it will raise the predefined NO_DATA_FOUND exception. And can catch the value by having specific exception handler. So, in the exception 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 is 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;

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 SQL error message ORA-01422.

shape Examples

The below example defines predefined exceptions.

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

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

shape Description

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.

shape Syntax

<exception_name>EXCEPTION;

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

EXCEPTION => Exception keyword.

shape Examples

The below example defines user defined exceptions.

SQL> DECLARE
  2  c_id customer,id%type:=&amp;cc_id;
  3  c_addr customers.address%type;
  4  --user defined execption
  5  ex_invalid_id EXCEPION;
  6  BEGIN
  7  IF c_id&lt;=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:=&amp;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

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