PL/SQL   «Prev  Next»

Modifying Nested Tables and Varrays - Quiz

Select one answer for each question. There are five questions, worth one point each; unanswered or invalid answers receive no credit.
1. Assume SALE_HEADER.detail_nest is a nested-table column of type DETAIL_TABLE; there is no column named detail_table. Its element attributes, in order, are product_ref (a compatible product REF), item_qty, and detail_amount. Sale 350 has an initialized collection, product 200 exists, and both IDs are unique. Which correction is needed?
INSERT INTO TABLE (
    SELECT s.detail_table
    FROM sale_header s
    WHERE s.sale_id = 350
)
SELECT REF(p), 1, 55
FROM product_obj_table p
WHERE p.product_id = 200;

Please select the best answer.
  A. No correction is needed; a collection type name can be used as a column reference.
  B. Replace REF(p) with the scalar product ID.
  C. Select s.detail_nest, the stored collection column, instead of s.detail_table.
  D. Remove the parent-row filter because nested-table inserts cannot target a single parent.

2. Assume CUSTOMER_OBJ_TABLE.phone_array is a varray column declared as PHONE_ARRAY_TYPE, where PHONE_ARRAY_TYPE is VARRAY(5) OF VARCHAR2(20). Customer 25 exists. What is wrong with this statement?
UPDATE customer_obj_table
SET phone_array = ('1-800-234-5678', '1-888-987-6543')
WHERE cust_id = 25;

Please select the best answer.
  A. The left side of SET must be the type name phone_array_type instead of the column name.
  B. Nothing; a parenthesized list is automatically converted to the declared varray type.
  C. Oracle prohibits replacement of an existing varray column.
  D. The right side needs a varray value, such as phone_array_type('1-800-234-5678', '1-888-987-6543').

3. Assume sale 350 has an initialized detail_nest nested table whose elements include item_qty and detail_amount. The intention is to delete its elements with item_qty = 20. Why is the following statement incorrect?
DELETE FROM TABLE (
    SELECT d.detail_nest
    FROM sale_header d
    WHERE d.sale_id = 350
) p
WHERE d.sale_id = 20;

Please select the best answer.
  A. Oracle cannot delete individual elements from a stored nested table.
  B. Alias d is scoped to the subquery; the outer predicate should be p.item_qty = 20 for the requested element filter.
  C. The inner WHERE must select every sale rather than one parent row.
  D. The DELETE removes the parent sale row, so the parent table must be dropped first.

4. Assume phone_array is a column of type PHONE_ARRAY_TYPE, defined as VARRAY(5) OF VARCHAR2(20). Customer 26 exists exactly once, its varray is initialized and contains fewer than five elements, and the session has the required privileges. What does this block do?
DECLARE
    v_phones phone_array_type;
BEGIN
    SELECT c.phone_array INTO v_phones
    FROM customer_obj_table c
    WHERE c.cust_id = 26
    FOR UPDATE;

    v_phones.EXTEND;
    v_phones(v_phones.LAST) := '1-800-336-6199';

    UPDATE customer_obj_table
    SET phone_array = v_phones
    WHERE cust_id = 26;
END;
/

Please select the best answer.
  A. It appends one phone number in a PL/SQL variable and writes the complete modified varray back to customer 26; transaction completion remains with the caller.
  B. It appends the number directly to the stored varray without replacing the collection value.
  C. It fails because EXTEND is supported only for nested tables, not varrays.
  D. It commits automatically when the PL/SQL block reaches END.

5. Which statement correctly distinguishes SQL DML on stored nested tables from SQL DML on stored varrays in Oracle AI Database 26ai?
Please select the best answer.
  A. Neither collection type supports SQL updates once it has been stored.
  B. Nested tables support element-level INSERT, UPDATE, and DELETE through TABLE(); stored varrays are modified by replacing the whole collection value.
  C. Varrays support element-level SQL DELETE through TABLE(), but nested tables do not.
  D. Changing an element of a fetched PL/SQL collection variable automatically updates the database column.