Type Constructor Expressions

A type constructor expression specifies a call to a type constructor. The argument to the type constructor is any expression.

type_constructor_expression::=

Text description of type_constructor_expression

The NEW keyword applies to constructors for object types but not for collection types. It instructs Oracle to construct a new object by invoking an appropriate constructor. The use of the NEW keyword is optional, but it is good practice to specify it.

If type_name is an object type, then the expressions must be an ordered list, where the first argument is a value whose type matches the first attribute of the object type, the second argument is a value whose type matches the second attribute of the object type, and so on. The total number of arguments to the constructor must match the total number of attributes of the object type.

If type_name is a varray or nested table type, then the expression list can contain zero or more arguments. Zero arguments implies construction of an empty collection. Otherwise, each argument corresponds to an element value whose type is the element type of the collection type.

Type constructors can be invoked anywhere functions are invoked. They also have similar restrictions, such as a limit on the maximum number of arguments.

Expression Example

This example uses the cust_address_typ type in the sample oe schema to show the use of an expression in the call to a type constructor (the PL/SQL is shown in italics):

CREATE TYPE address_book_t AS TABLE OF cust_address_typ;
DECLARE
 myaddr cust_address_typ := cust_address_typ(
   '500 Oracle Parkway', 94065, 'Redwood Shores', 'CA','USA');
 alladdr address_book_t := address_book_t();
BEGIN
 INSERT INTO customers VALUES (
    666999, 'Smith', 'Joe', myaddr, NULL, NULL, NULL, NULL,
    NULL, NULL, NULL);
END;
/

Subquery Example

This example uses the warehouse_typ type in the sample schema oe to illustrate the use of a subquery in the call to the type constructor.

CREATE TABLE warehouse_tab OF warehouse_typ;

INSERT INTO warehouse_tab 
   VALUES (warehouse_typ(101, 'new_wh', 201));

CREATE TYPE facility_typ AS OBJECT (
   facility_id NUMBER,
   warehouse_ref REF warehouse_typ);
   
CREATE TABLE buildings (b_id NUMBER, building facility_typ);

INSERT INTO buildings VALUES (10, facility_typ(102, 
   (SELECT REF(w) FROM warehouse_tab w 
      WHERE warehouse_name = 'new_wh')));

SELECT b.b_id, b.building.facility_id "FAC_ID",
   DEREF(b.building.warehouse_ref) "WH" FROM buildings b;

      B_ID     FAC_ID WH(WAREHOUSE_ID, WAREHOUSE_NAME, LOCATION_ID)
---------- ---------- ---------------------------------------------
        10        102 WAREHOUSE_TYP(101, 'new_wh', 201)