| Can I update contents of a collection as if its a Table ? (merged) [message #376092] |
Tue, 16 December 2008 00:39  |
raghu20
Messages: 2
Registered: December 2008
|
Junior Member |
|
|
Hi,
Is it possible to update contents of a collection treating it as a table (expecting cast to come in handy here) ?
Well, we have a rather complex piece of functionality, which we want to break into multiple simpler pieces. The idea is to fetch data in the first of those steps and keep into a collection. The collection is then subjected to other DML operations (basically filtering in different steps based on diff conditions).
I want to achieve something like this -
update table(cast(<collection_name> as <type_name)) t1
set t1.column_name = <whatever_value>
where ...
But I keep getting this compilation error - invalid table name, and PL/SQL: ORA-22905: cannot access rows from a non-nested table item
Conceptually, after reading about cast and collections, it should be allowed to perform standard DML operations on a collection.
However, being new to collections myself, I wonder if my understanding is actually correct. Has anyone tried something simiilar ?
please suggest
raghav..
|
|
|
|
| Re: Can I update contents of a collection as if its a Table ? (merged) [message #376294 is a reply to message #376092] |
Tue, 16 December 2008 18:14  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
I believe that DML commands are only valid against some kind of real database object. Thus they will not work on a variable, even if this is patterned after or retreived from an object.
Am hoping someone will prove me wrong, but here is a script and its execution that says it is currently illegal 10g1 and why.
Here is a link with some info:
Oracle Collections & TABLE Expression
create or replace type c_varchar2_4000 as table of varchar2(4000)
/
desc c_varchar2_4000
select 'abc' mystring from dual union all
select 'def' from dual union all
select 'xyz' from dual
/
declare
ns_v c_varchar2_4000;
begin
select cast(multiset(select * from (
select 'abc' from dual union all
select 'def' from dual union all
select 'xyz' from dual
)
) as c_varchar2_4000
)
into ns_v
from dual;
end;
/
begin
update (
select cast(multiset(select * from (
select 'abc' from dual union all
select 'def' from dual union all
select 'xyz' from dual
)
) as c_varchar2_4000
)
from dual
) set
column_value = '123'
where column_value = 'abc'
;
end;
/
declare
ns_v c_varchar2_4000;
begin
select cast(multiset(select * from (
select 'abc' from dual union all
select 'def' from dual union all
select 'xyz' from dual
)
) as c_varchar2_4000
)
into ns_v
from dual;
update (
select * from table(cast(ns_v as c_varchar2_4000))
) set
column_value = '123'
where column_value = 'abc'
;
end;
/
create table temp1
(
ns1 c_varchar2_4000
)
nested table ns1 store as ns1_table
/
insert into temp1 values (( select cast(multiset(select * from (
select 'abc' from dual union all
select 'def' from dual union all
select 'xyz' from dual
)
) as c_varchar2_4000
)
from dual
)
)
/
select * from temp1;
update table(select ns1 from temp1) set
column_value = '123'
where column_value = 'abc'
/
select * from temp1;
SQL> desc c_varchar2_4000
c_varchar2_4000 TABLE OF VARCHAR2(4000)
SQL>
SQL>
SQL> select 'abc' mystring from dual union all
2 select 'def' from dual union all
3 select 'xyz' from dual
4 /
MYS
---
abc
def
xyz
3 rows selected.
SQL> declare
2 ns_v c_varchar2_4000;
3 begin
4 select cast(multiset(select * from (
5 select 'abc' from dual union all
6 select 'def' from dual union all
7 select 'xyz' from dual
8 )
9 ) as c_varchar2_4000
10 )
11 into ns_v
12 from dual;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL> begin
2 update (
3 select cast(multiset(select * from (
4 select 'abc' from dual union all
5 select 'def' from dual union all
6 select 'xyz' from dual
7 )
8 ) as c_varchar2_4000
9 )
10 from dual
11 ) set
12 column_value = '123'
13 where column_value = 'abc'
14 ;
15 end;
16 /
update (
*
ERROR at line 2:
ORA-06550: line 2, column 4:
PL/SQL: ORA-01732: data manipulation operation not legal on this view
ORA-06550: line 2, column 4:
PL/SQL: SQL Statement ignored
SQL> declare
2 ns_v c_varchar2_4000;
3 begin
4 select cast(multiset(select * from (
5 select 'abc' from dual union all
6 select 'def' from dual union all
7 select 'xyz' from dual
8 )
9 ) as c_varchar2_4000
10 )
11 into ns_v
12 from dual;
13 update (
14 select * from table(cast(ns_v as c_varchar2_4000))
15 ) set
16 column_value = '123'
17 where column_value = 'abc'
18 ;
19 end;
20 /
declare
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
ORA-06512: at line 13
SQL> create table temp1
2 (
3 ns1 c_varchar2_4000
4 )
5 nested table ns1 store as ns1_table
6 /
Table created.
SQL> insert into temp1 values (( select cast(multiset(select * from (
2 select 'abc' from dual union all
3 select 'def' from dual union all
4 select 'xyz' from dual
5 )
6 ) as c_varchar2_4000
7 )
8 from dual
9 )
10 )
11 /
1 row created.
SQL> select * from temp1;
NS1
------------------------------------------------------------------------------------------------------
C_VARCHAR2_4000('abc', 'def', 'xyz')
1 row selected.
SQL> update table(select ns1 from temp1) set
2 column_value = '123'
3 where column_value = 'abc'
4 /
1 row updated.
SQL>
SQL> select * from temp1;
NS1
------------------------------------------------------------------------------------------------------
C_VARCHAR2_4000('123', 'def', 'xyz')
1 row selected.
SQL>
Seems as long as it is a real nested table, you can update it with DML. But not variables or transiant tables. Interesting idea. Maybe someday. Or maybe someone knows better than I.
Good luck, Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
