What are the difference between assoicate array, nested table and varray ?

Associate array	Nested Table	Varray
Single dimensional	Single dimensional	Single dimensional
Unbounded	Unbounded	Bounded. The row limit has to be specified while declaring
Sparse collection.	Initially Dense collection. Later on can be made sparce by deleting the element in nested table	Dense collections
Can only be used in PL/SQL	Can be used both in PL/SQL as well as a column of a database table.	Can be used both in PL/SQL as well as a column of a database table.
-	Nested table is multiset. I.e. when you store & retrieve elements in Nested table its order is not preserved.	The order is preserved while storing and extracting the elements.

Collections

What is collection?

A collection is a data type in oracle which can be compared to single dimensional array.

What are the types of collections ?

There are 3 types of collections :

1. Associate array
2. Nested Table
3. VARRAY

Advantages of collection :

• To track list of elements which are changed during the execution of the program
• It can be used with FORALL and BULK COLLECT to improve the DML performance in PL/SQL
• It can be used to cache the static database information which are queried more frequently during session or program. This will improve the performance.
  

Oracle 11g New Features

• The PL/SQL in oracle 11g is implemented with the new object oriented concepts.

• In oracle 11g the code compilation has been changed from interpreted to Native. i.e. It is no more dependent on the third party c compiler instead PL/SQL source code is used to generate the native code. This helps in faster execution.

• Database passwords are made case sensitive with oracle 11g.

• Read only tables: A table can be set to read only mode to restrict write operations on the table. This can be altered using the ‘alter table’ command.

• Invisible indexes: Oracle 11g introduces invisible indexes for query performance testing. This can be used to test the performance of the index on the query using either index hint or altering the session to use the invisible indexes. If the index is improving the performance then it can be made as visible index.

• Virtual Columns: This is a column whose value is derived from the other actual columns in the table. This shows the same behavior as other columns when it comes for indexing and gathering statistics. Oracle does not support LONG and RAW values for virtual columns.

• Result cache: A new SGA component (in Shared pool) in oracle 11g i.e. result cache to store the result of SQL and PL/SQL for the same input set. This can be achieved in PL/SQL block by including additional RESULT_CACHE clause in the function definition.

• Compound trigger: In compound trigger logic all the timing point logics are clubbed into one single body. This can be used to avoid the mutating trigger problem.

• Set the triggering order by ‘FOLLOWS’ clause: Using ‘FOLLOWS’ clause the order of the trigger execution set on the same timing point can be configured. Means the developer can decide which trigger has to be executed first and the next one and so on.

• Oracle trigger can be created in DISABLE mode in oracle 11g. Prior to oracle 11g the trigger must be created in ENABLED mode only.

• Additional partitioning strategies in oracle 11g:

  § Extended composite partitioning.

  § Virtual column based partition

  § Interval partitioning

  § REF partition

• Program inlining (PLSQL_OPTIMIZE_LEVEL) : It refers to replacing a program call with the copy of the program. There were two admissible values for the parameter before 3^rd level has been introduced in oracle 11g. A new pragma parameter PRAGMA INLINE has been introduced.

• REGEXP_COUNT: This new feature counts the occurrence of a character or string in another string.

• LISTAGG: This analytical function aggregates the result set in multiple rows into single column.

• Oracle 11g allows providing a default value during the table alteration.

• Oracle 11g allows direct sequence assignment to variables. Prior to oracle 11g it was done through select statement.

• A new clause ‘CONTINUE’ has been introduced in oracle 11g to skip the records which are not meeting the condition.

Records in PL/SQL

What is record in PL/SQL ?
A record is a composite data structure i.e. it consists of elements. A record by itself does not have value, but the elements in it will be having values. We can compare a record in PL/SQL with that of the table.

Various Methods to declare records :

1. Table based declaration.
2. Cursor based declaration.
3. Programmer defined records.

PS: Pseudo records in trigger are :NEW and :OLD