Yes, I didn't catch that part, my bad. You need to have the nested table "as part of" a regular database table to perform DML on it. IOW as a column in a table. Stefan On Tue, Apr 29, 2008 at 8:57 AM, William Robertson < william@xxxxxxxxxxxxxxxxxxxx> wrote: > It's an array variable in PL/SQL, not a database table. Although it can be > cast to appear as a relational table for querying, you use the PL/SQL > assignment, bulk collect etc syntax to set values. > > btw you don't need the explicit CAST in recent Oracle versions - > TABLE(collection_name) will generally do it. > > > -----Original message----- > From: Deepak Sharma > Date: 29/4/08 06:55 > > > If I have a PLSQL "my_array" table defined as below: > > > > create or replace type my_rec as object ( > > emp_id number, > > emp_name varchar2 > > ) > > / > > > > create or replace type my_array as table of my_rec > > / > > > > I can select from it w/o problem, even using a WHERE clause : > > > > my_list my_array; > > > > FOR c1 IN ( > > SELECT emp_name > > FROM TABLE( CAST( my_list AS my_array)) > > WHERE emp_id = 999 > > ) > > LOOP > > DBMS_OUTPUT.PUT_LINE( c1.emp_name ); > > END LOOP; > > > > > > But, is there a way to UPDATE a particular row of that array (just as a > > Table) ? > > > > Say, I want to update and set emp_name = UPPER(emp_name), for this > > entire array, how will I do it w/o having to loop across each element? > > And, #2,, how can I update just specific row(s), say, I want to update > > emp_name = 'UPPER(emp_name), where emp_id = 123, only. > > > > > > Any ideas? > > > > > > Thanks, > > Deepak > > > > > > > > -- > //www.freelists.org/webpage/oracle-l > > > -- ========================= Stefan P Knecht Senior Consultant Infrastructure Managed Services Trivadis AG Europa-Strasse 5 CH-8152 Glattbrugg Phone +41-44-808 70 20 Fax +41-808 70 12 Mobile +41-79-571 36 27 stefan.knecht@xxxxxxxxxxxx http://www.trivadis.com OCP 9i/10g SCSA SCNA =========================