Thanks, Thomas. I will try that way instead.
--
Gerard Averill
gaverill<at>chsra<dot>wisc<dot>edu
"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message
news:9qo1ho0uh4_at_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 Tue Oct 23 2001 - 23:56:58 CDT
