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.

Answers


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 ....

Need Your Help

Error while attempting to connect to an Oracle 12c database using eclipse

eclipse oracle12c

I am trying to connect to an oracle 12c database using the eclipse IDE but I get the error "Ping Failed!" every time I try to test the connection. I have tried to disable the windows firewall as s...

putting information nt working

php html mysql sql

I have a "racing" website where I am recording racers results, but I am trying to enter their results in all at once. shown below is the code