Type constructor expressions

A type constructor expression specifies a call to a constructor method. The parameters of a type constructor can be any expression. You can call a type constructor anywhere you can call a function.

Type constructor expression syntax

[ schema. ]type_name
([ expr [, expr ]... ])

If type_name is an object type, the expression must be an ordered list in which the type of the first parameter matches the type of the first attribute of the object type, the type of the second parameter matches the type of the second attribute of the object type, and so on. The total number of parameters of the constructor must match the total number of attributes of the object type.

If type_name is an array or nested table type, the expression list can contain zero or more parameters. Zero parameters means that you construct an empty collection. Otherwise, each parameter corresponds to an element value whose type is the element type of the collection.

Limitations

When you call a type constructor method from SQL, the number of specified parameters (expr) cannot exceed 999, even if the object type has more than 999 attributes.

Examples

CREATE TYPE cust_addr_typ
AS OBJECT
    (street_address    VARCHAR2(40),
     postal_code       VARCHAR2(10),
     city              VARCHAR2(30),
     state_province    VARCHAR2(10),
     country_id        CHAR(2));
/

CREATE TABLE customers
  (customer_id        NUMBER(6),
   cust_first_name    VARCHAR2(20) CONSTRAINT cust_fname_nn NOT NULL,
   cust_last_name     VARCHAR2(20) CONSTRAINT cust_lname_nn NOT NULL,
   cust_address       VARCHAR2(50),
   cust_phoneno       VARCHAR2(20)
);

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