Why I cant delete element from collection (array) PLSQL

Question

At this handler I delete NULL element:

if p_arr(i) is null then
    p_arr.delete(p_arr(i));
end if;

but null element still in collection. How can I correct this? The task is to sort elements in collection, but the condition is that there can be NULL in collection. I should delete them.

declare   
p_arr dbms_sql.Number_Table;   
i pls_integer;  

procedure do_sort(p_arr in out dbms_sql.Number_Table, p_asc in boolean 
default null, p_nulls_last in boolean default null) is   
x pls_integer;  
p_temp number;

begin  

for i in -1..p_arr.COUNT - 2 

loop   
  if p_arr(i) is null then
    p_arr.delete(p_arr(i));
  end if;        

end loop;  

end; 

begin   
p_arr(-1) := 0;   
p_arr(0) := -2;   
p_arr(1) := 10.1;   
p_arr(2) := null;    
p_arr(3) := 10.1;   
p_arr(4) := -1;  

do_sort(p_arr); 

i := p_arr.first;   

while i is not null loop   
  dbms_output.put_line('arr('||i||') = '||nvl(to_char(p_arr(i)), 
'null')||';');   
i := p_arr.next(i);   
end loop;   

end;

Kaushik Nayak · Accepted Answer · 2018-10-12 14:23:04Z

2

That's because you are passing the element itself as the argument to the delete, rather than the index

p_arr.delete(p_arr(i)) is equivalent to p_arr.delete(NULL) - So it simply doesn't delete anything.

So, change it to p_arr.DELETE(i) it will work.

answered Oct 12, 2018 at 14:23

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why I cant delete element from collection (array) PLSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related