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

Popular posts from this blog

sequelize.js - Sequelize group by with association includes id -

android - Robolectric "INTERNET permission is required" -

java - Android raising EPERM (Operation not permitted) when attempting to send UDP packet after network connection -