The CREATE TYPE statement creates a user-defined type (UDT). A UDT is a serializable Java class whose instances are stored in columns. The class must implement the java.io.Serializable interface.
The type name is composed of an optional schemaName and a SQL92Identifier. If a schemaName is not provided, the current schema is the default schema. If a qualified type name is specified, the schema name cannot begin with SYS.
If the Java class does not implement java.io.Serializable, or if it is
not public and visible on the classpath,
A UDT cannot be cast explicitly to any other type, and no other type can be cast to a UDT.
A UDT has no ordering. This means that you cannot compare and sort UDTs. You
cannot use them in expressions involving the
You can use subtypes in UDTs. That is, if you use the CREATE TYPE statement to bind a class named C to a UDT, you can populate that UDT value with an instance of any subclass of C.
You can create tables and views with columns that have UDTs. For example:
Although UDTs have no natural order, you can use generated columns to provide useful sort orders:
You can use factory functions to construct UDTs. For example:
Once a UDT column has been populated, you can use it in other INSERT and UPDATE statements. For example:
Using functions, you can access fields inside UDTs in a SELECT statement:
You can use JDBC API setObject() and getObject() methods to store and retrieve values of UDTs. For example: