declare
cursor erows is select a.import_id from sap_automatic a
where a.sap_id='Q3E' and a.excute_date between sysdate-4/3 and sysdate-1/2; -- declare a cursor
imptid sap_automatic.import_id%TYPE;
clen integer;
cnt integer;
begin
cnt := 0;
open erows; -- open cursor
loop -- iterate over the cursor
fetch erows into imptid; -- fetch row into varible
exit when erows%NOTFOUND;
imptid := regexp_replace(imptid,'[^,]+');
if imptid is null then
clen :=1;
else
clen := length(imptid)+1;
end if;
dbms_output.put_line('Current row is '||erows%ROWCOUNT||', there are '||clen||' importIds.');
cnt := cnt + clen;
end loop;
dbms_output.put_line('Total '||cnt||' requests.');
end;