Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

using index by varchar confusion with pl/sql collections

635179Oct 16 2008 — edited Oct 16 2008
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
This post has been answered by dask99 on Oct 16 2008
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 13 2008
Added on Oct 16 2008
5 comments
3,804 views