I want to get the departments name entered into a temporary table , based on a condition and want the entered values outputted to the screen.But getting an error in outputting
I have 28 records in Departments table but condition given is for 7 records ,so used the EXCEPTION clause in both input and output loops.
SQL> DECLARE
2 TYPE my_dept is table of departments.department_name%TYPE INDEX BY BINARY_INTEGER;
3 my_dept_table my_dept;
4 v_count number(4);
5 v_department_id departments.department_id%TYPE;
6 BEGIN
7 SELECT max(department_id) INTO v_count
8 FROM departments;
9 FOR i IN 1..v_count
10 LOOP
11 BEGIN
12 v_department_id:=
13 CASE
14 WHEN i=1 THEN 10
15 WHEN i=2 THEN 20
16 WHEN i=3 THEN 50
17 WHEN i=4 THEN 60
18 WHEN i=5 THEN 80
19 WHEN i=6 THEN 90
20 WHEN i=7 THEN 110
21 ELSE NULL
22 END;
23 SELECT department_name INTO my_dept_table(i) FROM departments
24 WHERE department_id=v_department_id;
25 EXCEPTION
26 WHEN no_data_found THEN
27 NULL;
28 END;
29 END LOOP;
30 FOR i IN 1..v_count
31 LOOP
32 BEGIN
33 dbms_output.put_line(my_dept_table(i).department_name);
34 EXCEPTION
35 WHEN no_data_found THEN
36 NULL;
37 END;
38 END LOOP;
39 END;
40 /
dbms_output.put_line(my_dept_table(i).department_name);
*
ERROR at line 33:
ORA-06550: line 33, column 41:
PLS-00487: Invalid reference to variable 'DEPARTMENTS.DEPARTMENT_NAME%TYPE'
ORA-06550: line 33, column 3:
PL/SQL: Statement ignored