2 d arrays????

Hi All,

I am trying to construct a variable of, what would be in C++ called a 2 dimensional array.

I need to be able to store maybe the name and age of many people in an area (memory) temp tablespace?? And then manipulate those.

I have looked into using VARRAY and nested tables. I have written a little plsql program to try and get two values from a table and write them to the screen as a test. I can't seem to do this by referencing what I think is the subscript and element of that array element.

So for example:


TYPE p_emp_type IS TABLE OF emp%ROWTYPE;
p_emp_tab p_emp_type; -- variable declaration--------------------------
cursor c1 is
select emp_id, age
from emp
where emp_id in (888888, 3333);
exit_count number := 0;
BEGIN
dbms_output.put_line('Started : ');
for i in c1
loop
p_emp_tab(i).emp_id := i.emp_id;
p_emp_tab(i).age := i.age;
end loop;
dbms_output.put_line('finished loop : ');
loop
exit_count := exit_count + 1;
dbms_output.put_line('empid: ' ||p_emp_tab(exit_count).emp_id || ' age: ' || p_emp_tab(exit_count).age);
if exit_count > 10 then
exit;
end if;
end loop;
END;

I am getting errors to the effect of

ERROR at line 25:
ORA-06550: line 25, column 21

Can anyone help please?????

Even if this is not correct can anyone help me to achieve what I am trying please?

Linked list or 2 dimensional array???

Thanks very much in advance.

Hi,

Your procedure has been partially modified. check if it will
do.

First crete a Object Type

SQL> create type empstr as object
2 (empid number(6),
3 age number(4));
4 /

Type created.


-- follwing is pl/sql block

declare
-- Create a type for above empstr object
Type p_emp_type is table of empstr ;

-- declare a variable on type and initilize.
p_emp_tab p_emp_type := p_emp_type(empstr(10,20));

cursor c1 is
select
emp_id, age
from
emp
where
emp_id in (888888, 3333);
exit_count number := 0;

-- extra variable added for counter in "for loop"
ctr number := 1;
BEGIN
dbms_output.put_line('Started : ');
for i in c1
loop
-- counter variable "ctr" is used here
p_emp_tab(ctr).emp_id := i.emp_id;
p_emp_tab(ctr).emp_id := i.age;
ctr := ctr + 1;
end loop;

dbms_output.put_line('finished loop : ');
loop
exit_count := exit_count + 1;
dbms_output.put_line('empid: ' ||p_emp_tab(exit_count).emp_id || ' age: ' || p_emp_tab(exit_count).age);

-- old condition was exit_count > 10
if exit_count >= ctr-1 then
exit;
end if;
end loop;
END;


run this procedure you will find output like this

Started :
finished loop :
empid: 10 age: 20

PL/SQL procedure successfully completed.



Thanks
P. Soni