What is the difference between nested array and associative array?

Puneet Kushwah picture Puneet Kushwah · Dec 3, 2014 · Viewed 11.8k times · Source

There are two links http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#LNPLS99981 and

Purpose of using different types of PL/SQL collections in Oracle

by referring above two links i have two doubt

1.Which one is correct nested table?

2.If the oracle doc is correct what is the difference between nested table and associative array?

Answer

Wernfried Domscheit picture Wernfried Domscheit · Dec 3, 2014

Here is another difference which is not that commonly known. You can compare two nested tables with = or <> but associative array you cannot.

DECLARE

    TYPE associative_array IS TABLE OF INTEGER INDEX BY PLS_INTEGER;
    a_var_associative_array associative_array;
    b_var_associative_array associative_array;

    TYPE nested_table IS TABLE OF INTEGER;
    a_var_nested_table nested_table := nested_table(1, 2, 3, 4, 5);
    b_var_nested_table nested_table := nested_table(5, 4, 3, 2, 1);

BEGIN

    IF a_var_nested_table = b_var_nested_table THEN
        -- Note, the different order of values!
        DBMS_OUTPUT.PUT_LINE ( 'TRUE' );
    ELSE
        DBMS_OUTPUT.PUT_LINE ( 'FALSE' );
    END IF;

    -- IF a_var_associative_array = b_var_associative_array THEN -> gives you an error! 

END;

When you work with nested tables you can also use Multiset Operators, Multiset Conditions and SET which are not available for associative arrays.