| Problem with Collection [message #596506] |
Tue, 24 September 2013 04:39  |
ejdrba
Messages: 27
Registered: May 2005
Location: Kolkata
|
Junior Member |
|
|
Hi,
I am facing one problem, with populating the collection object.
I am getting an error -
'ORA-06530: Reference to uninitialized composite"
My Code is as below
create type test_t as object (empno number, ename varchar2(20))
/
create type test_tt as table of test_t
/
create or replace procedure test_p
is
data_t test_tt := test_tt ();
vn number;
begin
for i in (select empno, ename from emp)
loop
data_t.extend;
data_t(data_t.last).empno := i.empno;
data_t(data_t.last).ename := i.ename;
end loop;
select count(*)
into vn
from table(cast(data_t as test_tt));
dbms_output.put_line(vn);
exception
when others then
dbms_output.put_line(sqlerrm);
end;
/
Please let me know, what could be reason, if this is the right way.
Thanks and Regards,
Debraj.
[EDITED by LF: fixed [code] tags]
[Updated on: Tue, 24 September 2013 07:24] by Moderator Report message to a moderator |
|
|
|
| Re: Problem with Collection [message #596509 is a reply to message #596506] |
Tue, 24 September 2013 04:45  |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
|
Oracle will tell you the reason and the exact line the error happened, if you remove the WHEN OTHERS exception handler, which is one of the most problematic bugs you can code into Oracle.
|
|
|
|
 Re: Problem with Collection [message #596510 is a reply to message #596506] |
Tue, 24 September 2013 05:01 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> create or replace procedure test_p
2 is
3 data_t test_tt := test_tt ();
4 vn number;
5 begin
6 for i in (select empno, ename from emp)
7 loop
8 data_t.extend;
9 data_t(data_t.last) := test_t(i.empno, i.ename); --<-- here
10 end loop;
11
12 select count(*)
13 into vn
14 from table(cast(data_t as test_tt));
15
16 dbms_output.put_line(vn);
17 end;
18 /
Procedure created.
SQL> execute test_p
14
PL/SQL procedure successfully completed.
or
SQL> create or replace procedure test_p
2 is
3 data_t test_tt := test_tt ();
4 vn number;
5 begin
6 for i in (select empno, ename from emp)
7 loop
8 data_t.extend;
9 data_t(data_t.last) := test_t(null, null); --<-- was missing
10 data_t(data_t.last).empno := i.empno;
11 data_t(data_t.last).ename := i.ename;
12 end loop;
13
14 select count(*)
15 into vn
16 from table(cast(data_t as test_tt));
17
18 dbms_output.put_line(vn);
19 end;
20 /
Procedure created.
SQL> execute test_p
14
PL/SQL procedure successfully completed.
[Updated on: Tue, 24 September 2013 05:11] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Problem with Collection [message #596550 is a reply to message #596512] |
Tue, 24 September 2013 12:56 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
And one more point. You have collection, right? So why are you using FOR cursor loop to populate it? Just use select with bulk collect. Then you don't even need to initialize collection and object. Also, there is no need to use SQL to get collection element count:
create or replace
procedure test_p
is
data_t test_tt;
begin
select test_t(empno,ename)
bulk collect
into data_t
from emp;
dbms_output.put_line(data_t.count);
end;
/
Procedure created.
SCOTT@orcl > set serveroutput on
SCOTT@orcl > exec test_p
14
PL/SQL procedure successfully completed.
SCOTT@orcl >
SY.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Problem with Collection [message #596757 is a reply to message #596753] |
Thu, 26 September 2013 03:09 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
With PL/SQL type the code would be (commenting the differences with the previous code, note the differences in the type declaration):
SQL> create or replace procedure test_p
2 is
3 type test_t is record (empno number, ename varchar2(20));
4 type test_tt is table of test_t index by binary_integer;
5 data_t test_tt; -- := test_tt ();
6 vn number;
7 begin
8 for i in (select empno, ename from emp)
9 loop
10 -- data_t.extend;
11 data_t(nvl(data_t.last,0)+1).empno := i.empno;
12 data_t(nvl(data_t.last,0)).ename := i.ename;
13 end loop;
14
15 -- select count(*)
16 -- into vn
17 -- from table(cast(data_t as test_tt));
18
19 dbms_output.put_line(data_t.count);
20 end;
21 /
Procedure created.
SQL> exec test_p
14
PL/SQL procedure successfully completed.
And, yes, you have not to initialize the array and elements, this is implicitly done by the PL/SQL engine.
|
|
|
|
| Re: Problem with Collection [message #596824 is a reply to message #596753] |
Thu, 26 September 2013 08:23 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
shumail wrote on Thu, 26 September 2013 03:48My question is that we initialize it again because we declare this collection type externally and otherwise if we create this directly in our package then we don't need to do this, rite?
Look at original example. Type test_tt is collection of objects. Statement:
data_t test_tt := test_tt ();
initializes collection. At this point data_t is initialized but empty. Statement:
adds an element to data_t. Now we can assign a value to newly added element. And since element type is object type we have no problem (and Michel showed it) assigning object to newly added collection element:
data_t(data_t.last) := test_t(i.empno, i.ename);
But what OP tries to do was quite different. OP tried to assign value not to collection element but to it attribute. In order to do that collection element must already have object assigned to it. In other word object must be initialized before we can work on atribute level. And this has nothing to do with collections. Just look what happens:
SCOTT@orcl > desc test_t
Name
--------------------------------------------------
EMPNO
ENAME
SCOTT@orcl > declare
2 v_obj test_t;
3 begin
4 v_obj.empno := 1;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-06530: Reference to uninitialized composite
ORA-06512: at line 4
SCOTT@orcl >
So it has nothing to do with declaring collection in SQL or in PL/SQL. It depends on collection type and element type. Nested table requires initialization regardless if type is SQL or PL/SQL declared type. Object type is SQL type and always require initialization.
SY.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
