-
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.
Steve
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|