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

If type_name is an object type, then the expression list 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.

If type_name is an object type, a varray, or a nested table type, the maximum number of arguments it can contain is 1000 minus some overhead.

Expression Example

This example shows the use of an expression in the call to a type constructor (the PL/SQL is shown in italics):

CREATE TYPE address_t AS OBJECT 
  (no NUMBER, street CHAR(31), city CHAR(21), state CHAR(3), zip NUMBER); 
CREATE TYPE address_book_t AS TABLE OF address_t;
DECLARE 
  /* Object Type variable initialized by Object Type Constructor */ 
  myaddr address_t = address_t(500, 'Oracle Parkway', 'Redwood Shores', 'CA',  94065);
  /* nested table variable initialized to an empty table by a constructor*/ 
  alladdr address_book_t = address_book_t(); 
BEGIN
  /* below is an example of a nested table constructor with two elements
     specified, where each element is specified as an object type constructor. */ 
  insert into employee values (666999, address_book_t(address_t(500,
     'Oracle  Parkway', 'Redwood Shores', 'CA', 94065), address_t(400,
     'Mission Street', 'Fremont', 'CA', 94555))); 
END; 

Subquery Example

This example illustrates the use of a subquery in the call to the type constructor.

CREATE TYPE employee AS OBJECT ( 
   empno NUMBER, 
   ename VARCHAR2(20)); 

CREATE TABLE emptbl of EMPLOYEE; 

INSERT INTO emptbl VALUES(7377, 'JOHN'); 

CREATE TYPE project AS OBJECT ( 
   pname VARCHAR2(25), 
   empref REF employee); 

CREATE TABLE depttbl (dno number, proj project); 

INSERT INTO depttbl values(10, project('SQL Extensions',  
                                       (SELECT REF(p) FROM emptbl p 
                                        WHERE ename='JOHN')));