Odtnhj

I am having nested table

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

Now the question is how I can update comm_amount column using a cursor?

For now, I have this, but I cannot get the access to the sub table (commission, any suggestions?

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;
/

EDIT

Here is the desc of my table:

Name Null? Type 
---------- -------- ------------ 
EMPNO NOT NULL NUMBER(4) 
ENAME VARCHAR2(10) 
BONUS NUMBER 
COMMISSION COMM_ARRAY

comm_array->12*times(comm_month, comm_amount)

ANd I want to update comm_amount in a specific month.

SOLUTION

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;
/

There are few limitation when you use varrays. One of them is when you do DML operations on table having columns of datatype varray like shown in your example. You can use a Nested table and achieve your requirement as shown below in my demo. However please bear in mind the nested table operations are quite complicated to understand. See below and read inline comments.

--Created Table emp2 with an additional column
CREATE TABLE emp2 (ename VARCHAR2(10));

--Object 
CREATE OR REPLACE TYPE COMM_TYPE AS OBJECT
 (COMM_MONTH VARCHAR(5),
 COMM_AMOUNT NUMBER); 

--Created a Table of object rather then varray.
CREATE OR REPLACE TYPE COMM_ARRAY AS TABLE OF COMM_TYPE;

--Modified table emp2. Added column commission as shown in your example
ALTER TABLE EMP2 ADD COMMISSION COMM_ARRAY NESTED TABLE COMMISSION STORE AS TBA1;

--Inserted records
INSERT INTO EMP2 VALUES('AAA',COMM_ARRAY(COMM_TYPE('NOV',100)));
INSERT INTO EMP2 VALUES('BBB',COMM_ARRAY(COMM_TYPE('DEC',200)));

--Selected Records
SQL> SELECT C.COMM_AMOUNT,C.COMM_MONTH
 2 FROM EMP2 E, TABLE (E.COMMISSION) C
 3 WHERE C.COMM_MONTH = 'DEC';

COMM_AMOUNT COMM_
----------- -----
 200 DEC 

--Block to Update records

DECLARE 
 CURSOR c_comm_amount_cursor IS 
 select c.comm_amount,c.comm_month
 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
 --With the help of table operator you can update records of a nested table but not varray.
 UPDATE table( Select commission from emp2 where ename = 'BBB') e --<--Make sure to use additional column of the table to make unique record selection for update
 SET e.comm_amount = e.comm_amount + 100 
 WHERE CURRENT OF c_comm_amount_cursor;
 END LOOP;
 COMMIT;
END;
/

-- You can see update being done.

SQL> /

COMM_AMOUNT COMM_
----------- -----
 300 DEC

Also, as mentioned in comments, the use of loop looks redundant and the block can be further simplified as below:

BEGIN
 --With the help of table operator you can update records of a nested table but not varray.
 UPDATE table( Select commission from emp2 where ename = 'BBB') e --<--Make sure to use additional column of the table to make unique record selection for update
 SET e.comm_amount = e.comm_amount + 100 
 WHERE e.comm_month ='DEC';

 COMMIT;
END;

EDIT:

how can I update every employee, here you choose only one with name
'BBB'. Is there a way?

As mentioned in my comments you need can use dynamic SQL to update all the employees as show below:

DECLARE 
v_sql varchar2(2000);
CURSOR c_enme_cursor IS 
 select ename
 from emp2;
BEGIN
FOR emp_recd IN c_enme_cursor 
LOOP
 v_sql:=q'[
 UPDATE table( Select commission from emp2 where ename = ']'||emp_recd.ename||q'[') e 
 SET e.comm_amount = e.comm_amount + 100 
 -- WHERE e.comm_month ='DEC'
 ]';
 EXECUTE IMMEDIATE V_SQL; 
END LOOP;
COMMIT;
END;

Hi the fast answer is:

DECLARE
 CURSOR C_COMM_AMOUNT_CURSOR
 IS
 SELECT ROWID ROW_ID
 FROM EMP2 E
 WHERE E.COMM_MONTH = 'DEC';
BEGIN
 FOR EMP_RECORD IN C_COMM_AMOUNT_CURSOR
 LOOP
 UPDATE TABLE (
 SELECT COMMISSION
 FROM EMP2
 WHERE ROWID = ROW_ID
 )
 SET COMM_AMOUNT = COMM_AMOUNT + 100
 WHERE ;--your where clause condition
 END LOOP;
END;

you should know when working with nested tables you have to use TABLE() around your nested table in SQL scripts, in PL/SQL it is bit more complicated and you have to create a nested object run time and fill it with your data and manipulate it and later save it on your table which is too far, I have experienced this problem and using ROWID was more readable.