Need help on plsql varray
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;
/
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;
This is from documentation (https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/plsql-collections-and-records.html#GUID-FB1216B3-2DC3-48D6-BCEC-243F995BFB09):
PRIOR returns the index of the preceding existing element of the collection, if one exists. Otherwise, PRIOR returns NULL.
So it means that it does not return value of element.
In your code you want value of element (LOOP through a collection), so it ought to be done as I have posted.
Seems like you are learning about collections. I don't think I've ever used VArrays. I prefer associative arrays or nested tables.
Yes, prior returns the index. Just use that to reference the value. No need for the additional variable.
declare
type v_numvarr is varray(10) of number;
l_numvarr v_numvarr;
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:' || case when l_numvarr.prior(i) is not null then l_numvarr(l_numvarr.prior(i)) end);
end loop;
end;
/