Home » SQL & PL/SQL » SQL & PL/SQL » collections and Records VARRAY
collections and Records VARRAY [message #323881] Thu, 29 May 2008 23:01 Go to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Hi,

When i am using below VARRAY block it shows an error can anybody support me.My understanding is that initialization of collection will be in fetch statement in my block.

I don't know why this error will occur.

  1  declare
  2   cursor c1 is select * from emp;
  3   TYPE v_arr is VARRAY(20) OF c1%rowtype;
  4   varr v_arr;
  5   cnt number;
  6   i number;
  7  begin
  8   open c1;
  9      i:=0;
 10   loop
 11   fetch c1 into varr(i);
 12   exit when c1%notfound;
 13   cnt:= c1%rowcount;
 14    dbms_output.put_line('count is'||cnt);
 15    dbms_output.put_line('rowcount is'||c1%rowcount||'time');
 16       i:=i+1;
 17   end loop;
 18* end;
SQL> 
SQL> /
declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 11
Re: collections and Records VARRAY [message #323891 is a reply to message #323881] Thu, 29 May 2008 23:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> declare
  2    cursor c1 is select * from emp;
  3    TYPE v_arr is VARRAY(20) OF c1%rowtype;
  4    varr v_arr;
  5    cnt number;
  6    i number;
  7  begin
  8    open c1;
  9    varr := v_arr(); -- initialize collection
 10    i := 1; -- start at 1, not 0
 11    loop
 12  	 varr.extend; -- extend the collection
 13  	 fetch c1 into varr(i);
 14  	 exit when c1%notfound;
 15  	 cnt := c1%rowcount;
 16  	 dbms_output.put_line ('count is ' || cnt);
 17  	 dbms_output.put_line ('rowcount is ' || c1%rowcount || ' time');
 18  	 i := i + 1;
 19    end loop;
 20  end;
 21  /
count is 1
rowcount is 1 time
count is 2
rowcount is 2 time
count is 3
rowcount is 3 time
count is 4
rowcount is 4 time
count is 5
rowcount is 5 time
count is 6
rowcount is 6 time
count is 7
rowcount is 7 time
count is 8
rowcount is 8 time
count is 9
rowcount is 9 time
count is 10
rowcount is 10 time
count is 11
rowcount is 11 time
count is 12
rowcount is 12 time
count is 13
rowcount is 13 time
count is 14
rowcount is 14 time

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> 

Re: collections and Records VARRAY [message #323900 is a reply to message #323891] Fri, 30 May 2008 00:06 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Thanks Barbara Boehmer

And another doubt that it is not need to initialise if i am using TABLE? for both (Nested table and Index By table). I am right

[Updated on: Fri, 30 May 2008 00:07]

Report message to a moderator

Re: collections and Records VARRAY [message #323906 is a reply to message #323900] Fri, 30 May 2008 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORAGENASHOK wrote on Fri, 30 May 2008 07:06
Thanks Barbara Boehmer

And another doubt that it is not need to initialise if i am using TABLE? for both (Nested table and Index By table). I am right

Just try it!

Regards
Michel

Re: collections and Records VARRAY [message #324090 is a reply to message #323900] Fri, 30 May 2008 15:00 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Varrays and nested tables (pl/sql tables) require initialization and extend. Associate arrays (index by tables) do not.

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/collections.htm#i20453
Previous Topic: External table creation
Next Topic: how to delete the particular column
Goto Forum:
  


Current Time: Thu Dec 08 18:05:27 CST 2016

Total time taken to generate the page: 0.12175 seconds