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
