Re: 9i: Nested table DML problem

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 18 Oct 2001 19:01:28 -0700
Message-ID: <9qo1ho0uh4@drn.newsguy.com>

In article <9qnuji$ipk$2_at_news.doit.wisc.edu>, "Gerard says...
>
>We've just installed 9i on a Win2K server and have been playing around with
>it. One problem arose in some code we'd used to test the object features in
>8i. The following code snippet, taken from a object type body, worked in 8i
>but does not compile in 9i:
>
> member procedure removeReference(groupID varchar2 := '', obj ref PObject :=
>null)
> is
> begin
> if objectReferences is not null then
> delete from table(objectReferences) r
> where (removeReference.groupID is null or r.groupID =
>removeReference.groupID)
> and (obj is null or r.objectRef = obj);
>
> if objectReferences.count = 0 then
> objectReferences := null;
> end if;
> end if;
> end;
>
>"objectReferences" is an attribute of "this", the implicit object instance
>variable, and is a nested table type. Formally, the TABLE operator could
>take a local nested table variable as an argument; now the PL/SQL compiler
>complains with a "invalid table name" error.
>
>Can anyone who knows better than I suggest a work-around (to be able to
>manipulate a local nested table using DELETE)?
>
>TIA,
>g
>
>--
>Gerard Averill
>gaverill<at>chsra<dot>wisc<dot>edu
>
>
>

of course, contact support (interesting syntax, never thought of doing it that way)

here is the way I've always thought of it (and it works). SELECT into the object the ANTI delete. Instead of delete where x = n, select where x <> n into the collection:

ops$tkyte_at_ORA9I.WORLD> create or replace type myArraytype as table of number   2 /

Type created.

ops$tkyte_at_ORA9I.WORLD>
ops$tkyte_at_ORA9I.WORLD> create or replace type myObjectType   2 as object
  3 ( x int,
  4 y myArrayType,
  5 member procedure removeReference( removeId in number )   6 )
  7 /

Type created.

ops$tkyte_at_ORA9I.WORLD>
ops$tkyte_at_ORA9I.WORLD> create or replace type body myObjectType   2 as
  3
  4 member procedure removeReference( removeId in number )   5 as
  6 begin

  7      if ( y is not null AND removeId is not null )
  8      then
  9          select cast( multiset(
 10          select column_value
 11            from ( select *
 12                     from table (cast ( y as myArrayType ) )
 13                    where column_value <> removeId ) )
 14                     as myArrayType ) into y
 15          from dual;
 16  
 17      end if;

 18 end;
 19
 20 end;
 21 /

Type body created.

ops$tkyte_at_ORA9I.WORLD> 
ops$tkyte_at_ORA9I.WORLD> 
ops$tkyte_at_ORA9I.WORLD> declare
  2      a myObjectType;
  3  begin
  4      a := myObjectType( 1, myArrayType( 1,2,3,4 ) );
  5  
  6      a.removeReference( 2 );
  7  
  8      for i in 1 .. a.y.count
  9      loop
 10          dbms_output.put_line( 'a(' || i || ' ) = ' || a.y(i) );
 11      end loop;

 12 end;
 13 /

a(1 ) = 1
a(2 ) = 3
a(3 ) = 4

PL/SQL procedure successfully completed.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Thu Oct 18 2001 - 21:01:28 CDT