Using Cursor for updating nested tables pl/sql









up vote
2
down vote

favorite
1












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









share|improve this question























  • Could you show us the definition and some sample records of emp2 table? Also, why do you specifically want a cursor loop and not a single update statement?
    – Kaushik Nayak
    Nov 11 at 5:54














up vote
2
down vote

favorite
1












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









share|improve this question























  • Could you show us the definition and some sample records of emp2 table? Also, why do you specifically want a cursor loop and not a single update statement?
    – Kaushik Nayak
    Nov 11 at 5:54












up vote
2
down vote

favorite
1









up vote
2
down vote

favorite
1






1





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









share|improve this question















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






oracle plsql






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 11 at 13:41

























asked Nov 10 at 21:59









rubyBeginner

133




133











  • Could you show us the definition and some sample records of emp2 table? Also, why do you specifically want a cursor loop and not a single update statement?
    – Kaushik Nayak
    Nov 11 at 5:54
















  • Could you show us the definition and some sample records of emp2 table? Also, why do you specifically want a cursor loop and not a single update statement?
    – Kaushik Nayak
    Nov 11 at 5:54















Could you show us the definition and some sample records of emp2 table? Also, why do you specifically want a cursor loop and not a single update statement?
– Kaushik Nayak
Nov 11 at 5:54




Could you show us the definition and some sample records of emp2 table? Also, why do you specifically want a cursor loop and not a single update statement?
– Kaushik Nayak
Nov 11 at 5:54












2 Answers
2






active

oldest

votes

















up vote
0
down vote



accepted










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;





share|improve this answer






















  • That helped me a lot. I have one more question, how can I update every employee, here you choose only one with name 'BBB'. Is there a way?
    – rubyBeginner
    Nov 11 at 9:41










  • In that case you would need a dynamic sql where each time you would be passing the name at run time. Thats what i said, nesting makes things complicated.
    – XING
    Nov 11 at 9:43










  • I just add one more cursor which returns 'empno' and send values to where clause and the thing works! Thank you very much!
    – rubyBeginner
    Nov 11 at 9:52










  • @rubyBeginner. You can see my updated answer as well for your reference
    – XING
    Nov 11 at 10:26

















up vote
0
down vote













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.






share|improve this answer






















  • This will not work incase the column is of type varray and CURRENT OF C_EMP_CURSOR; has to be changed to the cursor name C_COMM_AMOUNT_CURSOR else you will get invalid name error. Your fast answer is really not correct :-)
    – XING
    Nov 11 at 9:29











  • Correct this as well WHERE C.COMM_MONTH = 'DEC'; there is no table with name c. Table alias in your example is E. And you also cannot get nested table column name as done your example. Please take your time before you share your solution,
    – XING
    Nov 11 at 9:35











  • @XING I have tested this code with nested table type and it worked, thanks for your cirrection
    – hmmftg
    Nov 11 at 10:44










  • @XING the code has been tested in Oracle 11g and it worked, you have helped with some syntax errors which was corrected, thank you for that, but your idea " you also cannot get nested table column name as done your example" is incorrect, it is possible and correct.
    – hmmftg
    Nov 12 at 7:02










Your Answer






StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);













draft saved

draft discarded


















StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53243827%2fusing-cursor-for-updating-nested-tables-pl-sql%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown

























2 Answers
2






active

oldest

votes








2 Answers
2






active

oldest

votes









active

oldest

votes






active

oldest

votes








up vote
0
down vote



accepted










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;





share|improve this answer






















  • That helped me a lot. I have one more question, how can I update every employee, here you choose only one with name 'BBB'. Is there a way?
    – rubyBeginner
    Nov 11 at 9:41










  • In that case you would need a dynamic sql where each time you would be passing the name at run time. Thats what i said, nesting makes things complicated.
    – XING
    Nov 11 at 9:43










  • I just add one more cursor which returns 'empno' and send values to where clause and the thing works! Thank you very much!
    – rubyBeginner
    Nov 11 at 9:52










  • @rubyBeginner. You can see my updated answer as well for your reference
    – XING
    Nov 11 at 10:26














up vote
0
down vote



accepted










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;





