I have the following cursor in a procedure :

procedure Run is
    Cur Cursor is select * from table where condition;
    R Cur%rowtype;

    Open Cur;
    loop
        fetch Cur into R;
        exit when Cur%notfound;

        -- Run some time consuming operations here
        something...
    end loop;
    Close Cur;
end;

This cursor is run a scheduled job.

Assume when running this cursor there are 100 rows that satisfy the where condition.

If, while the procedure is running, I have a new rows inserted in the table that satisfies the same where condition, Is there any way that cursor picks also these new row please ?

Thanks. Cheers,

CodePudding user response：

No.

The set of rows the cursor will return is determined at the time the cursor is opened. At that point, Oracle knows the current SCN (system change number) and will return the data as it existed at that point in time.

Depending on the nature of the problem, you could write a loop that just keeps asking for a single row that meets the criteria (assuming your time-consuming operation updates some data so that you know what needs to be processed). Something like

loop
  begin
    select some_id
      into l_some_id
      from your_table
     where needs_processing = 'Y'
     order by some_id
     fetch first row only;
  exception
    when no_data_found
    then
      l_some_id := null;
  end;
  
  exit when l_some_id is null;
      
  some_slow_operation( l_some_id );
end loop;

assuming that some_slow_operation changes the needs_processing flag to N. And assuming that you are using the default read committed transaction isolation level.

CodePudding user response：

You can have commit inside loop so that select query fetches latest records from table in every iteration.

CodePudding user response：

No, a cursor can't do that. The transactions are consistent and your cursor is a snapshot of the data you've extracted. If you want consistent results you could either:

1. Lock the table so that there will be no changes,
2. Use other mechanism e.g. move the logic to a trigger, which will execute on each new piece of data that satisfies your conditions (and bring overhead too so very situational)