Postgres Plus Advanced Server Oracle Compatibility Developer's Guide :

Previous PageTable Of ContentsNext Page

3.3.25 CREATE TYPE

Name

CREATE TYPE -- define a new user-defined type

Synopsis

CREATE [ OR REPLACE ] TYPE name { IS | AS } OBJECT
  ({ attribute { datatype | objtype | collecttype } } [, ...])

CREATE [ OR REPLACE ] TYPE name { IS | AS } TABLE OF
  { datatype | objtype | collecttype }

CREATE [ OR REPLACE ] TYPE name { IS | AS }
  { VARRAY | VARYING ARRAY } (maxsize) OF { datatype | objtype }

Description

CREATE TYPE defines a new user-defined data type. The types that can be created are an object type, a nested table type, or a varray type. (Nested table and varray types belong to the category of types known as collections. See Section 4.10 for information on collections.) CREATE OR REPLACE TYPE will either create a new type definition, or replace an existing type definition.

If a schema name is included, then the type is created in the specified schema, otherwise it is created in the current schema. The name of the new type must not match any existing type in the same schema.

To update the definition of an existing type, use CREATE OR REPLACE TYPE.

The user that creates the type becomes the owner of the type.

See Section 4.10.2 for more information on nested table types. See Section 4.10.3 for more information on varray types. See Chapter 8 for more information on object types.

Parameters

name

The name (optionally schema-qualified) of the type to create.

attribute

The name of an attribute in the object type.

datatype

The data type that defines an attribute of the object type or the elements of the collection type that is being created.

objtype

The name of an object type that defines an attribute of the object type or the elements of the collection type that is being created.

collecttype

The name of a collection type that defines an attribute of the object type or the elements of the collection type that is being created.

maxsize

The maximum number of elements permitted in the varray.

Examples

Create object type, addr_obj_typ.

CREATE OR REPLACE TYPE addr_obj_typ AS OBJECT (
    street          VARCHAR2(30),
    city            VARCHAR2(20),
    state           CHAR(2),
    zip             NUMBER(5)
);

Create a nested table type, budget_tbl_typ, of data type, NUMBER(8,2).

CREATE OR REPLACE TYPE budget_tbl_typ IS TABLE OF NUMBER(8,2);

See Also

DROP TYPE

Previous PageTable Of ContentsNext Page