w3resource

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:

Flowchart: PL/SQL Exception Handling Exercises - Handling ACCESS_INTO_NULL exception in PL/SQL

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?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

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