Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to update nested table records ??

901542Nov 22 2011 — edited Nov 23 2011
Hi, I am just starting to write anything in PL/SQL and having some difficulties with basic syntax. Thanks for any help in advance.

My problem is how to update collection (nested table of objects) with SQL statement. My nested table is not a column of regular table.

Example:
CREATE OR REPLACE TYPE tmpRec AS OBJECT(
Col1 INT,
Col2 INT
);
/

CREATE OR REPLACE TYPE tmpTable IS TABLE OF tmpRec;
/

DECLARE v tmpTable :=
tmpMBATable(
tmpRec(1,1),
tmpRec(2,2),
tmpRec(3,3),
);
BEGIN
--UPDATE TABLE(CAST(v AS tmpTable)) T SET T.Col2 = 1 WHERE T.Col1 =1;
--UPDATE TABLE(v) T SET T.Col2 = 12 WHERE T.Col1 =1;
--UPDATE (SELECT * FROM TABLE(v) )T SET T.Col2 = 12 WHERE T.Col =1;
END;
/

I am getting either
PL/SQL: ORA-22841: DML is not allowed on PL/SQL Collections
OR
PL/SQL: ORA-00903 Bad table name.

I found there is no problem when collection is a column of DB table (UPDATE TABLE(select collection_column from table) T SET T.Col2 = 12 WHERE T.Col1 =1;) but i want it to be just a collection without storing it in DB, is it possible ?

Please help.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 21 2011
Added on Nov 22 2011
6 comments
1,465 views