PL/SQL Questions and Answers – PL/SQL Collections

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

Answer: b
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

Answer: c
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

Answer: a
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.
advertisement
advertisement

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

Answer: a
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)

advertisement
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; 
TABLE_NAME type_name;

c)

advertisement
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;
View Answer
Answer: b
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;
View Answer
Answer: d
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

Answer: c
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

Answer: a
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

Answer: d
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

Answer: b
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.

advertisement
advertisement
Subscribe to our Newsletters (Subject-wise). Participate in the Sanfoundry Certification contest to get free Certificate of Merit. Join our social networks below and stay updated with latest contests, videos, internships and jobs!

Youtube | Telegram | LinkedIn | Instagram | Facebook | Twitter | Pinterest
Manish Bhojasia - Founder & CTO at Sanfoundry
Manish Bhojasia, a technology veteran with 20+ years @ Cisco & Wipro, is Founder and CTO at Sanfoundry. He lives in Bangalore, and focuses on development of Linux Kernel, SAN Technologies, Advanced C, Data Structures & Alogrithms. Stay connected with him at LinkedIn.

Subscribe to his free Masterclasses at Youtube & discussions at Telegram SanfoundryClasses.