| Varray of object type [message #462368] |
Wed, 23 June 2010 13:36  |
SamuelJk
Messages: 25
Registered: August 2007
|
Junior Member |
|
|
Hello,
I want to pass varray of object as out parameter (more than 1 ), Tested the below code getting error. Please provide suggession to resolve this issue.
Note : I want to get value from table and assign it to varray object and pass varray as out parameter.
Here below the code I tested.
create type emp_type as object (
emp_no number,
emp_name
);
create type emp is varray(10) of emp_type;
declare
v_emp emp:= emp(emp_type(10,'Name1'));
begin
dbms_output.put_line( v_emp.count);
end;
output : 1
declare
v_emp emp:= emp(emp_type(10,'Name1'));
begin
v_emp.extend;
v_emp(v_emp.last):=emp(emp_type(20,'Name2'));
dbms_output.put_line( v_emp.count);
end;
Getting error while above code.
Regards
Samuel
|
|
|
|
|
|
| Re: Varray of object type [message #462370 is a reply to message #462369] |
Wed, 23 June 2010 14:01  |
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
You want to add a second element to varray, right? so why are you trying to add a varray? Change:
> v_emp(v_emp.last):=emp(emp_type(20,'Name2'));
to
> v_emp(v_emp.last):=emp_type(20,'Name2');
And you should be OK.
SY.
|
|
|
|
| Re: Varray of object type [message #462372 is a reply to message #462370] |
Wed, 23 June 2010 14:45 |
SamuelJk
Messages: 25
Registered: August 2007
|
Junior Member |
|
|
Hi syakobson,
Thanks for your reply.
For example emp table having following rows
Emp_no Emp_name
10 Name1
11 Name2
12 Name3
13 Name4
14 name5
15 name6
16 name7
17 name8
18 name9
19 name10
declare
v_emp emp:= emp();
begin
for rec in ( select empno, ename from emp where rownum < 11)
loop
v_emp.extend;
v_emp(v_emp.last):=emp_type(rec.empno,rec.ename);
end loop;
for i in 1..v_emp.count
loop
dbms_output.put_line( v_emp(i).emp_type.emp_no||' '||v_emp(i).emp_type.emp_name);
end loop;
end;
The above code will give the expected below output ?
Emp_no Emp_name
10 Name1
11 Name2
12 Name3
13 Name4
14 name5
15 name6
16 name7
17 name8
18 name9
19 name10
Regards
Samuel
|
|
|
|
| Re: Varray of object type [message #462376 is a reply to message #462372] |
Wed, 23 June 2010 16:25 |
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
> The above code will give the expected below output ?
You wrote the code, so what keeps you from testing it? But I can tell you your code has errors. Also, there is no need to populate varray using FOR CURSOR loop. Use plain select with bulk collect.
SY.
|
|
|
|
|
|
|
|
|
|
| Re: Varray of object type [message #462449 is a reply to message #462400] |
Thu, 24 June 2010 05:22 |
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
SamuelJk wrote on Thu, 24 June 2010 03:10Now I tested the code, it is working fine.
You mean you corrected code. Otherwise:
declare
v_emp emp:= emp();
begin
for rec in ( select empno, ename from emp where rownum < 11)
loop
v_emp.extend;
v_emp(v_emp.last):=emp_type(rec.empno,rec.ename);
end loop;
for i in 1..v_emp.count
loop
dbms_output.put_line( v_emp(i).emp_type.emp_no||' '||v_emp(i).emp_type.emp_name);
end loop;
end;
/
dbms_output.put_line( v_emp(i).emp_type.emp_no||' '||v_emp(i).emp_type.emp_name);
*
ERROR at line 11:
ORA-06550: line 11, column 32:
PLS-00302: component 'EMP_TYPE' must be declared
ORA-06550: line 11, column 1:
PL/SQL: Statement ignored
SQL>
Also, did you replace FOR CURSOR loop with bulk collect as I suggested?
SY.
|
|
|
|
| Re: Varray of object type [message #462516 is a reply to message #462368] |
Thu, 24 June 2010 12:20 |
SamuelJk
Messages: 25
Registered: August 2007
|
Junior Member |
|
|
Hi SY,
Yes I Modifed the code to
declare
v_emp emp:= emp();
begin
for rec in ( select empno, ename from emp where rownum < 11)
loop
v_emp.extend;
v_emp(v_emp.last):=emp_type(rec.empno,rec.ename);
end loop;
for i in 1..v_emp.count
loop
dbms_output.put_line( v_emp(i).emp_no||' '||v_emp(i).emp_name);
end loop;
end;
Bulk collect
I tried below code for bulk collect you suggested
declare
v_emp emp:= emp();
begin
v_emp.extend(10);
select empno, ename bulk collect into v_emp.emp_no,emp.emp_name from emp;
for i in 1..v_emp.count
loop
dbms_output.put_line( v_emp(i).emp_no||' '||v_emp(i).emp_name);
end loop;
end;
Getting error. Sorry I am not able recollect the error code.
At the moment I am at Home not able check it right now.
Regards
Samuel.
CM: changed quote tags to code tags
[Updated on: Thu, 24 June 2010 12:23] by Moderator Report message to a moderator |
|
|
|
| Re: Varray of object type [message #462523 is a reply to message #462516] |
Thu, 24 June 2010 13:41 |
Solomon Yakobson
Messages: 3303
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
declare
v_emp emp:= emp();
begin
-- v_emp.extend(10); -- no need to extend, bulk collect will do it for you
select emp_type(empno,ename) -- make it an object, since we bulk collect into a collection of objects
bulk collect into v_emp -- we bulk collect into a collection of objects
from emp;
for i in 1..v_emp.count
loop
dbms_output.put_line( v_emp(i).emp_no||' '||v_emp(i).emp_name);
end loop;
end;
/
SY.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
