Home » SQL & PL/SQL » SQL & PL/SQL » using Collection variable???
using Collection variable??? [message #265363] Thu, 06 September 2007 03:17 Go to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
Hi all

Given below is an anonymous block.

declare
type name_rec is record
(first_name varchar2(20), last_name varchar2(20));
type vrec is varray(20) of name_rec;
vrec1 vrec;
begin
null;
end;


Now i want to store values (or) use the collection
type of variable vrec1. how do i do it?
please help me in this regard..

Thanks in advance
Natesh
Re: using Collection variable??? [message #265378 is a reply to message #265363] Thu, 06 September 2007 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

What did you try? What is your problem?

Regards
Michel
Re: using Collection variable??? [message #265389 is a reply to message #265378] Thu, 06 September 2007 04:20 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
In the begin block i have used
vrec1(1).first_name:='abc';.
here i am getting an error "reference to uninitialized
collection".

[Updated on: Thu, 06 September 2007 04:21]

Report message to a moderator

Re: using Collection variable??? [message #265420 is a reply to message #265389] Thu, 06 September 2007 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You first have to instantiate the object.
Then you have to allocate memory for each element/record.
SQL> declare
  2    type name_rec is record 
  3      (first_name varchar2(20), last_name varchar2(20));
  4    type vrec is varray(20) of name_rec;
  5    vrec1 vrec := vrec();       -- Instantiation / Initialization of the collection
  6  begin
  7    vrec1.extend;               -- Allocation / Initialization of a new element
  8    vrec1(1).first_name:='abc'; -- Setting the new element
  9  end;
 10  /

PL/SQL procedure successfully completed.

Regards
Michel
Re: using Collection variable??? [message #265434 is a reply to message #265420] Thu, 06 September 2007 05:38 Go to previous messageGo to next message
thisisnatesh
Messages: 92
Registered: March 2007
Member
ThankQ Mic
Re: using Collection variable??? [message #267008 is a reply to message #265420] Wed, 12 September 2007 04:28 Go to previous messageGo to next message
sarans
Messages: 30
Registered: November 2006
Location: UK
Member
Michel

I am going to implement this collection, but the only difference is, which I dont know the size of the varray:-

type vrec is varray(20) of name_rec;

How to populate data within this collection type not restricting only to 20, its dynamic...

Please let me know, how to do it...

Cheers,
Saran.
Re: using Collection variable??? [message #267015 is a reply to message #267008] Wed, 12 September 2007 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use a varray, use a table.
SQL> declare
  2    type name_rec is record 
  3      (first_name varchar2(20), last_name varchar2(20));
  4    type vrec is table of name_rec index by binary_integer;
  5  begin
  6    null;
  7  end;
  8  /

PL/SQL procedure successfully completed.

varray is made when you know the maximum size and don't want to exceed it.

Regards
Michel
Re: using Collection variable??? [message #267152 is a reply to message #267015] Wed, 12 September 2007 10:39 Go to previous messageGo to next message
sarans
Messages: 30
Registered: November 2006
Location: UK
Member
Michel

Thanks for the reply, and yes I have implemented the Table type and its working fine...

Now I want to populate only unique values, to be used at later point, is there any collection methods which I can use it and how?? or do I need to use the IF condition to check whether it exists already, if not exists then add it into the Table etc.

Please let me know...

Cheers,
Saran.

Re: using Collection variable??? [message #267158 is a reply to message #267152] Wed, 12 September 2007 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use associative table.
You still have to check it but you don't have to check every element:
SQL> declare
  2    type mytab is table of varchar2(20) index by varchar2(20);
  3    myvar mytab;
  4  begin
  5    myvar('Cadot') := 'Michel';
  6    if myvar.exists('Cadot') then null; 
  7    else null;
  8    end if;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Regards
Michel


Re: using Collection variable??? [message #267165 is a reply to message #267158] Wed, 12 September 2007 11:19 Go to previous messageGo to next message
sarans
Messages: 30
Registered: November 2006
Location: UK
Member
Michel

Thanks for the reply.

Apologies for not letting you know my requirement in the first place, I have to use 2 values, can I do it in the same way??? I am using 2 variables within the table i.e. value varchar2 and id number, see below:-


DECLARE
  TYPE Name_rec IS RECORD(VALUE VARCHAR2(34),
                           Id NUMBER);
   TYPE vrec IS TABLE OF NAME_REC INDEX BY BINARY_INTEGER ;
   vrec1  VREC;
BEGIN
  FOR i IN 1.. 10 LOOP
    -- "how to check here??"
    -- I want to check both values for the existance...
    vrec1(i).VALUE := 'test'||i;    
    vrec1(i).Id := i;

  END LOOP;
END;


please let me know

Cheers,
Saran.

[Updated on: Wed, 12 September 2007 11:29]

Report message to a moderator

Re: using Collection variable??? [message #267170 is a reply to message #267165] Wed, 12 September 2007 11:29 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not? I used your first example with first_name and last_name.
You can use ID to index your table of "VALUE" (myvar(ID):=VALUE).

Regards
Michel
Previous Topic: Runtime Select
Next Topic: Partition index Tablespace
Goto Forum:
  


Current Time: Tue Dec 06 00:25:17 CST 2016

Total time taken to generate the page: 0.14370 seconds