How can I use MOD function within WHILE LOOP with PLS_INTEGER data type?

Question

I'm trying to print on screen odd values of an associative array using a simple "WHILE LOOP" with MOD condition. Is it possible? I know that PLS_INTEGER only accept not decimal values (like int datatype on Java). So... I tried with a NUMBER counter but I receive the same results. How Can I resolve it? . Thanks

SET SERVEROUTPUT ON

DECLARE
  TYPE type_test IS TABLE OF VARCHAR2(45)
       INDEX BY PLS_INTEGER;
  t_test_5 type_test;

  v_counter_1 PLS_INTEGER;
  v_counter_2 NUMBER;
BEGIN                      
  t_test_5(1)  := 'Test1';
  t_test_5(2)  := 'Test2';
  t_test_5(3)  := 'Test3';
  t_test_5(4)  := 'Test4';
  t_test_5(5)  := 'Test5';
  t_test_5(6)  := 'Test6';
  t_test_5(7)  := 'Test7';
  t_test_5(8)  := 'Test8';
  t_test_5(9)  := 'Test9';
  t_test_5(10) := 'Test10';

  DBMS_OUTPUT.PUT_LINE('PLS_INTEGER COUNTER TEST');
  v_counter_1 := t_test_5.FIRST;
  WHILE MOD(v_counter_1, 2) <> 0
  LOOP
        DBMS_OUTPUT.PUT_LINE(t_test_5(v_counter_1));

        v_counter_1 := t_test_5.NEXT(v_counter_1);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('  ');
  DBMS_OUTPUT.PUT_LINE('NUMBER COUNTER TEST');
  v_counter_2 := t_test_5.FIRST;
  WHILE MOD(v_counter_2, 2) <> 0
  LOOP
        DBMS_OUTPUT.PUT_LINE(t_test_5(v_counter_2));

        v_counter_2 := t_test_5.NEXT(v_counter_2);
  END LOOP;
END;

I want to retrieve on screen the values 1, 3, 5, 7, 9 but in both situations I only retrieve value 1:

Procedimiento PL/SQL terminado correctamente.
PLS_INTEGER COUNTER TEST
Test1

NUMBER COUNTER TEST
Test1

Apart from your question of "how to use `MOD()` for this assignment", if you want to print only odd-indexed values you should loop on a counter `j` and print `array_name(2 * j + 1)`. This is even more important if you only want to print, say, `array_name(k)` when `mod(k, 1000) = 1` - don't write it that way, write it with `...(1000 * j + 1)`, it will run *much* faster. — , Jan 11 '17 at 15:53
@mathguy That would work for a normal array but an associative array can be sparse and you are not guaranteed to have an element at every even index. — MT0, Jan 11 '17 at 15:57
@MT0 - Right. With that said, I see that quite often, even when the data is dense - and I cringe. And regardless of the OP saying "Exactly!" in the comment above, the data he offered in the original post didn't look sparse to me. — , Jan 11 '17 at 16:17

Aleksej · Accepted Answer · 2017-01-11T15:51:50.690

The issue is not in the type of your variables, but in the fact that your loops end at the first row that does not match MOD(v_counter_1, 2) <> 0, thus not scanning all the rows.

What you need is not a loop ending when MOD(v_counter_1, 2) = 0, but a loop that scans all the rows, simply printing the values for the only rows that match your criteria:

DECLARE
  TYPE type_test IS TABLE OF VARCHAR2(45)
       INDEX BY PLS_INTEGER;
  t_test_5 type_test;

  v_counter_1 PLS_INTEGER;
  v_counter_2 NUMBER;
BEGIN                      
  t_test_5(1)  := 'Test1';
  t_test_5(2)  := 'Test2';
  t_test_5(3)  := 'Test3';
  t_test_5(4)  := 'Test4';
  t_test_5(5)  := 'Test5';
  t_test_5(6)  := 'Test6';
  t_test_5(7)  := 'Test7';
  t_test_5(8)  := 'Test8';
  t_test_5(9)  := 'Test9';
  t_test_5(10) := 'Test10';

  DBMS_OUTPUT.PUT_LINE('PLS_INTEGER COUNTER TEST');
  v_counter_1 := t_test_5.FIRST;
  WHILE v_counter_1 is not null
  LOOP
        if mod(v_counter_1, 2) != 0 then
            DBMS_OUTPUT.PUT_LINE(t_test_5(v_counter_1));
        end if;
        v_counter_1 := t_test_5.NEXT(v_counter_1);
  END LOOP;
END;

the result:

PLS_INTEGER COUNTER TEST
Test1
Test3
Test5
Test7
Test9

Beat me to it, but I would add an explanation of the issue: that the `WHILE` loop was ending on the first run due to hitting its exit condition. — gmiley, Jan 11 '17 at 15:51
@gmiley Thanks for your answer. Now I understand that WHILE condition Is the EXIT condition, similar to simple Loop clause EXIT WHEN... Thanks both for your help!!! — , Jan 11 '17 at 15:55

MT0 · Answer 2 · 2017-01-11T16:02:47.980

Your while loop ends as soon as the counter's value is an even number - so as soon as it reaches 2 then loop will end. What you want is to loop through all the values but skip the even values:

WHILE ( v_counter_1 IS NOT NULL )
LOOP
  IF MOD( v_counter_1, 2 ) = 0 THEN
    v_counter_1 := t_test_5.NEXT(v_counter_1);
    CONTINUE;
  END IF;
  DBMS_OUTPUT.PUT_LINE(t_test_5(v_counter_1));
  v_counter_1 := t_test_5.NEXT(v_counter_1);
END LOOP;

If you are not going to have a sparse array then you do not need to use an associative array:

DECLARE
  TYPE type_test IS TABLE OF VARCHAR2(45);
  t type_test := type_test( 'Test1', 'Test2', 'Test3', 'Test4', 'Test5', 'Test6' );
BEGIN
  FOR i = 1 .. t.COUNT / 2 LOOP
    DBMS_OUTPUT.PUT_LINE(t(2*i-1));
  END LOOP;
END;
/

How can I use MOD function within WHILE LOOP with PLS_INTEGER data type?

2 Answers2