Collections

Que: Why collections are required in pl/sql programming ?
Ans: PL/SQL code will reside on the server, an ideal place for programs that require intensive database interaction. Having said that, anytime a software application links up with a database, there is a performance price to be paid. Not only that, programs that continually switch off between code and SQL can become quite complex. PL/SQL collections can address some of these concerns.

Que: What is the collection?
Ans: A collection can be defined as a group of ordered elements, all of the same type, that allows programmatic access to its elements through an index.

Advantages:
· Collections can help to simplify code
· A collection can provide is improved application performance.
We can utilize collections to 'cache' static data that needs to be regularly accessed. This results in reduced calls to a database.

Que: What are the types of PLSQL collections
Ans: Oracle provides three types of PL/SQL collections:
· Nested tables
· Varrays
· Associative arrays

Que: Tell us about the history on PLSQL collections?
Ans:
· With the release of Oracle 7, Oracle introduced the PL/SQL Table. By using PL/SQL Tables, it was possible to create a collection of items, all of the same type, indexed by an integer.

TYPE book_title_tab IS TABLE OF book.title%TYPE
INDEX BY BINARY_INTEGER;
· The only way to access the elements of a PL/SQL Table was through its numeric index.
· In version 8, Oracle introduced two collection types, Nested Tables and Varrays. At this time, the PL/SQL Table was renamed to 'index-by table'. As of Oracle 9i, PL/SQL Tables (index-by tables) have again been renamed to Associative Arrays. The Associative Array functions much the same way the PL/SQL Table of old did.