Handling SUBSCRIPT_BEYOND_COUNT exception in PL/SQL

PL/SQL Exception Handling: Exercise-13 with Solution

Handle the SUBSCRIPT_BEYOND_COUNT exception when accessing an array element beyond its declared size.

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
   TYPE employees_array IS TABLE OF hr.employees%ROWTYPE INDEX BY PLS_INTEGER;
emp_arrayemployees_array;
v_emp_idhr.employees.employee_id%TYPE;
BEGIN
   FOR emp_rec IN (SELECT * FROM hr.employees)
   LOOP
emp_array(emp_rec.employee_id) := emp_rec;
   END LOOP;
v_emp_id := 207; -- Employee ID that does not exist in the HR employees table
   BEGIN
      IF emp_array.EXISTS(v_emp_id) THEN
         DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_array(v_emp_id).first_name || ' ' || emp_array(v_emp_id).last_name);
      ELSE
         RAISE SUBSCRIPT_BEYOND_COUNT;
      END IF;
   EXCEPTION
      WHEN SUBSCRIPT_BEYOND_COUNT THEN
         DBMS_OUTPUT.PUT_LINE('Employee ID ' || v_emp_id || ' does not exist in the HR employees table.');
   END;
END;
/

Sample Output:

Employee ID 207 does not exist in the HR employees table.

Explanation:

The said code in Oracle's PL/SQL that demonstrates how to handle the SUBSCRIPT_BEYOND_COUNT exception when accessing an index beyond the defined count in a PL/SQL associative array.

An array variable employees_array of type employees ROWTYPE indexed by PLS_INTEGER is declared. It then creates an instance emp_array of employees_array.

Then the code retrieves all records from the employees table and each record is assigned to the corresponding index in emp_array using the employee's ID as the index key.

After populating the emp_array, the code sets v_emp_id to a specific employee ID 207 that does not exist in the employees table.

Then a nested BEGIN-END block handles the potential exceptions. It checks whether v_emp_id exists in emp_array or not, If it exists, it retrieves the employee's first name and last name from emp_array and displays them using the DBMS_OUTPUT.PUT_LINE procedure.

If the employee ID does not exist in emp_array, it raises the SUBSCRIPT_BEYOND_COUNT exception and a corresponding error message is displayed using DBMS_OUTPUT.PUT_LINE.

Flowchart:

Previous: Handling INVALID_TRANSACTION exception in PL/SQL with Example Code.
Next: Handling ROWTYPE_MISMATCH exception in PL/SQL.