Re: Update of the object table

Ah, the table function TABLE() can not be the target of update, insert or delete. If you want to transform the data you will have to do it when you are populating the collection or you will have to look at the collection element one by one and decide what to do after it has been populated.

SQL> select c1 from test2;

C1

--
a
b
c
e
K
P
G
a
a
a
a

C1
--
a
a
G
G

15 rows selected.

SQL> declare
  2  TYPE nt_typ is table of test2.c1%type;
  3  tmp_typ nt_typ;
  4  cursor cur1 is select c1 from test2;
  5  begin
  6  open cur1;
  7  fetch cur1 BULK COLLECT INTO tmp_typ;
  8  close cur1;
  9  for i in tmp_typ.FIRST..tmp_typ.LAST loop
 10    tmp_typ(i) := tmp_typ(i)||'*';
 11    DBMS_OUTPUT.PUT_LINE(tmp_typ(i));
 12  end loop;
 13  end;
 14  /
a*
b*
c*
e*
K*
P*
G*
a*
a*
a*
a*
a*
a*
G*
G*

PL/SQL procedure successfully completed.





alex_at_sinoma.com (Alex Vilner) wrote in message news:<22e9f6e0.0208220403.3573c77f_at_posting.google.com>...


> Our case is a bit different -- there is NO physical object table.

> The only table that needs to be updated is the table of object type (a

> memory table), so the construct of the type:

> UPDATE TABLE(SELECT * FROM aMemoryTab) A

> SET A.N1 = 0;

> 

> does not work -- I get the following error message:

> PL/SQL: SQL Statement ignored

> PLS-00201: identifier 'AMEMORYTAB' must be declared

> 

> It is the memory table that needs to be updated...

> Any suggestions?

> 

> Thank you!

> 

> alex_at_sinoma.com (Alex Vilner) wrote in message news:<22e9f6e0.0208211305.3558fc28_at_posting.google.com>...

> > Hello!

> > 

> > I have the following structures defined:

> > CREATE OR REPLACE TYPE TEST_TYPE AS OBJECT

> > (

> >       N1               NUMBER (5) 

> >      ,C1                  VARCHAR2(9) 

> > )

> > /

> > 

> > CREATE OR REPLACE TYPE TEST_TYPE_TABLE AS TABLE OF TEST_TYPE

> > /

> > 

> > In the stored proc, I declare and populate the object table:

> > 

> > lMemoryTab	  TEST_TYPE_TABLE;

> > 	

> > CURSOR lCursor IS

> > SELECT ...

> > FROM YYY;

> > 	

> > OPEN lCursor;

> > 		

> > FETCH lCursor BULK COLLECT 

> > INTO lMemoryTab;

> > 

> > I also need to update the table -- as if it was a regular table. Since

> > it has "columns", I can do a SELECT:

> > SELECT * FROM TABLE( CAST( lMemoryTab AS TEST_TYPE_TABLE) );

> > 

> > But can I do the UPDATE with a WHERE Clause, for example? I do not

> > need the update that references the specific row in this memory table,

> > but rather does a SQL-like update.

> > 

> > Thank you!