Handling ACCESS_INTO_NULL exception in PL/SQL
PL/SQL Exception Handling: Exercise-15 with Solution
Handle the ACCESS_INTO_NULL exception when trying to access a NULL record.
Sample Solution:
Table: employees
employee_id integer first_name varchar(25) last_name varchar(25) email archar(25) phone_number varchar(15) hire_date date job_id varchar(25) salary integer commission_pct decimal(5,2) manager_id integer department_id integer
PL/SQL Code:
DECLARE
v_employee_idemployees.employee_id%TYPE := 110;
v_commission_pctemployees.commission_pct%TYPE;
BEGIN
SELECT commission_pct INTO v_commission_pct
FROM employees
WHERE employee_id = v_employee_id;
IF v_commission_pct IS NULL THEN
RAISE ACCESS_INTO_NULL;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No data found for employee_id ' || v_employee_id);
WHEN ACCESS_INTO_NULL THEN
DBMS_OUTPUT.PUT_LINE('Caught ACCESS_INTO_NULL exception: Trying to access NULL value in the commission_pct column for employee_id ' || v_employee_id);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;
Sample Output:
Caught ACCESS_INTO_NULL exception: Trying to access NULL value in the commission_pct column for employee_id 110
Explanation:
The said code in Oracle's PL/SQL that demonstrates how to handle the ACCESS_INTO_NULL exception specifically and produced an appropriate message for the user.
A variable v_employee_id is declared and assigns it a specific employee ID 110 and another variable v_commission_pct is declared to store the commission percentage for the employee.
The SELECT statement retrieves the commission_pct value from the employees table against the given employee ID. If the commission_pct is NULL, the code raises the ACCESS_INTO_NULL exception and it prints a message indicating an attempt to access a NULL value in the commission_pct column for the employee.
The other exception if the NO_DATA_FOUND occurs, a message that indicates no data was found for the specified employee ID. For any other exception, the code outputs a general error message with the SQLERRM function.
Flowchart:
Previous: Handling ROWTYPE_MISMATCH exception in PL/SQL.
Next: Handling NO_DATA_NEEDED exception in PL/SQL.
What is the difficulty level of this exercise?
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/plsql-exercises/exception-handling/plsql-exception-handling-exercise-15.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics