Need help on plsql varray
Hi Team ,
iam running the below program on oracle varray . I need to print the current element , previous element in the dbms_output statement. But i get the below error when i run the program
Error starting at line 2 in command:
declare
type v_numvarr is varray(10) of number;
l_numvarr v_numvarr;
cnt simple_integer:=1;
begin
l_numvarr :=v_numvarr();
l_numvarr.extend;
l_numvarr(1):=10;
l_numvarr.extend;
l_numvarr(2):=20;
l_numvarr.extend(8);
l_numvarr(3):=30;
l_numvarr(4):=40;
l_numvarr(5):=50;
l_numvarr(6):=60;
l_numvarr(7):=70;
l_numvarr(8):=80;
l_numvarr(9):=90;
l_numvarr(10):=100;
-- l_numvarr(11):=110 this will cause an error , because we are accessing invalid index position , the maximum numbe is 10 , we are accessing 11
dbms_output.put_line ('The total size of the array is: '||l_numvarr.limit);
/*
for i in l_numvarr.first..l_numvarr
loop
dbms_output.put_line ('The element at index position ('||i||') is:' || l_numvarr(i));
end loop;
*/
l_numvarr.trim;
for i in l_numvarr.first..l_numvarr
loop
dbms_output.put_line ('The element at index position ('||i||') is:' || l_numvarr(i) ||l_numvarr(l_numvarr(l_nu
end loop;
/*
l_numvarr.trim(2);
for i in l_numvarr.first..l_numvarr
loop
dbms_output.put_line ('The element at index position ('||i||') is:' || l_numvarr(i));
end loop;*/
end;
Error report:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at line 36
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:
--- Program
declare
type v_numvarr is varray(10) of number;
l_numvarr v_numvarr;
cnt simple_integer:=1;
begin
l_numvarr :=v_numvarr();
l_numvarr.extend;
l_numvarr(1):=10;
l_numvarr.extend;
l_numvarr(2):=20;
l_numvarr.extend(8);
l_numvarr(3):=30;
l_numvarr(4):=40;
l_numvarr(5):=50;
l_numvarr(6):=60;
l_numvarr(7):=70;
l_numvarr(8):=80;
l_numvarr(9):=90;
l_numvarr(10):=100;
-- l_numvarr(11):=110 this will cause an error , because we are accessing invalid index position , the maximum numbe is 10 , we are accessing 11
dbms_output.put_line ('The total size of the array is: '||l_numvarr.limit);
/*
for i in l_numvarr.first..l_numvarr
loop
dbms_output.put_line ('The element at index position ('||i||') is:' || l_numvarr(i));
end loop;
*/
l_numvarr.trim;
for i in l_numvarr.first..l_numvarr
loop
dbms_output.put_line ('The element at index position ('||i||') is:' || l_numvarr(i) || ' Previous element :'|| l_numvarr(l_numvarr(l_numv
end loop;
/*
l_numvarr.trim(2);
for i in l_numvarr.first..l_numvarr
loop
dbms_output.put_line ('The element at index position ('||i||') is:' || l_numvarr(i));
end loop;*/
end;
/
iam running the below program on oracle varray . I need to print the current element , previous element in the dbms_output statement. But i get the below error when i run the program
Error starting at line 2 in command:
declare
type v_numvarr is varray(10) of number;
l_numvarr v_numvarr;
cnt simple_integer:=1;
begin
l_numvarr :=v_numvarr();
l_numvarr.extend;
l_numvarr(1):=10;
l_numvarr.extend;
l_numvarr(2):=20;
l_numvarr.extend(8);
l_numvarr(3):=30;
l_numvarr(4):=40;
l_numvarr(5):=50;
l_numvarr(6):=60;
l_numvarr(7):=70;
l_numvarr(8):=80;
l_numvarr(9):=90;
l_numvarr(10):=100;
-- l_numvarr(11):=110 this will cause an error , because we are accessing invalid index position , the maximum numbe is 10 , we are accessing 11
dbms_output.put_line ('The total size of the array is: '||l_numvarr.limit);
/*
for i in l_numvarr.first..l_numvarr
loop
dbms_output.put_line ('The element at index position ('||i||') is:' || l_numvarr(i));
end loop;
*/
l_numvarr.trim;
for i in l_numvarr.first..l_numvarr
loop
dbms_output.put_line ('The element at index position ('||i||') is:' || l_numvarr(i) ||l_numvarr(l_numvarr(l_nu
end loop;
/*
l_numvarr.trim(2);
for i in l_numvarr.first..l_numvarr
loop
dbms_output.put_line ('The element at index position ('||i||') is:' || l_numvarr(i));
end loop;*/
end;
Error report:
ORA-06502: PL/SQL: numeric or value error: NULL index table key value
ORA-06512: at line 36
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:
--- Program
declare
type v_numvarr is varray(10) of number;
l_numvarr v_numvarr;
cnt simple_integer:=1;
begin
l_numvarr :=v_numvarr();
l_numvarr.extend;
l_numvarr(1):=10;
l_numvarr.extend;
l_numvarr(2):=20;
l_numvarr.extend(8);
l_numvarr(3):=30;
l_numvarr(4):=40;
l_numvarr(5):=50;
l_numvarr(6):=60;
l_numvarr(7):=70;
l_numvarr(8):=80;
l_numvarr(9):=90;
l_numvarr(10):=100;
-- l_numvarr(11):=110 this will cause an error , because we are accessing invalid index position , the maximum numbe is 10 , we are accessing 11
dbms_output.put_line ('The total size of the array is: '||l_numvarr.limit);
/*
for i in l_numvarr.first..l_numvarr
loop
dbms_output.put_line ('The element at index position ('||i||') is:' || l_numvarr(i));
end loop;
*/
l_numvarr.trim;
for i in l_numvarr.first..l_numvarr
loop
dbms_output.put_line ('The element at index position ('||i||') is:' || l_numvarr(i) || ' Previous element :'|| l_numvarr(l_numvarr(l_numv
end loop;
/*
l_numvarr.trim(2);
for i in l_numvarr.first..l_numvarr
loop
dbms_output.put_line ('The element at index position ('||i||') is:' || l_numvarr(i));
end loop;*/
end;
/
ASKER
Is it not possible to refer to the previous element in the same statement
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot .
declare
type v_numvarr is varray(10) of number;
l_numvarr v_numvarr;
cnt simple_integer:=1;
n_prev_element number;
begin
l_numvarr :=v_numvarr();
l_numvarr.extend;
l_numvarr(1):=10;
l_numvarr.extend;
l_numvarr(2):=20;
l_numvarr.extend(8);
l_numvarr(3):=30;
l_numvarr(4):=40;
l_numvarr(5):=50;
l_numvarr(6):=60;
l_numvarr(7):=70;
l_numvarr(8):=80;
l_numvarr(9):=90;
l_numvarr(10):=100;
dbms_output.put_line ('The total size of the array is: '||l_numvarr.limit);
l_numvarr.trim;
for i in l_numvarr.first..l_numvarr.last
loop
dbms_output.put_line ('The element at index position ('||i||') is:' || l_numvarr(i)||' Previous element :'||l_numvarr.prior(i)||' is:' || n_prev_element);
n_prev_element:=l_numvarr(i);
end loop;
end;