RelationalDBDesign
SiteMap
PL/SQL
Stored Objects
PL/SQL Programming
PL/SQL
«Prev
Next»
Programming PL/SQL
Querying Object Tables
Using Oracle Objects
Object Oriented Terms
Query Object Tables
Collections Varrays Oracle
Querying Nested Tables
Practical/SQL Examples
SQL Query Example
Querying Complex Object
Alternate Query Example
Object Relational Summary
Querying Related Tables
Understanding Related Object Tables
Analyzing Dangling References
Writing deref Queries
Querying Object Tables
Creating PL/SQL Block
Object Relational Conclusion
Querying Nested Tables
Flattening nested Table
Query Flattens Nested Table
Simple varray Query
Query to Flatten varray
Flatten varray - Exercise
Procedure retrieves Nested Table
Varrays Nested Table Conclusion
Object Table Modification
Modifying Object Tables
Insert into Table
SQL Insert Rows
SQL Update Object Table
Subquery in Update
Delete Rows from Table
DML Statements Conclusion
Modifying Nested Tables
Modifying nested Varrays
Insert Rows into Table
Varray insert Command
Updating nested Table Rows
Updating Varray Rows
Delete Nested varray Command
Delete Entire Array
Nested Tables Conclusion
Modifying LOBs
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.
Background
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
Instructions
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.
Hints
To retrieve the contents of the varray column, use the
VALUE
operator.
Answer the following questions
What is the difference between the first and second SQL statements?
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.