share|improve this answer






















  • That helped me a lot. I have one more question, how can I update every employee, here you choose only one with name 'BBB'. Is there a way?
    – rubyBeginner
    Nov 11 at 9:41










  • In that case you would need a dynamic sql where each time you would be passing the name at run time. Thats what i said, nesting makes things complicated.
    – XING
    Nov 11 at 9:43










  • I just add one more cursor which returns 'empno' and send values to where clause and the thing works! Thank you very much!
    – rubyBeginner
    Nov 11 at 9:52










  • @rubyBeginner. You can see my updated answer as well for your reference
    – XING
    Nov 11 at 10:26












up vote
0
down vote



accepted







up vote
0
down vote



accepted






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;





share|improve this answer














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;






share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 11 at 10:26

























answered Nov 11 at 9:20









XING

7,3893929




7,3893929











  • That helped me a lot. I have one more question, how can I update every employee, here you choose only one with name 'BBB'. Is there a way?
    – rubyBeginner
    Nov 11 at 9:41










  • In that case you would need a dynamic sql where each time you would be passing the name at run time. Thats what i said, nesting makes things complicated.
    – XING
    Nov 11 at 9:43










  • I just add one more cursor which returns 'empno' and send values to where clause and the thing works! Thank you very much!
    – rubyBeginner
    Nov 11 at 9:52










  • @rubyBeginner. You can see my updated answer as well for your reference
    – XING
    Nov 11 at 10:26
















  • That helped me a lot. I have one more question, how can I update every employee, here you choose only one with name 'BBB'. Is there a way?
    – rubyBeginner
    Nov 11 at 9:41










  • In that case you would need a dynamic sql where each time you would be passing the name at run time. Thats what i said, nesting makes things complicated.
    – XING
    Nov 11 at 9:43










  • I just add one more cursor which returns 'empno' and send values to where clause and the thing works! Thank you very much!
    – rubyBeginner
    Nov 11 at 9:52










  • @rubyBeginner. You can see my updated answer as well for your reference
    – XING
    Nov 11 at 10:26















That helped me a lot. I have one more question, how can I update every employee, here you choose only one with name 'BBB'. Is there a way?
– rubyBeginner
Nov 11 at 9:41




That helped me a lot. I have one more question, how can I update every employee, here you choose only one with name 'BBB'. Is there a way?
– rubyBeginner
Nov 11 at 9:41












In that case you would need a dynamic sql where each time you would be passing the name at run time. Thats what i said, nesting makes things complicated.
– XING
Nov 11 at 9:43




In that case you would need a dynamic sql where each time you would be passing the name at run time. Thats what i said, nesting makes things complicated.
– XING
Nov 11 at 9:43












I just add one more cursor which returns 'empno' and send values to where clause and the thing works! Thank you very much!
– rubyBeginner
Nov 11 at 9:52




I just add one more cursor which returns 'empno' and send values to where clause and the thing works! Thank you very much!
– rubyBeginner
Nov 11 at 9:52












@rubyBeginner. You can see my updated answer as well for your reference
– XING
Nov 11 at 10:26




@rubyBeginner. You can see my updated answer as well for your reference
– XING
Nov 11 at 10:26












up vote
0
down vote













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.






share|improve this answer






















  • This will not work incase the column is of type varray and CURRENT OF C_EMP_CURSOR; has to be changed to the cursor name C_COMM_AMOUNT_CURSOR else you will get invalid name error. Your fast answer is really not correct :-)
    – XING
    Nov 11 at 9:29











  • Correct this as well WHERE C.COMM_MONTH = 'DEC'; there is no table with name c. Table alias in your example is E. And you also cannot get nested table column name as done your example. Please take your time before you share your solution,
    – XING
    Nov 11 at 9:35











  • @XING I have tested this code with nested table type and it worked, thanks for your cirrection
    – hmmftg
    Nov 11 at 10:44










  • @XING the code has been tested in Oracle 11g and it worked, you have helped with some syntax errors which was corrected, thank you for that, but your idea " you also cannot get nested table column name as done your example" is incorrect, it is possible and correct.
    – hmmftg
    Nov 12 at 7:02














up vote
0
down vote













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.






share|improve this answer






















  • This will not work incase the column is of type varray and CURRENT OF C_EMP_CURSOR; has to be changed to the cursor name C_COMM_AMOUNT_CURSOR else you will get invalid name error. Your fast answer is really not correct :-)
    – XING
    Nov 11 at 9:29











  • Correct this as well WHERE C.COMM_MONTH = 'DEC'; there is no table with name c. Table alias in your example is E. And you also cannot get nested table column name as done your example. Please take your time before you share your solution,
    – XING
    Nov 11 at 9:35











  • @XING I have tested this code with nested table type and it worked, thanks for your cirrection
    – hmmftg
    Nov 11 at 10:44










  • @XING the code has been tested in Oracle 11g and it worked, you have helped with some syntax errors which was corrected, thank you for that, but your idea " you also cannot get nested table column name as done your example" is incorrect, it is possible and correct.
    – hmmftg
    Nov 12 at 7:02












