A type constructor expression specifies a call to a constructor method. The parameters of a type constructor function can be any expression. A type constructor function can be called anywhere a function is called.
Syntax
[ schema. ]type_name
([ expr [, expr ]... ])
If type_name is an object type, the expression must be an ordered list where the first parameter type matches the first attribute of the object type, the second parameter type matches the second attribute of the object type, and so on. The total number of parameters in the constructor function 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 indicate the construction of an empty set. Otherwise, each parameter corresponds to one element value whose type is an element type in the set.
Limitations
When a type constructor method is called 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;
/
