SQL & PL/SQL

Folks at OTN,
I have been reading about TOM KYTE's suggestion and recommendation on
NOT ISSUING a COMMIT with in CURSOR LOOPS WHERE a DML operation is posed
on the same table that the CURSOR is based on.

I thought of couple alternatives to go around this and want to run this
by people at OTN and see which one is better And Why?
Also to see if there are any other alternatives in use in the industry.
Your innovative inputs will be greatly appreciated. Hope you will find
this educating.

P.S. Following example is a deliberate attempt to use PL/SQL and meant
to show the concept rather than syntax, otherwise solution itself is
quite simple in SQL.

OPTION I. By using Autonomous Transaction
-----------------------------------------

Create or replace do_ddl(pEmpNum IN Number) IS
    pragma autonomous_transaction;
    vNewSal Number := 0;
Begin
     -- compute new salary here..into vNewSal
     -- dml operation here
     UPDATE Emp SET SAL = vNewSal
     WHERE empno = pEmpNum;
     COMMIT;
End;

-- Main Program
DECLARE
Cursor C1 Is SELECT * FROM EMP
WHERE SAL > 5000;
...
Begin
  FOR r1 in C1 
  LOOP
      ....
      do_ddl(r1.empno); -- call to AT (Autonomous Transaction)
      ....
   END LOOP;
End;

PROS: 1. Work around for COMMIT issue with in Cursor Loops.

CONS: 1. Context switch between SQL and PL/SQL is still expensive ??


OPTION II. Use PL_SQL tables and Bulk Binding
---------------------------------------------

-- Create couple of SQL types here
Create or replace type empNumType as table of Number;

Create or replace type salType as table of Number;

-- Main Program
DECLARE
....
-- array of emp numbers
vEmpNumArray  empNumType := empNumType();

-- array of salaries
vSalArray     salType    := salType();

vNewSal       Number     := 0;

Cursor C1 Is SELECT * FROM EMP
WHERE SAL > 5000;
...
Begin
  FOR r1 in C1 
  LOOP
      ....
      -- fill the pl_sql table(s) with emp numbers & salaries
      vEmpNumArray.extend;
      vEmpNumArray(vEmpNumArray.count) := r1.rempno;
      -- compute salary here..into vNewSal
      -- fill each element of salary array with new salary
      vSalArray.extend;
      vSalArray(vSalArray.count) := vNewSal;
      ....
   END LOOP;
   
   Forall i in 1..vEmpNumArray.Count
   LOOP
      UPDATE EMP
      SET SAL = vSalArray(i)
      WHERE empno = vEmpNumArray(i);

   COMMIT;
End;

PROS: 1. Work around for COMMIT problem in CURSOR Loops.
      2. Context switch between SQL and PL/SQL is **eliminated**
      3. Tends to RUN faster.

CONS: 1. Over head of creating extra types in SQL
      2. Coding is not as simple as option I.

Thx,
SriDHAR