oracle cursor concurrency with insert stmt

Question

cursor's result is determined at the time of opened ? but the following demo reveal a difference :

drop table Highschooler;
drop table w;
create table Highschooler( grade int );
create table w( grade int );

insert into Highschooler(grade) values (13);
insert into Highschooler(grade) values(14);
insert into Highschooler(grade) values (15);
insert into w values (16);

select * from Highschooler;
select * from w;

create or replace create function ff(a int) return int is 
   total int := 0;
begin
  select  count(grade) into total from w where grade > a ;
  return total;
end;

DECLARE
   my_var int :=0;
   my_var2 int := 0;
   my_var3 int := 0;
   CURSOR CC IS select ff(grade) from Highschooler for update;
BEGIN
   open CC;
   fetch CC into my_var;
   insert into w values (16);
   fetch CC into my_var2;
   fetch CC into my_var3;
   dbms_output.put_line(my_var || '  - '  || my_var2 || ' - ' || my_var3);
   close CC;
end;

outputs:

GRADE
13
14
15
Download CSV
3 rows selected.

Result Set 4
GRADE
16
Statement processed. ------- the insert stmt in 'insert into w values (16);' affect the cursor's output here
1  - 2 - 2

Answer 1

Your function contains another cursor which is only opened at the time of execution. This is why you generally shouldn't use functions to just do SQL - you end up with logically corrupt data as each execution will be using a different SCN point.