Hi, i'm attempting to learn the mechanics behind collections and have been doing ok so far until i came across trying to use the index by varchar.
I'm using the following code:-
declare
type address_lines_tab is table of varchar2(50)
index by pls_integer;
type address_tab is table of address_lines_tab
index by varchar(40);
type student_rec is record(
first varchar2(40),
last varchar2(40),
address address_tab
);
type student_tab is table of student_rec
index by pls_integer;
student student_tab;
i number := 0;
v varchar2(20);
x number := 0;
begin
student(1).first := 'mike';
student(1).last := 'jones';
student(1).address('home')(1) := 'the manor';
student(1).address('home')(2) := 'london';
student(1).address('home')(3) := 'w12 4kf';
student(1).address('term')(1) := '5 university';
student(1).address('term')(2) := 'plymouth';
student(1).address('term')(3) := 'pl22da';
student(2).first := 'helen';
student(2).last := 'roots';
student(2).address('home')(1) := '1 little lane';
student(2).address('home')(2) := 'hull';
student(2).address('home')(3) := 'h45 4fd';
student(2).address('term')(1) := '3 university';
student(2).address('term')(2) := 'plymouth';
student(2).address('term')(3) := 'pl22da';
for student_no in student.first .. student.last
loop
dbms_output.put_line( 'STUDNET NAME:'
|| student(student_no).first
|| ' '
|| student(student_no).last);
for add_type in
student(student_no).address.first .. student(student_no).address.last
loop
dbms_output.put_line(student(student_no).address);
for add_line in
student(student_no).address(add_type).first .. student(student_no).address(add_type).last
loop
dbms_output.put_line(student(student_no).address(add_type)(add_line));
end loop;
end loop;
end loop;
end;
the problem i'm having is in the second loop when i'm trying to display the 2 different types of address (ie home and term).
because its indexed by a varchar i'm getting the following error:-
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
How do i get around this, when looping through associative arrays which are indexed by varchar?
many thanks
this is the output i would expect to see:-
STUDNET NAME:mike jones
HOME
the manor
london
w12 4kf
TERM
5 university
plymouth
pl22da
STUDNET NAME:helen roots
HOME
1 little lane
hull
h45 4fd
TERM
3 university
plymouth
pl22da