Advanced PL/SQL Collections: VARRAYs, Nested Tables, and Associative Arrays Quiz Quiz

Challenge your understanding of advanced PL/SQL collection types including VARRAYs, nested tables, and associative arrays. This quiz covers differences, usage scenarios, syntax, and capabilities for optimal collection handling in PL/SQL programming.

1. Identifying VARRAY Limits

   Which PL/SQL collection type requires you to specify a maximum number of elements when you define it?

   1. Nested Table
   2. Associative Array
   3. VARRAY
   4. Variant Table

   Explanation: VARRAY (Variable-Size Array) requires you to define an upper limit for its elements at creation. Nested tables and associative arrays do not have a predefined maximum size. 'Variant Table' is not a valid collection type. Associative arrays can grow dynamically with no upper bound specified.

2. Associative Array Key Type

   Which collection type allows you to use string values as keys to access individual elements, for example, employees('John')?

   1. Associative Array
   2. Nested Table
   3. Index-by Table
   4. VARRAY

   Explanation: Associative arrays support string (VARCHAR2) and integer keys, allowing you to use values like 'John' as an index. VARRAYs and nested tables only support integer subscripts. 'Index-by Table' is an older term, but it refers to associative arrays, making 'Associative Array' the precise choice. Nested tables do not support named keys.

3. Sparse vs Dense Collections

   Which PL/SQL collection type can become sparse, meaning it can have gaps between element indexes after deletions?

   1. Small Table
   2. Nested Table
   3. VARRAY
   4. Associative Array

   Explanation: Nested tables can become sparse after deleting elements, leaving gaps in their index numbering. VARRAYs remain dense and do not allow gaps. Associative arrays can also appear sparse if you use non-sequential keys, but 'Nested Table' is the main type that becomes sparse from deletions. 'Small Table' is not a valid PL/SQL collection.

4. Initialization Requirement

   Which collection type must be initialized with a constructor before you can add elements to it?

   1. Table Array
   2. VARRAY
   3. Nested Table
   4. Associative Array

   Explanation: VARRAYs require explicit initialization with a constructor method before elements can be added. Associative arrays do not need formal initialization; you can assign values directly. 'Table Array' is not a PL/SQL type. While nested tables do need instantiation in some contexts, VARRAYs always need constructors.

5. Bulk Binding Support

   Which PL/SQL collection types can be used with the BULK COLLECT statement to fetch multiple rows from a query?

   1. Array Table only
   2. Associative Array with string keys
   3. None of the above
   4. Nested Table and VARRAY

   Explanation: Nested tables and VARRAYs can both be used with BULK COLLECT to efficiently fetch query results into PL/SQL collections. Associative arrays can only participate in BULK COLLECT if they use integer keys, not string keys. 'Array Table' is not a recognized type, and 'None of the above' is incorrect.

6. Index Data Types

   For an associative array declared as TYPE my_array IS TABLE OF NUMBER INDEX BY VARCHAR2(10);, what is the data type of the index?

   1. VARCHAR2(10)
   2. CHAR(10)
   3. NUMBER
   4. DATE

   Explanation: In this associative array declaration, the index type is VARCHAR2(10), allowing you to use strings of up to 10 characters as keys. NUMBER and DATE would be valid index types for other collection needs, but not in this example. CHAR(10) is not used in the declaration shown.

7. Collection Storage in Database Tables

   Which collection type can be directly stored in a table column and supports set operations such as MULTISET UNION?

   1. VARRAY with string keys
   2. Associative Array
   3. Reserved Array
   4. Nested Table

   Explanation: Nested tables can be stored in database table columns and support set operations like MULTISET UNION. VARRAYs can be stored in table columns but have limited set operation support and do not allow string keys. 'Associative Array' cannot be stored in table columns. 'Reserved Array' is not a valid collection type.

8. Accessing Elements by Index

   If you have a VARRAY called numbers_varray, how do you access its third element?

   1. numbers_varray['3']
   2. numbers_varray[THIRD]
   3. numbers_varray(3)
   4. numbers_varray{3}

   Explanation: Elements in VARRAYs are accessed using parenthesis with a numeric index, like numbers_varray(3). The brackets [] and braces {} are incorrect syntax in PL/SQL. 'numbers_varray[THIRD]' is invalid as indexes must be integers and use parenthesis, not named labels or square brackets.

9. Collection Construction Syntax

   What is the correct way to construct an empty nested table named dept_nt of type dept_table_type?

   1. dept_nt := NEW dept_table_type;
   2. dept_nt = dept_table_type[];
   3. dept_nt : dept_table_type();
   4. dept_nt := dept_table_type();

   Explanation: The correct syntax for constructing an empty nested table is dept_nt := dept_table_type();. Using 'NEW' or assignment symbols other than ':=' would result in errors. 'dept_nt : dept_table_type();' and 'dept_nt = dept_table_type[];' are both syntactically incorrect in PL/SQL.

10. Collection Method Usage

    Which built-in PL/SQL method returns the highest index value currently in use in a nested table or VARRAY?

    1. COUNT
    2. LAST
    3. TOP
    4. FIRST

    Explanation: The LAST method returns the highest index currently used in nested tables and VARRAYs. FIRST returns the lowest index, while COUNT gives the number of elements but not the exact index value. 'TOP' is not a valid PL/SQL collection method.