CREATE TYPE|V4.2.5| docs|Distributed Database

CREATE TYPE

Last Updated：2026-02-11 07:43:39 Updated

CREATE TYPE is used to define a new user-defined type (UDT). You can use CREATE TYPE to specify the name and attributes, methods, and other properties of the type.

Applicability

This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.

CREATE TYPE is used to create or replace the following specifications:

Abstract data types (ADTs)
- For example, you can create an ADT to represent complex data that has structured properties and may need to be encapsulated as methods. For example, you can create an ADT to represent an address or an employee.
Independent varray types
- For example, you can define a varray type to represent an array with a fixed upper bound that needs to be stored and retrieved as a whole.
Independent nested table types
- For example, you can use a nested table type to store and access an unlimited number of data items of the same type, where the order of the data items in the collection is not important.
Incomplete object types

An incomplete type is created by a forward type definition. It is called incomplete because it has a name but no attributes or methods. It can be referenced by other types, allowing for custom mutually referenced types. However, you must create the complete type before you can use it as a column type for a table, object column, or nested table column. For example, incomplete types are typically used when you anticipate mutual references between types. You can declare an incomplete type as a placeholder and then define it further after the referenced type is fully defined.

Note

Object types support non-deterministic default values for their parameters.

CREATE TYPE BODY is used to declare the implementation code of the type. That is, if the type you create contains methods, you must use the CREATE TYPE BODY statement to provide the implementation code for these methods.

Note

If the type specification you create declares only attributes and not methods, you do not need to create a body.
You can use CREATE TYPE to create nested tables and varray types, but not associative arrays. In PL blocks or packages, you can define the three types of collection types.

Prerequisites

To create a type in the current schema, you must have the CREATE TYPE system privilege. To create a type in another user's schema, you must have the CREATE ANY TYPE system privilege. You can obtain these privileges directly or through roles.

The owner of the type must be explicitly granted the EXECUTE object privilege on all other types referenced in the type definition, or the owner must be granted the EXECUTE ANY TYPE system privilege. These privileges cannot be obtained through roles.

If the type owner intends to grant access to other users, the owner must be granted the EXECUTE object privilege on the type with the GRANT OPTION clause, or the owner must be granted the EXECUTE ANY TYPE system privilege with the ADMIN OPTION clause. Otherwise, the type owner does not have sufficient privileges to grant access to other users.

In the current version of OceanBase Database, when you create a new table using the CREATE TABLE statement, you can define your own object types (also known as user-defined types - UDTs). However, these custom types cannot be used as the data types for columns in database tables. That is, you cannot directly use these UDTs as part of the table structure, such as creating a column with a type specified as your custom object type.

Nevertheless, even though you cannot use user-defined object types (UDTs) when creating tables, you can still use custom object types to define variables, parameters, and return types in PL/SQL programs.

Syntax

Note

This section only contains the format of some key syntax nodes.

create_type_stmt is specified as follows:
plsql_type_spec_source is specified as follows:
object_type_def is specified as follows:
attr_and_element_spec is specified as follows:
element_spec is specified as follows:
el_element_spec_list_cc is specified as follows:
element_spec_long is specified as follows:
inheritance_final_instantiable_clause is specified as follows:
inheritance_overriding_instantiable_clause is specified as follows:
subprogram_spec is specified as follows:
proc_or_func_spec is specified as follows:

Semantics

Syntax	Keywords or syntax nodes	Description
create_type_stmt	OR REPLACE	Recreates the type (if it exists) and then recompiles it. Users who were granted privileges before the type was redefined can still access the type without needing to be granted privileges again. If any function-based indexes depend on this type, the database will mark the indexes as DISABLED.
plsql_type_spec_source	pl_schema_name	The name of the schema that contains the type. The default value is your schema.
plsql_type_spec_source	TYPE	The name of the ADT, nested table type, or VARRAY type.
plsql_type_spec_source	FORCE	If the type_name exists and has type dependencies but no table dependencies, `FORCE` forces the statement to replace the type. (If the type_name has table dependencies, the statement will fail regardless of whether `FORCE` is used.) Note: If type t1 has type dependency t2, and type t2 has table dependencies, then type t1 also has table dependencies.
plsql_type_spec_source	varray_type_def	Creates the type as an ordered collection of elements, where each element has the same data type.
plsql_type_spec_source	nested_table_type_def	Creates a nested table of the specified type, named `datatype`.
object_type_def	object_type_def	Creates an ADT. Variables that form the data structure are called attributes (Attributes). Member subprograms that define the behavior of the ADT are called methods (Methods). The keyword `AS OBJECT` is required when creating an ADT.
object_type_def	AS OBJECT	Creates a schema-level ADT. This type of ADT is sometimes referred to as a root ADT.
attr_and_element_spec	element_spec	Specifies each attribute of the ADT.
subprogram_spec	subprogram_spec	Declares a subprogram.
subprogram_spec	member_or_static	MEMBER specifies a function or stored procedure associated with the ADT. These are called member functions. Typically, a member method has an implicit first parameter, called SELF, in its body. This parameter represents the object on which the method is called. STATIC specifies a function or stored procedure associated with the ADT. Unlike member methods, static methods do not have an implicit parameter. You cannot reference SELF in the body. They are typically called in the form `type_name.method()`.
subprogram_spec	proc_or_func_spec	Specifies the parameters and data types of a stored procedure or function. If the subprogram does not contain the definition of a stored procedure or function, you must define it using the `CREATE TYPE BODY` command. If you are creating a subtype, the name of the stored procedure or function cannot be the same as the name of any attribute declared in the supertype chain, regardless of whether it is inherited.

Examples

Create type examples

Example 1: Create an object type

Create an object type named person_typ with two attributes: name and age, and a member function get_age to return the object's age.

CREATE TYPE person_typ AS OBJECT (
  name VARCHAR2(50),
  age NUMBER,
  MEMBER FUNCTION get_age RETURN NUMBER);

Example 2: Create a VARRAY type

Create a VARRAY type named number_list that can store up to 10 numbers.

CREATE TYPE number_list AS VARRAY(10) OF NUMBER;

Complete usage example

We will use the creation of an object type to demonstrate a complete usage example.

Step 1: Create an object type

First, define the object type and its member functions. Here, we assume the member function get_age simply returns the object's age.

CREATE OR REPLACE TYPE person_typ AS OBJECT (
  name VARCHAR2(50),
  age NUMBER,
  MEMBER FUNCTION get_age RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY person_typ AS
  MEMBER FUNCTION get_age RETURN NUMBER IS
  BEGIN
    RETURN self.age;
  END get_age;
END;
/

Step 2: Use the object type in PL/SQL

Create a table named people.

CREATE TABLE people (person_id NUMBER, person_name VARCHAR2(50), person_age NUMBER);

In your PL/SQL program, declare variables of the person_typ type to work with these data:

DECLARE
  v_person person_typ := person_typ('Alice', 30);
BEGIN
  -- Insert data into the people table
  INSERT INTO people (person_id, person_name, person_age) VALUES (1, v_person.name, v_person.age);
  COMMIT;
END;
/