pl/sql collection/object update
I have these declared at the schema level
create or replace type "myobj" as as object ( var1 varchar2(10), var2 varchar2(10) ); create or replace type memtable as table of myobj;
In my procedure, I have
mytable memtable := memtable(); for loop_index in 1 .. 1000000 Loop mytable.extend; mytable(loop_index) := myobj('blah', null); end loop;
That works fine...now if i want to select from the table, i could do
select * from table(mytable) where var1 = 'abc';
That works fine too.
Now I want to update var2 in all those million objects, how would I do it using update statement
update (select * from table(mytable)) set var2 = '123' where ....
That doesn't work...compiler is complaining with ORA-0904 invalid identifier at mytable
Any idea how I would do it? Appreciate any help. Thanks.
Maybe the DML must have one "real" table at least. Try this one:
CREATE GLOBAL TEMPORARY TABLE TT_memtable (val memtable) ON COMMIT PRESERVE ROWS;
then in your code:
insert into TT_memtable (val) values (mytable); update (select * from TT_memtable natural JOIN table(val)) set var2 = '123' where ....