up vote
0
down vote










up vote
0
down vote









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.






share|improve this answer














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.







share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 11 at 10:43

























answered Nov 11 at 9:17









hmmftg

1,0001122




1,0001122











  • This will not work incase the column is of type varray and CURRENT OF C_EMP_CURSOR; has to be changed to the cursor name C_COMM_AMOUNT_CURSOR else you will get invalid name error. Your fast answer is really not correct :-)
    – XING
    Nov 11 at 9:29











  • Correct this as well WHERE C.COMM_MONTH = 'DEC'; there is no table with name c. Table alias in your example is E. And you also cannot get nested table column name as done your example. Please take your time before you share your solution,
    – XING
    Nov 11 at 9:35











  • @XING I have tested this code with nested table type and it worked, thanks for your cirrection
    – hmmftg
    Nov 11 at 10:44










  • @XING the code has been tested in Oracle 11g and it worked, you have helped with some syntax errors which was corrected, thank you for that, but your idea " you also cannot get nested table column name as done your example" is incorrect, it is possible and correct.
    – hmmftg
    Nov 12 at 7:02
















  • This will not work incase the column is of type varray and CURRENT OF C_EMP_CURSOR; has to be changed to the cursor name C_COMM_AMOUNT_CURSOR else you will get invalid name error. Your fast answer is really not correct :-)
    – XING
    Nov 11 at 9:29











  • Correct this as well WHERE C.COMM_MONTH = 'DEC'; there is no table with name c. Table alias in your example is E. And you also cannot get nested table column name as done your example. Please take your time before you share your solution,
    – XING
    Nov 11 at 9:35











  • @XING I have tested this code with nested table type and it worked, thanks for your cirrection
    – hmmftg
    Nov 11 at 10:44










  • @XING the code has been tested in Oracle 11g and it worked, you have helped with some syntax errors which was corrected, thank you for that, but your idea " you also cannot get nested table column name as done your example" is incorrect, it is possible and correct.
    – hmmftg
    Nov 12 at 7:02















This will not work incase the column is of type varray and CURRENT OF C_EMP_CURSOR; has to be changed to the cursor name C_COMM_AMOUNT_CURSOR else you will get invalid name error. Your fast answer is really not correct :-)
– XING
Nov 11 at 9:29





This will not work incase the column is of type varray and CURRENT OF C_EMP_CURSOR; has to be changed to the cursor name C_COMM_AMOUNT_CURSOR else you will get invalid name error. Your fast answer is really not correct :-)
– XING
Nov 11 at 9:29













Correct this as well WHERE C.COMM_MONTH = 'DEC'; there is no table with name c. Table alias in your example is E. And you also cannot get nested table column name as done your example. Please take your time before you share your solution,
– XING
Nov 11 at 9:35





Correct this as well WHERE C.COMM_MONTH = 'DEC'; there is no table with name c. Table alias in your example is E. And you also cannot get nested table column name as done your example. Please take your time before you share your solution,
– XING
Nov 11 at 9:35













@XING I have tested this code with nested table type and it worked, thanks for your cirrection
– hmmftg
Nov 11 at 10:44




@XING I have tested this code with nested table type and it worked, thanks for your cirrection
– hmmftg
Nov 11 at 10:44












@XING the code has been tested in Oracle 11g and it worked, you have helped with some syntax errors which was corrected, thank you for that, but your idea " you also cannot get nested table column name as done your example" is incorrect, it is possible and correct.
– hmmftg
Nov 12 at 7:02




@XING the code has been tested in Oracle 11g and it worked, you have helped with some syntax errors which was corrected, thank you for that, but your idea " you also cannot get nested table column name as done your example" is incorrect, it is possible and correct.
– hmmftg
Nov 12 at 7:02

















draft saved

draft discarded
















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid


  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.





Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


Please pay close attention to the following guidance:


  • Please be sure to answer the question. Provide details and share your research!

But avoid


  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53243827%2fusing-cursor-for-updating-nested-tables-pl-sql%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Use pre created SQLite database for Android project in kotlin

Darth Vader #20

Ondo