Home » SQL & PL/SQL » SQL & PL/SQL » Can I update contents of a collection as if its a Table ? (merged)
Can I update contents of a collection as if its a Table ? (merged) [message #376092] Tue, 16 December 2008 00:39 Go to next message
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 Go to previous message
Kevin Meade
Messages: 2101
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
Previous Topic: unable to send mail more then 100ids
Next Topic: NULL SELF ERROR
Goto Forum:
  


Current Time: Thu Dec 08 18:28:49 CST 2016

Total time taken to generate the page: 0.10586 seconds