whatever parameters i give when i run this procedure, it always says that my index needs to be lower. for example, if i give the parameters (1,1) it says that my index is 61 and it should be in the range of 1 to 60. i have been baffled at this for hours, please if anyone has any idea
both of these tables have two columns, the first one has id_produs and id_departament, the second one has id_produs and id_comanda. both tabes have single digit numbers inserted intoo both columns, yet nothing works. does anything look particularly wrong to you?
CREATE OR REPLACE PROCEDURE procedura_6
(v_produse_2 produs_departament.id_produs%type, v_produse_5 comanda_produs.id_produs%type )
IS
TYPE varray_produse_2 IS VARRAY(60) OF produs_departament.id_produs%type;
TYPE nested_table_produse_5 IS TABLE OF comanda_produs.id_produs%type;
varray_numar_dept varray_produse_2:=varray_produse_2();
nested_table_numar_com nested_table_produse_5:=nested_table_produse_5();
BEGIN
SELECT id_produs BULK COLLECT INTO varray_numar_dept FROM produs_departament;
SELECT id_produs BULK COLLECT INTO nested_table_numar_com FROM comanda_produs;
for l_index in varray_numar_dept.FIRST..varray_numar_dept.LAST
LOOP
IF (v_produse_2>varray_numar_dept(l_index)) THEN
DBMS_OUTPUT.PUT_LINE(varray_numar_dept(l_index));
END IF;
END LOOP;
for l_index in nested_table_numar_com.FIRST..nested_table_numar_com.LAST
LOOP
IF (v_produse_5>nested_table_numar_com(l_index)) THEN
DBMS_OUTPUT.PUT_LINE(nested_table_numar_com(l_index));
END IF;
END LOOP;
END procedura_6;
execute procedura_6(2,2);
begin
procedura_6(3,3);
end;
/
>Solution :
This is the culprit:
TYPE varray_produse_2 IS VARRAY(60) OF produs_departament.id_produs%type;
--
this
Apparently, select from the table returns more than 60 rows, whilst you reserved only 60 places for them. It ran OK until the limit has been reached. For the 61st row, it failed.
Enlarge it.