asked on 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;
/
* Oracle PL/SQLOracle Database
Last Comment
Swaminathan K
ASKER
Is it not possible to refer to the previous element in the same statement
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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;