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.