pl/sql remove element from array

Question

I need to remove element from array. I have tried to use array.delete(n) function, but it deletes all elements from identifier n. How to just delete exact element n ?

For example if array is 1 2 3 4 5, and n = 3, after delete it should look like following 1 2 4 5.

My code so far :

DECLARE
  /* declare type array */ 
  TYPE number_index_by_number IS TABLE OF number INDEX BY binary_integer;

  v_n NUMBER := &sv_n;
  v_m NUMBER := &sv_m;
  v_min Number; 
  v_tmp Number;
  v_array number_index_by_number; 
  v_sorted_array number_index_by_number;
begin
  for i in 1..v_n 
  loop
    v_array(i) := dbms_random.value(1,1000);
  end loop;
  for j in v_array.first..v_array.last
  loop
    DBMS_OUTPUT.put_line('v_array('||j||') :'||v_array(j));
  end loop;
  <<i_loop>> for i in 1..v_m
  loop
    /*set first array value to variable min*/
    v_min := v_array(1);
    v_tmp := 1;
    <<j_loop>> for j in v_array.first..v_array.last
    loop
      DBMS_OUTPUT.put_line('v_array('||j||') :'||v_array(j));
      if (v_min > v_array(j)) THEN
        begin
          v_min := v_array(j);
          v_tmp := j;
          DBMS_OUTPUT.put_line(j);
        end;
      end if;
    end loop;
    /*problem is in at this line*/
    v_array.delete(v_tmp);
    v_sorted_array(i) := v_min; 
  end loop;
  for i in v_sorted_array.first..v_sorted_array.last
  loop
    DBMS_OUTPUT.put_line('v_sorted_array('||i||') :'||v_sorted_array(i));
  end loop;
end;

what symptoms do you have of v_array being deleted all at once? — Sebas
– Sebas, Commented Mar 9, 2013 at 19:08
it doesn `t delete all at once. It works like for example. If declared array is 1 2 3 4 5, and n = 2, after array.delete(n) array contains only two elements. 1 and 2 — kuldarim
– kuldarim, Commented Mar 9, 2013 at 19:13
but it kind of makes sense regarding to your algorithm. The question is when is it happening? I mean, just open an anonymous block and try it, you'll see it works fine. I think your algorithm is the problem. docs.oracle.com/cd/B19306_01/appdev.102/b14261/… — Sebas
– Sebas, Commented Mar 9, 2013 at 19:20

Luke Woodward · Accepted Answer · 2013-03-09 20:06:44Z

4

I cannot reproduce any of the behaviour you describe. I could not get the delete collection method to do anything other than what it is documented to do.

However, there are a few errors in your code that could do with being tidied up.

Firstly, I should point out if you delete an element with key 3 from a PL/SQL associative array, there is then nothing with key 3 in the array. The remaining values don't 'shuffle' down to fill the gap. If there was an element with key 4 before the delete, the same element will still have key 4 afterwards. As a result, if you delete element j from a PL/SQL associative array v_array and then attempt to get v_array(j), you will get a 'no data found' error. You should check to see whether the element exists, using v_array.exists(j), before attempting to get a nonexistent element.

Secondly, the element with index 1 may get deleted before the last iteration of the outer loop. If this happens, v_array(1) will fail with a 'no data found' error. It would be better to assign NULL to v_min and v_tmp at the start of the loop, and assign to them during the loop if v_min is NULL or greater than v_array(j).

Finally, it seems your code returns the v_m smallest numbers from v_n. It would be worth verifying that v_m is less than or equal to v_n, as otherwise this doesn't make sense.

answered Mar 9, 2013 at 20:06

Luke Woodward

65.4k16 gold badges94 silver badges108 bronze badges

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

1 Comment

kuldarim Over a year ago

Thanks Luke ;) Yeah, I agree with you, that v_m and v_n values must be checked as you say, but this code is only for testing purpose, because I `m learning pl/sql. But thanks for your good advice, now I understand there was my problem ;)

artie · Accepted Answer · 2015-03-19 14:01:02Z

-1

I'm affraid you cannot use a built-in method like this. Instead of you shoud create a temp array to collect the elements prior to and afterwards the selected one from the original array, and return the temp array.

answered Mar 19, 2015 at 14:01

artie

1

Collectives™ on Stack Overflow

pl/sql remove element from array

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related