title: Using Cursor for updating nested tables pl/sql
body: <p>I am having nested table </p>
  
  <pre><code>create or replace type comm_type as object
   (comm_month varchar(5),
   comm_amount  number); 
  
  create or replace TYPE comm_array AS VARRAY(12) OF comm_type;
  
  alter table emp2 add commission comm_array
  </code></pre>
  
  <p>Now the question is how I can update <code>comm_amount</code> column using a <strong>cursor</strong>?</p>
  
  <p>For now, I have this, but I cannot get the access to the sub table (commission, any suggestions?</p>
  
  <pre><code>DECLARE 
    CURSOR c_comm_amount_cursor IS 
     select c.comm_amount
      from emp2 e, table (e.commission) c
      where c.comm_month = 'DEC' for update of c.comm_month nowait;
  BEGIN
     FOR emp_record IN c_comm_amount_cursor LOOP
      UPDATE emp2
       SET  emp2.commission.comm_amount = emp2.commission.comm_amount + 100
       WHERE CURRENT OF c_emp_cursor;
    END LOOP;
  END;
  /
  </code></pre>
  
  <p><strong>EDIT</strong></p>
  
  <p>Here is the desc of my table:</p>
  
  <pre><code>Name       Null?    Type         
  ---------- -------- ------------ 
  EMPNO      NOT NULL NUMBER(4)    
  ENAME               VARCHAR2(10)    
  BONUS               NUMBER       
  COMMISSION          COMM_ARRAY
  
  comm_array-&gt;12*times(comm_month, comm_amount)
  </code></pre>
  
  <p>ANd I want to update comm_amount in a specific month.</p>
  
  <p><strong>SOLUTION</strong></p>
  
  <pre><code>DECLARE 
    CURSOR c_comm_amount_cursor IS 
     select c.comm_amount,c.comm_month, e.empno
      from emp2 e, table (e.commission) c
      where c.comm_month = 'DEC'for update of c.comm_month nowait;
  
  BEGIN
    FOR emp_record IN c_comm_amount_cursor 
    LOOP
       UPDATE table(Select commission from emp2 where empno = emp_record.empno) e 
       SET  e.comm_amount = e.comm_amount + 100
       WHERE CURRENT OF c_comm_amount_cursor;
   END LOOP;
  END;
  /
  </code></pre>