PL/SQL   «Prev  Next»

Preferrable Method to Query Varray - Exercise

Write two queries using techniques learned in this module

Objective: Write queries to select varray data from object tables.

Exercise scoring

This exercise is worth a total of 15 points. You will receive up to 10 points for writing the two queries (five points for each query). You will receive up to five points for explaining the techniques that you used and why.


In this exercise, you will query CUSTOMER_OBJ_TABLE by using two different techniques to select the varray data stored within the column PHONE_LIST. The first technique employs a simple SELECT of the varray column from the object table. The second technique uses the flattening of the nested tables technique that you learned in Lesson 3. In this case, instead of the column name, you will select the VALUE of the table alias.

Download files


For the first SQL statement, use a simple SELECT clause. For the second SQL statement, include two SELECT statements, the first for retrieving the VALUE of the table alias and the second for selecting the varray column from the object table with a WHERE clause.


To retrieve the contents of the varray column, use the VALUE operator.

Answer the following questions

  1. What is the difference between the first and second SQL statements?
  2. What is the advantage of the second SQL statement over the first?

Submitting your Exercise

After you have defined the two queries and added your explanation for the techniques that you used and why, place it within the text area below and click Submit to submit the exercise.
Remember that you must submit all your responses to this exercise at once.