Type Constructor Expressions

A type constructor expression specifies a call to a constructor method. The argument to the type constructor is any expression. Type constructors can be invoked anywhere functions are invoked.

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.

Restriction on Type Constructor Invocation In an invocation of a type constructor method, the number of parameters (expr) specified cannot exceed 999, even if the object type has more than 999 attributes. This limitation applies only when the constructor is called from SQL. For calls from PL/SQL, the PL/SQL limitations apply.

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 constructor method (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, 'Joe', 'Smith', myaddr, NULL, NULL, NULL, NULL, 
      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 constructor method.

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)