create or replace procedure ttt is
v_min varchar2(2);
type TYPE_CUR is ref cursor;
type type_array is table of varchar2(20);
v_cur TYPE_CUR ;
v_number varchar2(1);
var_array type_array:=type_array('s','x','X','0','1','2');
begin
select case
when min(substr(ID, 5)) = 'A' then
'9'
when min(substr(ID, 5)) = 'a' then
'Z'
when min(substr(ID, 5)) = '0' then
'-1'
else
CHR(to_number(ASCII(NVL(min(substr(ID,5)), '0'))-1))
end case
into v_min
from test;
if v_min ='-1' then
open v_cur for select to_char(nvl((substr("ID", 5)),0)) from test;
loop
fetch v_cur into v_number;
exit when v_cur%notfound;
FOR i IN 1..var_array.COUNT LOOP
if var_array(i)=v_number then
var_array.delete(i);
exit;
end if;
end loop;
end loop;
close v_cur;
v_min:=var_array(var_array.FIRST);
end if;
end ttt;