Inserting a Customer with a Varray - Exercise

Objective: Insert an object-table row containing an address object and a populated phone-number varray.

Example schema and prerequisites

For this exercise, assume CUSTOMER_OBJ_TABLE is an object table of CUSTOMER_TYPE. Its attributes are declared in this order:

CUST_ID (NUMBER)
FIRST_NAME and LAST_NAME (VARCHAR2)
FULL_ADDRESS (ADDRESS_TYPE)
PHONE_LIST (PHONE_ARRAY)
LAST_UPDATED (DATE)
LAST_UPDATED_BY (VARCHAR2)

ADDRESS_TYPE has five character attributes, in order: address line 1, address line 2, city, state, and postal code. PHONE_ARRAY is a SQL type defined as VARRAY(5) OF VARCHAR2(20). All character attributes can hold the supplied values. Customer ID 56 is unused, and the values satisfy the table's constraints. These are explicit example definitions; adapt names to your practice schema if needed.

You can write your answer without a database. To test it, use your assigned account and service in a disposable practice schema with these definitions and sufficient privileges. Disable autocommit, inspect the results, and use ROLLBACK to undo uncommitted practice changes.

Tasks

Write one INSERT using CUSTOMER_TYPE, ADDRESS_TYPE, and PHONE_ARRAY. Insert customer 56, Norma Grey, at 100 South East Main, with NULL for address line 2, Burbank, CA, postal code 65443. Store the two phone numbers 1-800-876-3243 and 1-714-785-8763, in that order. Use December 22, 2025 as the last-updated date and JOAN as the updater.
Write a SELECT that displays the inserted customer and both phone values. Explain why this inserts a customer row with a complete varray value, rather than inserting individual elements through TABLE().
Can an INSERT name only some columns? Show an alternative INSERT for the same customer supplying only CUST_ID, FIRST_NAME, and LAST_NAME. For this alternative, assume the omitted attributes permit NULL and have no defaults. Explain what happens if an omitted column is required and has no default. Treat this as an alternative, not a second insert of the same ID.

Hints: Constructor arguments follow the type's attribute order. Use an ANSI DATE literal to avoid dependence on session date formats.

10-point self-assessment

5 points for SQL and verification: target table and customer constructor (1), scalar values and date literal (1), address constructor (1), populated varray constructor (1), and a SELECT that verifies customer 56 and both phone values (1).
5 points for explanation: distinguish the parent row from its varray (1), explain the three constructors (1), provide a valid column-list alternative (1), explain omitted defaults or NULL values (1), and note that required columns and other constraints must still be satisfied (1).

Submit your exercise

Submit your SQL, verification query, and explanations together. The result page displays a model answer; it does not execute SQL or calculate a score.