Write a PL/SQL program to demonstrate Exceptions. in advanced database
Sure! Below is a PL/SQL program that demonstrates the usage of exceptions in Oracle:
```sql
DECLARE
total_marks NUMBER := 0;
average_marks NUMBER := 0;
BEGIN
-- Divide by zero exception
DBMS_OUTPUT.PUT_LINE('Divide by zero Exception:');
BEGIN
average_marks := total_marks / 0;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error: Divide by zero exception');
END;
-- Numeric value exception
DBMS_OUTPUT.PUT_LINE('Numeric Value Exception:');
BEGIN
total_marks := 'ABC';
EXCEPTION
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Error: Invalid numeric value');
END;
-- Custom exception
DBMS_OUTPUT.PUT_LINE('Custom Exception:');
BEGIN
IF total_marks < 50 THEN
RAISE_APPLICATION_ERROR(-20001, 'Student failed the exam');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error Code: ' || SQLCODE || ', Error Message: ' || SQLERRM);
END;
END;
/
```
In this program, we simulate three different scenarios where exceptions can occur:
1. Divide by zero exception: We try to divide a number by zero, which results in a zero_divide exception. We catch this exception and display an error message.
2. Numeric value exception: We assign a non-numeric value ('ABC') to a number variable, causing a value_error exception. We catch this exception and display an error message.
3. Custom exception: We define a custom exception using the RAISE_APPLICATION_ERROR procedure. If the total_marks variable is less than 50, we raise an exception with the error code -20001 and a custom error message. We catch this exception using the WHEN OTHERS clause and display the error code and message.
Note that exceptions are handled using the BEGIN-END block and EXCEPTION clause. Also, we use the DBMS_OUTPUT.PUT_LINE procedure to display the error messages. You may need to enable the DBMS_OUTPUT by executing the SET SERVEROUTPUT ON command before running this program.