Hi,
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE 11.2.0.1.0 Production"
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
I am trying to update a value in a nested collection but couldn't. Can some one please help me out?
Here is the sample code
create or replace type col_object IS object (column1 varchar2(10),column2 varchar2(10));
Create or replace type COL_NT is TABLE OF col_object;
declare
v_col_type COL_NT :=COL_NT();
v_cnt number;
Type l_array is table of varchar2(10);
Col2_list l_array;
Begin
v_col_type.EXTEND(3);
v_col_type(v_col_type.LAST) := NEW col_object(NULL,NULL);
v_col_type(v_col_type.LAST) :=col_object('Mike','Test1');
v_col_type(v_col_type.LAST) :=col_object('Mike','Test2');
v_col_type(v_col_type.LAST) :=col_object('Mike','Test3');
select column2 bulk collect into Col2_list from TABLE(v_col_type);
-- I know i could use COUNT method but want to try this way.
DBMS_OUTPUT.PUT_LINE('Count:' ||Col2_list.count );
END;
/
I get the output:
Count:3
I want to update the column1 value where column2='Test3';
UPDATE TABLE (v_col_type) SET column1 = 'Joe' WHERE column2 = 'Test3';
And also i wanted to know is there a way to delete both coulmn1 and column2 values where column2='Test3';
Thanks,
sg