PL/SQL - what is difference between varray and nested table.can u explain in brief and clear my these concept

GeekInterview.com > Interview Questions > Oracle > PL/SQL

Go To First | Previous Question | Next Question

PL/SQL | Question 67 of 241

what is difference between varray and nested table.
can u explain in brief and clear my these concepts.
also give a small and sweet example of both these.

Total Answers and Comments: 5

Last Update: May 07, 2008 Asked by: nayan

Sponsored Links

Best Rated Answer

—

Submitted by: krishnaindia2007

The following are the main differences between nested tables and varray

1. Nested tables are unbounded where as varray has maximun limit.
2. Nested tables are dense intially but can be sparse latter. So you can delete individual elements from nested tables. Varrays are always dense .It is not possible to delete individual elements in varrays.
3. Varrays stores data in-line. Nested tables are stored seperately , main table maintains a pointer to nested table.
4. When stored in database varrays retain their order where as nested tables do not.

Above answer was rated as good by the following members:
harit79

April 04, 2006 04:41:31

Deepika S Verma

RE: what is difference between varray and nested table...

According to O Reilly Book the main difference between Index-By Table(pl-Sql Table) Varray and nested tables are -

	Index-By Table	Nested Table	VARRAY
Dimensionality	Single	Single	Single
Usable in SQL?	No	Yes	Yes
Usable as column datatype in a table?	No	Yes; data stored out of line (in separate table)	Yes; data stored in line (in same table)
Uninitialized state	Empty (cannot be null); elements undefined	Atomically null; illegal to reference elements	Atomically null; illegal to reference elements
Initialization	Automatic when declared	Via constructor fetch assignment	Via constructor fetch assignment
In PL/SQL elements referenced via	BINARY_INTEGER (-2 147 483 647 .. 2 147 483 647)	Positive integer between 1 and 2 147 483 647	Positive integer between 1 and 2 147 483 647
Sparse?	Yes	Initially no; after deletions yes	No
Bounded?	No	Can be extended	Yes
Can assign value to any element at any time?	Yes	No; may need to EXTEND first	No; may need to EXTEND first and cannot EXTEND past upper bound
Means of extending	Assign value to element with a new subscript	Use built-in EXTEND procedure (or TRIM to condense) with no predefined maximum	EXTEND (or TRIM) but only up to declared maximum size
Can be compared for equality?	No	No	No
Retains ordering and subscripts when stored in and retrieved from database?	N/A	No	Yes

Is this answer useful? Yes | No

0 1

Overall Rating: -1

April 13, 2006 12:11:07

Sravan Anumala

RE: what is difference between varray and nested table...

Varry and Nestead table both are belongs to CollectionsThe Main difference is Varray has Upper bound where as Nestead table doesn't. It's size is unconstrained like any other database tableNestead table can be stored in DatabaseSyntax of Nestead TableTYPE nes_tabtype IS TABLE OF emp.empno type;nes_tab nes_tabtype;Syntax of VarryTYPE List_ints_t IS VARRAY(8) OF NUMBER(2);aList List_ints_t: List_ints_t(2 3 5 1 5 4);

Is this answer useful? Yes | No

February 09, 2007 06:49:50

Ganesh Ashok Korde

RE: what is difference between varray and nested table...

Nested table can be indexed where as VArray can't.

Is this answer useful? Yes | No

February 18, 2008 11:31:06

g_sidhu

Member Since: August 2007 Contribution: 122

RE: what is difference between varray and nested table.can u explain in brief and clear my these concepts.also give a small and sweet example of both these.

Nested tables are unbounded.

Initially dense but can become sparse through deletions.

Order is not preserved

Can be indexed

VARRAYs are always bounded(varying arrays have a limited number of entries)

Never sparse.

When you store and retrieve a VARRAY its element order is preserved.

Cannot be indexed

Is this answer useful? Yes | No

May 07, 2008 05:11:25

krishnaindia2007

Member Since: September 2007 Contribution: 854

RE: what is difference between varray and nested table.can u explain in brief and clear my these concepts.also give a small and sweet example of both these.

The following are the main differences between nested tables and varray

1. Nested tables are unbounded where as varray has maximun limit.
2. Nested tables are dense intially but can be sparse latter. So you can delete individual elements from nested tables. Varrays are always dense .It is not possible to delete individual elements in varrays.
3. Varrays stores data in-line. Nested tables are stored seperately main table maintains a pointer to nested table.
4. When stored in database varrays retain their order where as nested tables do not.

Is this answer useful? Yes | No

1 0

Overall Rating: +1

Go To Top

User Name		Remember Me?
Password

Question Bank