Is savepoint required inside FOR loop of PLSQL oracle?

Question

just wondering if we really need rollback/savepoint required inside a LOOP statement if we have exception catch inside.

create table my_Tab
(id number,
name varchar2(10),
address varchar2(100));

insert into my_tab values (1,'ABC','XXX');
insert into my_tab values (2,'DEF','YYY');
insert into my_tab values (3,'GHI','ZZZ');
insert into my_tab values (4,'JKL',null);

DECLARE
CURSOR cur_my_tab is
select * from my_tab;
l_var varchar2(100);
l_count number := 0;
begin
for rec_my_tab in cur_my_tab
loop
    BEGIN
    dbms_output.put_line('id' || rec_my_tab.id);
    --savepoint sv_cur_my_tab;
        dbms_output.put_line('name' || rec_my_tab.name);
        select rec_my_tab.address into l_var from dual;
        update my_Tab set id = id +4 where id = rec_my_tab.id;
        dbms_output.put_line(l_var);
        if rec_my_tab.address ='xxx' then
            l_count:= rec_my_tab.id/l_count;
        END IF;
    Exception
        when others then 
            dbms_output.put_line('in inner exception');
            --rollback to sv_cur_my_tab;*/
    end;
end loop;
Exception
    when others then 
        dbms_output.put_line('in outer exception');
        --rollback to sv_cur_my_tab;
end;

I have 2 queries wrt above query.

1. When does the commit happen in the above sequence.
2. in the above code consider the exception is thrown in the middle of the cursor (like 3rd or 4th iteration). will adding rollback & savepoint make a change in this? currently, the entire loop is completed and all updates are getting committed as well.

Answer 1

When does the commit happen in the above sequence?

It doesn't. At least, I don't see a commit statement. Maybe I'm blind. But there is no "autocommit" in PL/SQL. So, a commit won't take place until you issue one (or your client software does).

in the above code consider the exception is thrown in the middle of the cursor (like 3rd or 4th iteration). will adding rollback & savepoint make a change in this? currently, the entire loop is completed and all updates are getting committed as well.

Rolling back to a savepoint is generally useful when you have multiple DML statements inside the cursor loop and you both

1. want to ensure that, for each cursor record, that the DML statements either ALL succeed or none of them do
2. do not want to commit anything until all the rows are processed

In your case, you only have one update inside the loop and after that you change the value of the 'l_count' variable. If you want the update to roll back in the event of any problem changing 'l_count', the rolling back to a savepoint is what you need.

Keep in mind that rolling back to a savepoint will NOT revert any changes to PL/SQL variables, such as 'l_count'. You're responsible for restoring their values as needed before you rollback to the savepoint.