This set of PL/SQL Multiple Choice Questions & Answers (MCQs) focuses on “PL/SQL Collections”.
1. A collection is a group of elements of different datatypes.
a) True
b) False
View Answer
Explanation: A collection is an ordered group of elements having the same data type. Each element has a unique id that tells about its position in the collection.
2. How many types of collections are provided by PL/SQL?
a) 1
b) 2
c) 3
d) 4
View Answer
Explanation: There are 3 different types of collections provided by PL/SQL – Index-by Tables or Associative Arrays, Nested Table, Variable-size Array or Varrays. Index-by Tables and Nested Tables comes under the category of Tables and Varray is a type of array.
3. Associative Arrays are also called as Index-by Tables.
a) True
b) False
View Answer
Explanation: Associative Arrays are also called as Index-by Tables as every element present inside the array has a subscript number to define the position of the element inside the table. These subscript numbers can be called as Indexes.
4. Which of the following collection is always a dense collection type?
a) Varray
b) Associative Arrays
c) Nested Tables
d) All collections in PL/SQL are dense in nature
View Answer
Explanation: Varray is a dense collection type. A dense collection has no gaps between elements—every element between the first and last element is defined and has a value (the value can be NULL unless the element has a NOT NULL constraint). Associative Arrays can also be a dense collection about it is not guaranteed every time.
5. Which of the following syntax is correct to create an Associative Array?
a)
TABLE OF element_type [NOT NULL] INDEX BY subscript_type; TABLE_NAME type_name;
b)
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; TABLE_NAME type_name;
c)
TYPE type_name IS TABLE OF element_type [NOT NULL]; TABLE_NAME type_name;
d)
TYPE type_name [NOT NULL] INDEX BY subscript_type; TABLE_NAME type_name;
Explanation: The correct syntax to create an Associative array is –
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; TABLE_NAME type_name;
Here, TYPE, IS TABLE OF and INDEX BY are keywords. NOT NULL is not mandatory to use and type_name, element_type, subscript_type and table_name depends on the user.
6. Which of the following syntax is correct to create a Nested Array?
a)
TABLE OF element_type [NOT NULL]; TABLE_NAME type_name;
b)
TYPE type_name IS TABLE OF element_type [NOT NULL]; TABLE_NAME;
c)
TYPE type_name [NOT NULL]; TABLE_NAME type_name;
d)
TYPE type_name IS TABLE OF element_type [NOT NULL]; TABLE_NAME type_name;
Explanation: The correct syntax to create a Nested Array is –
TYPE type_name IS TABLE OF element_type [NOT NULL]; TABLE_NAME type_name;
This declaration is similar to the declaration of an Associative array, but there is no INDEX BY clause.
7. Which of the following collection exception is raised when a subscript exceeds the number of elements in a collection?
a) VALUE_ERROR
b) COLLECTION_IS_NULL
c) SUBSCRIPT_BEYOND_COUNT
d) SUBSCRIPT_OUTSIDE_LIMIT
View Answer
Explanation: The SUBSCRIPT_BEYOND_COUNT exception is raised when a subscript exceeds the number of elements in a collection. The SUBSCRIPT_BEYOND_COUNT error is where the in-limit of a subscript was greater than that of the count of a varray or was too large for a nested table.
8. Which of the following collection exception is raised when a subscript assigns an element that was deleted, or a non-existent element of an associative array?
a) NO_DATA_FOUND
b) COLLECTION_IS_NULL
c) VALUE_ERROR
d) SUBSCRIPT_OUTSIDE_LIMIT
View Answer
Explanation: The NO_DATA_FOUND exception is raised when a subscript assigns an element that was deleted. The NO_DATA_FOUND exception is raised when the FETCH statement is not able to find the relevant data that has been asked.
9. Which of the following collection method checks if the specified element is present in the collection or not?
a) EXTEND
b) NEXT
c) COUNT
d) EXISTS
View Answer
Explanation: The EXISTS(n) method is used to check if the specified element (n) is present in the collection or not. It Returns TRUE if the nth element in a collection exists, otherwise it returns FALSE.
10. Which of the following method is used to remove n number of elements from the end of a collection?
a) TRIM
b) TRIM(n)
c) DELETE
d) DELETE(n)
View Answer
Explanation: The TRIM(n) method is used to remove n number of elements from the end of a collection. The TRIM method only removes 1 element from the end of a collection. The DELETE method removes all elements from a collection. The DELETE(n) method is used to remove the nth element from an associative array with a numeric key or a nested table.
Sanfoundry Global Education & Learning Series – PL/SQL.
To practice all areas of PL/SQL, here is complete set of 1000+ Multiple Choice Questions and Answers.
If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]
