Querying Object Tables
Object Oriented Terms
Collections Varrays Oracle
Querying Nested Tables
Querying Complex SQL Object
SQL Query Example
Using Oracle Objects
Querying Related Tables
Understanding Related Object Tables
Analyzing Dangling References
Writing deref Queries
Oracle deref Syntax
Creating PL/SQL Block
Querying Nested Tables
Flattening nested Table
Flattening nested Table Example
Simple varray Query
Query to Flatten varray
Query Flattens Nested Table
Procedure retrieves Nested Table
Varrays Nested Table Conclusion
Object Table Modification
Insert into Table
SQL Insert Rows
Write SQL to Update Object Table
Subquery in Update
Delete Rows from object Table
DML Statements Conclusion
Updating Rows Example
Modifying Nested Tables
Insert Rows into Nested Table
Delete Command for Nested varray
Updating Varray Rows
inserting Data in Nested Table(Example)
Varray insert Command
Preferrable Method to Query Varray - Exercise
Write two queries using techniques learned in this module
Write queries to select varray data from object tables.
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
by using two different techniques to select the varray data stored within the column
. The first technique employs a simple
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
of the table alias.
For the first SQL statement, use a simple
clause. For the second SQL statement, include two
statements, the first for retrieving the
of the table alias and the second for selecting the varray column from the object table with a
To retrieve the contents of the varray column, use the
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
to submit the exercise.
Remember that you must submit all your responses to this exercise at once.