In order to understand my problem please refer below two points first:
1) The table in reference is hr.employees supplied with oracle 12C default scema:
2) first insert below records in employee table:
INSERT INTO employees VALUES (444, 'Pat', 'Fay', 'PFAY', '603.123.6666', TO_DATE('17-08-2005', 'dd-MM-yyyy'), 'MK_REP', 6000, NULL , 201, 20);
INSERT INTO employees VALUES (555, 'Rat', 'Fay', 'RFAY', '603.123.6666', TO_DATE('17-08-2005', 'dd-MM-yyyy'), 'MK_REP', 6000, NULL , 201, 20);
now my problem is I am writing a pl-sql block to display something like below for all the records
record number 123 is Shanta Vollman
-- but there is a gap between employee_id , that means after employee_id=206 we have two more employee_id's 444 and 555, so I am getting "no data found" error after 206th fetch, I tried to handle this exception.
but my code is only able to print records from employee_id:=100 to employee_id:=206, which are continuous, but the 444 and 555 id's are not printing . I have pasted below the code, Please have a look and get it print the remaining two id's that means from employee_id 100 to 206 and 444 and 555 as well..
declare
type tab_emp is table of varchar2(100)
index by pls_integer;
v_tab_emp tab_emp;
min_emp_id number;
max_emp_id number;
begin
select min(employee_id) into min_emp_id from employees;
select max(employee_id) into max_emp_id from employees;
for i in (min_emp_id)..(max_emp_id)
loop
select first_name||' '||last_name into v_tab_emp(i)
from employees
where employee_id=i;
dbms_output.put_line('record number '||i||' is '|| v_tab_emp(i));
end loop;
EXCEPTION
WHEN no_data_found THEN
null;
end;
let me know if you have better code for this....have a great day .... enjoy!!!!!!