Varrays

Que: What is the varrays ?
Ans: A Varray stores elements of the same type in the order in which they are added. The number of elements in a Varray must be known at the time of its declaration. In other words, a Varray has a fixed lower and upper bounds, making it most similar to collection types from other programming languages. Once it is created and populated, each element can be accessed by a numeric index. We mentioned that a Varray differs from Nested Tables and Associative Arrays in that you must supply a size during its declaration.

Example:
The following statements declare, and then populate, a Varray that will contain 4 elements of the same type as the column genre_name in table book_genre:
DECLARE TYPE genres IS VARRAY(4) OF book_genre.genre_name%TYPE; Fiction_genres genres;BEGIN fiction_genres := genres('MYSTERY','SUSPENSE', 'ROMANCE','HORROR');END;
Syntax :
TYPE type_name IS {VARRAY VARYING ARRAY} (size_limit) OF element_type [NOT NULL];
Þ type_name is a type specifier used later to declare collections.
Þ size_limit is the number of elements in the array.
Þ element_type is any PL/SQL datatype .

Que: Could we modified the declared size of varrays ?
Ans: This example usese the EXTENDS method to demonstrate that it is possible to modify a Varray's size programmatically.--Add a new genre.IF adding_new_genre THEN --Is this genre id already in the collection? IF NOT fiction_genres.EXISTS(v_genre_id) THEN --**Add** another element to the varray. fiction_genres.EXTENDS(1); fiction_genres(v_genre_id) := v_genre; END IF; --Display the total # of elements. DBMS_OUTPUT.PUT_LINE('Total # of entries in fiction_genres is : 'fiction_genres.COUNT(); END IF;......--Remove all entries.IF deleting_all_genres THEN Fiction_genres.DELETE();END IF;

Que:Advantages of varrays
Ans:
Varrays (and Nested Tables) have over Associative Arrays is their ability to be added to the database. For example, you could add the genres type, a Varray, to a DML statement on the library table.
When a table contains a Varray type, its data is included in-line, with the rest of the table's data.

Example: --Insert a new collection into the column on our book_library table.INSERT INTO book_library (library_id, name, book_genres) VALUES (book_library_seq.NEXTVAL,'Brand New Library', Genres('FICTION','NON-FICTION', 'HISTORY', 'BUSINESS AND FINANCE'));
The query SELECT name, book_genres from book_library returns us:NAME BOOK_GENRES-------------------- ---------------------------------------------Brand New Library GENRES('FICTION', 'NON-FICTION', 'HISTORY', 'BUSINESS AND FINANCE')

Que: Disdvantages of varrays?
Ans:
If you require your collection to be stored in the database but would like the flexibility to manipulate elements individually, Nested Tables are a better solution.
It is not possible to perform inserts, updates, and deletes on the individual elements in a Varray. The Varray type does not because Varray data is stored as one single, delimited piece of data within the database.

Example: We couldn’t update to “FICTION” because this is the individual elements in this column.NAME BOOK_GENRES-------------------- ---------------------------------------------Brand New Library GENRES('FICTION', 'NON-FICTION', 'HISTORY', 'BUSINESS AND FINANCE')

Note : Below are the example which show that UPDATE/DELETE/INSET is possible in NESTED table on individual ELEEMENTS.

UPDATE TABLE(SELECT place FROM employee WHERE name ='Ravi') SET
column_value = 'CDC5' WHERE column_value = 'CDC2';

DELETE from TABLE(SELECT place FROM employee WHERE name ='Ravi')
WHERE column_value = 'CDC5'


Some more details on Vaarays :
The following statements declare, populate and then update a Varray that will contain 3 elements of the same type
v Create a Varray as follows
CREATE TABLE employee
(ENO NUMBER,NAME VARCHAR2(60), PLACE VARCHAR2 (60));
CREATE TYPE details IS VARRAY (3) OF employee.place%type(60);
(or)
CREATE TYPE details IS VARRAY (3) OF VARCHAR2(60);
CREATE TABLE employee
(ENO NUMBER,NAME VARCHAR2(60), PLACE details );
v Insert records into varray as follows
INSERT INTO employee VALUES(11004797,'SabariRaj',
details('CDC3','Guindy','Chennai'));
Select * from employee.
The above query returns:
ENO NAME PLACE
------------- ------------- --------------------------
11004797 Sabari Raj details('CDC3','Guindy','Chennai').

v Update the records in varray as follows
update employee set place= details('CDC1', 'Guindy','Chennai') where eno=11004797;
Select * from employee.
The above query returns:
ENO NAME PLACE
------------- ------------- --------------------------
11004797 Sabari Raj details('CDC1','Guindy','Chennai').


v It is not possible to perform inserts, updates, and deletes on the individual elements in a Varray.