Exceptions:
An error condition during a program excution is called an exception in PL/SQL. We can catch such errors using EXCEPTION block in the program and an appropriate action is taken against the error condition.
There are two types of exceptions:
- System defined exceptions
- User defined exceptions
Syntax:
DECLARE
<declarations section>
BEGIN
<executable commands>
EXCEPTION
WHEN exception1 THEN
exception1-handling-statements
WHEN exception2 THEN
exception2-handling-statements
WHEN exception3 THEN
exception3-handling-statements
.......
WHEN others THEN
exception-handling-statements
END;
Exceptions example:
Fetch customer details based on customer id. There are 3 possibilities to get the responses.
1. Successfully fetched 1 customer
2. Error with no customer
3. Many customers records found.
CREATE OR REPLACE PROCEDURE GET_CUSTOMER
(
c_id IN NUMBER
)
AS
c_name customer.first_name%type;
c_country customer.country%type;
BEGIN
SELECT first_name, country INTO c_name, c_country FROM customer WHERE customer_id = c_id;
dbms_output.put_line('Name: '|| c_name);
dbms_output.put_line('Country: '|| c_country);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN too_many_rows THEN
dbms_output.put_line('You got more than 1 row!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
Run the procedure:
Execute get_customer(10);
User defined exceptions:
PL/SQL allows us to define our own exceptions according to the need of our program. A user defined exception must be declared and then raised explicitly, using either a RAISE.
CREATE OR REPLACE PROCEDURE GET_CUSTOMER
(
c_id IN NUMBER
)
AS
c_name customer.first_name%type;
c_country customer.country%type;
ex_customer_id EXCEPTION;
BEGIN
IF c_id <=0 THEN
RAISE ex_customer_id;
SELECT first_name, country INTO c_name, c_country FROM customer WHERE customer_id = c_id;
dbms_output.put_line('Name: '|| c_name);
dbms_output.put_line('Country: '|| c_country);
EXCEPTION
WHEN ex_customer_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 too_many_rows THEN
dbms_output.put_line('You got more than 1 row!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
System defined Exception List:
An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system dependent limit.
PL/SQL declares predefined exceptions globally in package STANDARD which defines the PL/SQL environment. So we need not to declare them again and just to reuse them.
Few of them are below:
ZERO_DIVIDE : when your program attempts to divide a number by zero
VALUE_ERROR : when an arithmatic, conversion, truncation or size-constraint error occurs.
STORAGE_ERROR: PL/SQL runs out of memory ot memory has been corrupted.
ROWTYPE_MISMATCH : the host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types.
No comments:
Post a Comment