[TECH:DB:ORACLE]PLSQL TABLE V/S NESTED TABLE

 difference between PLSQL TABLE V/S NESTED TABLE.

Dear,

 

Just to refresh your skill.

 

 

TPL/SQL Table and Nested Table

Introduction:

As you know, a datatype is used to classify the data to be stored as number, varchar,
boolean etc. But a varible declared using a dataype can hold only a single data value
at a time, i.e a single memory location.

What if we need to store multiple rows of data in a single variable? There comes the
use of collections in Oracle. A variable declared as a collection could store an array
of data of the same TYPE or ROWTYPE. This is beneficial when we need to pass
multiple rows of data between Procedures or need to return multiple records from
functions especially to other languages like Java, C etc.

These are the main classifications:

PL/SQL TABLE (index-by tables) or NESTED TABLE

VARRAY

RECORD

OBJECT

Here I am discussing PL/SQL TABLE (index-by tables) and NESTED TABLE in
detail

1. PL/SQL TABLE (index-by tables) and NESTED TABLE

These are single-column tables and could be considered as an Array of data
but which is unbounded; i.e we cannot set a limit on number of rows like an array and
that s sometimes an added advantage.

Both PL/SQL TABLE and NESTED TABLE has got the same structure and
all, but the main difference is, nested tables could be stored in a database column
whereas PL/SQL tables could not be. To explain further, Nested table Dataypes can
be created standalone they could be used for columns in normal database Tables.

Declaration: