In this part, we will examine Exceptions in PL/SQL. An exemption is a mistake condition during a program execution. PL/SQL underpins developers to find such conditions utilizing EXCEPTION block in the program and a proper move is made against the blunder condition. There are two kinds of exemptions −
- Framework characterized exemptions
- Client characterized exemptions
Syntax for Exception Handling
The overall language structure for special case dealing with is as per the following. Here you can list down as possible handle. The default special case will be dealt with utilizing WHEN others THEN −
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling goes here >
WHEN exception1 THEN
exception1-handling-statements
WHEN exception2 THEN
exception2-handling-statements
WHEN exception3 THEN
exception3-handling-statements
........
WHEN others THEN
exception3-handling-statements
END;
Example
Allow us to compose a code to show the idea. We will utilize the CUSTOMERS table we had made and utilized in the past parts −
DECLARE
c_id customers.id%type := 8;
c_name customerS.Name%type;
c_addr customers.address%type;
BEGIN
SELECT name, address INTO c_name, c_addr
FROM customers
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
At the point when the above code is executed at the SQL brief, it creates the accompanying outcome −
No such customer!
PL/SQL procedure successfully completed.
The above program shows the name and address of a client whose ID is given. Since there is no client with ID esteem 8 in our information base, the program raises the run-time special case NO_DATA_FOUND, which is caught in the EXCEPTION block.
Raising Exceptions
Special cases are raised by the information base worker naturally at whatever point there is any inner data set mistake, however exemptions can be raised unequivocally by the software engineer by utilizing the order RAISE. Following is the basic grammar for raising a special case −
DECLARE
exception_name EXCEPTION;
BEGIN
IF condition THEN
RAISE exception_name;
END IF;
EXCEPTION
WHEN exception_name THEN
statement;
END;
You can utilize the above language structure in increasing the Oracle expectation special case or any client characterized exemption. In the following area, we will give you a model on raising a client characterized special case. You can increase the Oracle expectation special cases likewise.
User-defined Exceptions
PL/SQL permits you to characterize your own exemptions as per the need of your program. A client characterized special case should be announced and afterward raised expressly, utilizing either a RAISE articulation or the method DBMS_STANDARD.RAISE_APPLICATION_ERROR.
The punctuation for pronouncing an exemption is −
DECLARE
my-exception EXCEPTION;
Example
The accompanying model outlines the idea. This program requests a client ID, when the client enters an invalid ID, the special case invalid_id is raised.
DECLARE
c_id customers.id%type := &cc_id;
c_name customerS.Name%type;
c_addr customers.address%type;
-- user defined exception
ex_invalid_id EXCEPTION;
BEGIN
IF c_id <= 0 THEN
RAISE ex_invalid_id;
ELSE
SELECT name, address INTO c_name, c_addr
FROM customers
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
END IF;
EXCEPTION
WHEN ex_invalid_id THEN
dbms_output.put_line('ID must be greater than zero!');
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
At the point when the above code is executed at the SQL brief, it creates the accompanying outcome −
Enter value for cc_id: -6 (let's enter a value -6)
old 2: c_id customers.id%type := &cc_id;
new 2: c_id customers.id%type := -6;
ID must be greater than zero!
PL/SQL procedure successfully completed.
Pre-defined Exceptions
PL/SQL gives numerous pre-characterized special cases, which are executed when any data set guideline is abused by a program. For instance, the predefined special case NO_DATA_FOUND is raised when a SELECT INTO articulation restores no lines. The accompanying table records not many of the significant pre-characterized exemptions −
Exception | Oracle Error | SQLCODE | Description |
---|---|---|---|
ACCESS_INTO_NULL | 06530 | -6530 | It is raised when a null object is automatically assigned a value. |
CASE_NOT_FOUND | 06592 | -6592 | It is raised when none of the choices in the WHEN clause of a CASE statement is selected, and there is no ELSE clause. |
COLLECTION_IS_NULL | 06531 | -6531 | It is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray. |
DUP_VAL_ON_INDEX | 00001 | -1 | It is raised when duplicate values are attempted to be stored in a column with unique index. |
INVALID_CURSOR | 01001 | -1001 | It is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor. |
INVALID_NUMBER | 01722 | -1722 | It is raised when the conversion of a character string into a number fails because the string does not represent a valid number. |
LOGIN_DENIED | 01017 | -1017 | It is raised when a program attempts to log on to the database with an invalid username or password. |
NO_DATA_FOUND | 01403 | +100 | It is raised when a SELECT INTO statement returns no rows. |
NOT_LOGGED_ON | 01012 | -1012 | It is raised when a database call is issued without being connected to the database. |
PROGRAM_ERROR | 06501 | -6501 | It is raised when PL/SQL has an internal problem. |
ROWTYPE_MISMATCH | 06504 | -6504 | It is raised when a cursor fetches value in a variable having incompatible data type. |
SELF_IS_NULL | 30625 | -30625 | It is raised when a member method is invoked, but the instance of the object type was not initialized. |
STORAGE_ERROR | 06500 | -6500 | It is raised when PL/SQL ran out of memory or memory was corrupted. |
TOO_MANY_ROWS | 01422 | -1422 | It is raised when a SELECT INTO statement returns more than one row. |
VALUE_ERROR | 06502 | -6502 | It is raised when an arithmetic, conversion, truncation, or sizeconstraint error occurs. |
ZERO_DIVIDE | 01476 | 1476 | It is raised when an attempt is made to divide a number by zero. |