PL/SQL   «Prev  Next»

Updating Varrays and Nested Tables - Exercise

Objective: Replace a stored varray and update selected elements of a stored nested table using Oracle SQL.

Background and prerequisites

This exercise is worth 10 points. Use the following schema assumptions so that column names and collection type names are unambiguous:

  • CUSTOMER_OBJ_TABLE contains CUST_ID and a PHONE_LIST column. The column's SQL collection type is PHONE_ARRAY, defined as VARRAY(5) OF VARCHAR2(20).
  • SALE_HEADER contains SALE_ID and an initialized nested-table column named DETAIL_NEST. Its object elements include numeric attributes ITEM_QTY and DETAIL_AMOUNT.
  • Customer 29 and sale 34 each exist exactly once. Assume the requested updates are permitted by the schema's constraints and privileges.

These names define this exercise's example schema. If your practice schema uses a different collection type name, use its declared type constructor. You can write your answer without a database. To execute it, connect to your own practice database with your assigned account and service name; no shared username or password is required here.

Instructions

  1. Write an UPDATE that replaces customer 29's complete PHONE_LIST with exactly '1-800-444-5623' and '1-310-345-8899', in that order.
  2. Write an UPDATE that sets ITEM_QTY to 453 for every element of sale 34's DETAIL_NEST whose DETAIL_AMOUNT equals 3.50. Leave other elements and other sales unchanged.

If testing the statements, use disposable practice data with autocommit disabled. Inspect the affected rows and use ROLLBACK to undo the uncommitted practice changes.

Answer these questions

  1. Can existing data in a stored varray be corrected? Explain the difference between changing a PL/SQL varray variable and updating a stored varray column.
  2. Write a variation of the second statement that sets ITEM_QTY = 453 for all detail elements belonging to sale 34. Identify which predicate you remove and which you keep.

Hints

Use the varray type constructor to create the replacement value. For the nested table, use UPDATE TABLE(SELECT ...): the inner WHERE selects the sale, and the outer WHERE selects elements within that sale's collection.

10-point self-assessment

  • 3 points: Replace the correct customer's varray using the correct constructor and values.
  • 3 points: Update the correct nested table, quantity, and matching detail elements.
  • 2 points: Explain how varray data can be corrected and persisted.
  • 2 points: Show the all-details variation while retaining the sale 34 filter.

The result page provides a model answer for comparison. It does not execute submitted SQL or calculate an automatic score.

Submitting your exercise

Enter both required UPDATE statements and your answers to both questions, including the all-details variation. Select Submit to view your response alongside the solution.