Home » SQL & PL/SQL » SQL & PL/SQL » Problem with Collection (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
Problem with Collection [message #596506] Tue, 24 September 2013 04:39 Go to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3090
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.
icon14.gif  Re: Problem with Collection [message #596510 is a reply to message #596506] Tue, 24 September 2013 05:01 Go to previous messageGo to next message
Michel Cadot
Messages: 58635
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 #596512 is a reply to message #596510] Tue, 24 September 2013 05:06 Go to previous messageGo to next message
ejdrba
Messages: 27
Registered: May 2005
Location: Kolkata
Junior Member

Hi Michel,

Thanks alot for your help.

Regards,

Debraj.
Re: Problem with Collection [message #596513 is a reply to message #596512] Tue, 24 September 2013 05:09 Go to previous messageGo to next message
Maaher
Messages: 7041
Registered: December 2001
Senior Member
Do you understand why your code failed and what Michel did?

MHE
icon4.gif  Re: Problem with Collection [message #596514 is a reply to message #596512] Tue, 24 September 2013 05:10 Go to previous messageGo to next message
Michel Cadot
Messages: 58635
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Don't forget Thomas' very important remark.
Carefully read the link.

Re: Problem with Collection [message #596550 is a reply to message #596512] Tue, 24 September 2013 12:56 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1966
Registered: January 2010
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 #596629 is a reply to message #596506] Wed, 25 September 2013 04:28 Go to previous messageGo to next message
ejdrba
Messages: 27
Registered: May 2005
Location: Kolkata
Junior Member

Thanks all.

Actually, I was doing some practice on the old skills..

Thanks once again for all your precious time.

Regards,

Debraj.
Re: Problem with Collection [message #596747 is a reply to message #596629] Thu, 26 September 2013 02:24 Go to previous messageGo to next message
shumail
Messages: 66
Registered: September 2012
Location: Canada
Member
Hi Michel

Appreciate if you can tell me why you initialize collection again in your procedure #2:

data_t(data_t.last) := test_t(null, null); 
--was missing

If I look into procedure #2 then collection already initialize
data_t test_tt := test_tt ();
so why need to initialize it again.

If I look into procedure #1 then you initialize it only one.

Really appreciate your reply because want to get knowledge from your type of experts people. Thanks



icon14.gif  Re: Problem with Collection [message #596749 is a reply to message #596747] Thu, 26 September 2013 02:31 Go to previous messageGo to next message
Michel Cadot
Messages: 58635
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"data_t test_tt := test_tt ();" this allocates and initializes the array.
In the same way, you cannot use a record before allocating it and tbis is what "test_t(null, null)" (in addition, it also initializes the attributes to null).
As I showed with my first code is that you can do at the same time both the allocation and initialization with the final values using "test_t(i.empno, i.ename)".

Re: Problem with Collection [message #596753 is a reply to message #596749] Thu, 26 September 2013 02:48 Go to previous messageGo to next message
shumail
Messages: 66
Registered: September 2012
Location: Canada
Member
Thanks for the reply and got your point . Another thing, you mentioned the points
you cannot use a record before allocating it and tbis is what "test_t(null, null)" (in addition, it also initializes the attributes to null).
, My 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?
icon14.gif  Re: Problem with Collection [message #596757 is a reply to message #596753] Thu, 26 September 2013 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 58635
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1966
Registered: January 2010
Senior Member
shumail wrote on Thu, 26 September 2013 03:48
My 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:

data_t.extend;


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.
Re: Problem with Collection [message #596876 is a reply to message #596824] Fri, 27 September 2013 00:15 Go to previous message
shumail
Messages: 66
Registered: September 2012
Location: Canada
Member
Great explanation. Really appreciate your replies... Thanks once again.
Previous Topic: Same source code giving different results
Next Topic: Subtracting two dates in string format with out AM or PM
Goto Forum:
  


Current Time: Thu Jul 31 13:44:57 CDT 2014

Total time taken to generate the page: 0.09741 seconds