SQL & PL/SQL

Hello ..
I am not using any commits inside cursors with for update statements. This was running for the last 48 hrs and suddenly started throwing errors. Can anyone help me why it has problems sporadically. Code is enclosed here.

Is Sys_refcursor a dynamic cursor?.. I have my transaction_cursor as a sys_refcursor? Is that the problem here. If so, Do I need to remove commit inside the opening and closing of this cursor. The commit is executed for every 10000 rows..

Thanks in Advance
Kris

PROCEDURE PROCESS_EXECUTOR AS

cursor jobs_cursor is
select job_id from (
SELECT DISTINCT
job_id
FROM table_x )
ORDER BY job_id ) where ROWNUM <= 10;

transaction_cursor SYS_REFCURSOR;
tran tran_type;

sql_code varchar2(1024);
err_msg varchar2(1024);

cmt_counter number := 0;


BEGIN -- Process_Executor Start


for r_csr in jobs_cursor loop
OPEN transaction_cursor FOR SELECT * FROM table_y a
WHERE a.job_id = r_csr.job_id
order by create_timestamp, task_id;
LOOP
FETCH transaction_cursor BULK COLLECT INTO tran LIMIT 10;
EXIT WHEN tran.COUNT = 0;



FOR i IN 1..tran.COUNT LOOP
begin

if cmt_counter = 0 then
savepoint last_transaction;
end if;

cmt_counter := cmt_counter + 1;


exec_process1(tran(i));

if (cmt_counter = 10000 ) then
commit;
cmt_counter := 0;
end if;


exception
when others then
rollback to last_transaction;
end;
END LOOP;

END LOOP;
CLOSE transaction_cursor;

end loop;


commit;


END PROCESS_EXECUTOR;