oracle - PL-SQL Delete item from a table of Objects -
i'm trying delete item variable of type table of objects:
create or replace type "t_attributepage_attributelist" table of o_attributepage_attributelist; create or replace type "o_attributepage_attributelist" object ( wizattreditid number, internalindex number, dimensionobjectid number, attributename varchar2(50), attributelabel varchar2(50), attributetype number, attributelength varchar2(50), mandatoryattribute number, readonly number, name varchar2(2000), num number, ismodified number, colour number);
i'm itterating through list of objects, checked , ls_attr_list.count
16 , try delete 1 item when criteria met error :
ora-01403: no data found
which raised @ line : ls_attr_list.delete(i);
for in 1..ls_attr_list.count loop begin if ls_attr_list(i).attributename = 'protection_role' ls_attr_list.delete(i); end if; exception when others writelog(sqlerrm,'error'); end; end loop;
the ls_attr_list initialize input parameter:
procedure attribinit(geninfo in out o_geninfo, pageinfo in out o_attributepage_pageinfo, attributelist in out t_attributepage_attributelist, enumlist in out t_attributepage_enumlist) ls_attr_list := attributelist;
problem when itterating on list after deleting 1 item
i := attributelist.first; loop if attributelist(i).attributename = 'protection_role' attributelist.delete(i); end if; exit when = attributelist.last; := attributelist.next(i); end loop; --second for in 1..attributelist.count loop
--no data found when reaches index of previous deleted item
writelog(attributelist(i).attributename,'attributename'); end loop;
i'm doing wrong, ideas what??
for in 1 .. ls_attr_list.count loop
will cause exceptions/errors when have deleted element collection. each time delete element creates gap in collection , next time try , run procedure gap , throw ora-01403: no data found
.
instead need loop between i := ls_attr_list.first
, ls_attr_list.last
, use i := ls_attr_list.next(i)
next index.
a simplified working example is:
create type varchar2_table table of varchar2(20); / declare vals varchar2_table := varchar2_table( 'a', 'b', 'c', 'd', 'e', 'f' ); procedure del_val ( v in out varchar2_table, x in varchar2 ) int; begin if v null or v empty return; end if; := v.first; loop if v(i) = x dbms_output.put_line(i); v.delete(i); end if; exit when = v.last; := v.next(i); end loop; end; begin del_val( vals, 'b' ); del_val( vals, 'e' ); del_val( vals, 'a' ); end; /
however using procedure:
procedure del_val ( v in out varchar2_table, x in varchar2 ) begin in 1 .. v.count loop if v(i) = x dbms_output.put_line(i); v.delete(i); end if; end loop; end;
would have caused ora-01403: no data found
when procedure called second time.
Comments
Post a Comment