Creates a user-defined type, which can be an object type, a collection type (a nested table type or a varray type), or a composite type.

Syntax

Object type

CREATE [ OR REPLACE ] TYPE name
  [ AUTHID { DEFINER | CURRENT_USER } ]
  { IS | AS } OBJECT
( { attribute { datatype | objtype | collecttype } }
    [, ...]
  [ method_spec ] [, ...]
) [ [ NOT ] { FINAL | INSTANTIABLE } ] ...

where method_spec is:

  [ [ NOT ] { FINAL | INSTANTIABLE } ] ...
  [ OVERRIDING ]
    subprogram_spec

subprogram_spec is:

  { MEMBER | STATIC }
  { PROCEDURE proc_name
      [ ( [ SELF [ IN | IN OUT ] name ]
          [, argname [ IN | IN OUT | OUT ] argtype
                     [ DEFAULT value ]
          ] ...)
      ]
  |
    FUNCTION func_name
      [ ( [ SELF [ IN | IN OUT ] name ]
          [, argname [ IN | IN OUT | OUT ] argtype
                     [ DEFAULT value ]
          ] ...)
      ]
    RETURN rettype
  }

Nested table type

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

Varray type

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

Composite type

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

Description

The CREATE TYPE command creates a user-defined data type. The types that can be created include object type, nested table type, varray type, and composite type. The nested table type and varray type belong to the collection type.

Composite types are incompatible with Oracle databases. However, composite types can be accessed through SPL programs, which is the same as other types described in this topic.

Note For packages only, a composite type can be included in a user-defined record type declared using the TYPE IS RECORD statement within the package specification or package body. Such nested structure is not allowed in other SPL programs such as functions, stored procedures, and triggers.

If you specify a schema name in the CREATE TYPE command, the type is created in the specified schema. Otherwise, the type is created in the current schema. The name of a new type must be different from an existing type in the same schema. If you want to update the definition of an existing type, you can use the CREATE OR REPLACE TYPE command.

Note
  • The OR REPLACE option cannot be used to add, delete, or modify the attributes of an existing object type. However, you can use the DROP TYPE command to delete the existing object type. The OR REPLACE option can be used to add, delete, or modify the methods in an existing object type.
  • The PostgreSQL form of the ALTER TYPE ALTER ATTRIBUTE command can be used to change the data type of an attribute in an existing object type. However, the ALTER TYPE command cannot add or delete attributes in the object type.

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

Parameters

Parameter Description
name The name of the type to be created. The name can be schema-qualified.
DEFINER | CURRENT_USER Specifies the privileges that are used to determine whether access is allowed to database objects referenced in the object type. DEFINER indicates the privileges of the object type owner. CURRENT_USER indicates the privileges of the current user executing a method in the object type. The default value is DEFINER.
attribute The name of an attribute in the object type or composite type.
datatype The data type that defines an attribute of the object type or composite 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.
FINAL | NOT FINAL
  • For an object type, this parameter specifies whether a subtype can be derived from the object type. The default value is FINAL, indicating that no subtype can be derived from the object type.
  • For method_spec, this parameter specifies whether to override the method in a subtype. The default value is NOT FINAL, indicating that the method can be overridden in a subtype.
INSTANTIABLE | NOT INSTANTIABLE
  • For an object type, this parameter specifies whether an instance of this object type can be created. The default value is INSTANTIABLE, indicating that an instance of this object type can be created. If you specify NOT INSTANTIABLE, you must also specify NOT FINAL. If method_spec for a method in the object type contains the NOT INSTANTIABLE qualifier, the object type must be defined with NOT INSTANTIABLE and NOT FINAL following the closing parenthesis of the object type specification.
  • For method_spec, this parameter specifies whether the object type definition provides an implementation for the method. The default value is INSTANTIABLE, indicating that the CREATE TYPE BODY command for the object type provides the implementation of the method. If you specify NOT INSTANTIABLE, the CREATE TYPE BODY command for the object type cannot contain the implementation of the method.
OVERRIDING If you specify OVERRIDING, method_spec overrides an identically named method with the same number of identically named method arguments. The arguments have the same data types, the same order, and the same return type (if the method is a function) as defined in a supertype.
MEMBER | STATIC If the subprogram runs on an object instance, specify MEMBER. If the subprogram runs independently of a particular object instance, specify STATIC.
proc_name The name of the stored procedure to be created.
SELF [ IN | IN OUT ] name For a member method, an implicit built-in parameter named SELF is available. The data type of this parameter is the data type of the object type being created. SELF references the object instance that is calling the method. SELF can be explicitly declared as an IN or IN OUT parameter in the parameter list. If explicitly declared, SELF must be the first parameter in the parameter list. If SELF is not explicitly declared, its parameter mode defaults to IN OUT for member stored procedures and IN for member functions.
argname The name of an argument. The argument is referenced by this name in the method body.
argtype The data types of the method arguments. The argument types can be a base data type or a user-defined type such as a nested table type or an object type. You cannot specify the length of a base data type. For example, you can specify VARCHAR2 rather than VARCHAR2(10).
DEFAULT value If no default value is specified in the method call, this parameter specifies a default value for an input argument. DEFAULT may not be specified for arguments with the IN OUT or OUT mode.
func_name The name of the function to be created.
rettype The return data type, which can be one of the types listed for the argtype parameter. For argtype, you cannot specify a length for rettype.
maxsize The maximum number of elements in the varray.

Examples

  • Create an object type

    Create an object type named 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 an object type named emp_obj_typ that contains a member method display_emp.

    CREATE OR REPLACE TYPE emp_obj_typ AS OBJECT (
        empno           NUMBER(4),
        ename           VARCHAR2(20),
        addr            ADDR_OBJ_TYP,
        MEMBER PROCEDURE display_emp (SELF IN OUT emp_obj_typ)
    );

    Create an object type named dept_obj_typ that contains a static method get_dname.

    CREATE OR REPLACE TYPE dept_obj_typ AS OBJECT (
        deptno          NUMBER(2),
        STATIC FUNCTION get_dname (p_deptno IN NUMBER) RETURN VARCHAR2,
        MEMBER PROCEDURE display_dept
    );
  • Creating a collection type

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

    CREATE OR REPLACE TYPE budget_tbl_typ IS TABLE OF NUMBER(8,2);
  • Create and use a composite type

    The following example shows how to access a composite type from an anonymous block.

    The composite type is created as follows:

    CREATE OR REPLACE TYPE emphist_typ AS (
        empno           NUMBER(4),
        ename           VARCHAR2(10),
        hiredate        DATE,
        job             VARCHAR2(9),
        sal             NUMBER(7,2)
    );

    The following example shows the anonymous block that accesses the composite type:

    DECLARE
        v_emphist       EMPHIST_TYP;
    BEGIN
        v_emphist.empno    := 9001;
        v_emphist.ename    := 'SMITH';
        v_emphist.hiredate := '01-AUG-17';
        v_emphist.job      := 'SALESMAN';
        v_emphist.sal      := 8000.00;
        DBMS_OUTPUT.PUT_LINE('   EMPNO: ' || v_emphist.empno);
        DBMS_OUTPUT.PUT_LINE('   ENAME: ' || v_emphist.ename);
        DBMS_OUTPUT.PUT_LINE('HIREDATE: ' || v_emphist.hiredate);
        DBMS_OUTPUT.PUT_LINE('     JOB: ' || v_emphist.job);
        DBMS_OUTPUT.PUT_LINE('     SAL: ' || v_emphist.sal);
    END;
    
       EMPNO: 9001
       ENAME: SMITH
    HIREDATE: 01-AUG-17 00:00:00
         JOB: SALESMAN
         SAL: 8000.00

    The following example shows how to access a composite type from a user-defined record type that is declared in a package body.

    The composite type is created as follows:

    CREATE OR REPLACE TYPE salhist_typ AS (
        startdate       DATE,
        job             VARCHAR2(9),
        sal             NUMBER(7,2)
    );

    The package specification is defined as follows:

    CREATE OR REPLACE PACKAGE emp_salhist
    IS
        PROCEDURE fetch_emp (
            p_empno     IN NUMBER
        );
    END;

    The package body is defined as follows:

    CREATE OR REPLACE PACKAGE BODY emp_salhist
    IS
        TYPE emprec_typ IS RECORD (
            empno       NUMBER(4),
            ename       VARCHAR(10),
            salhist     SALHIST_TYP
        );
        TYPE emp_arr_typ IS TABLE OF emprec_typ INDEX BY BINARY_INTEGER;
        emp_arr         emp_arr_typ;
    
        PROCEDURE fetch_emp (
            p_empno     IN NUMBER
        )
        IS
            CURSOR emp_cur IS SELECT e.empno, e.ename, h.startdate, h.job, h.sal
                FROM emp e, jobhist h
                WHERE e.empno = p_empno
                  AND e.empno = h.empno;
    
            i           INTEGER := 0;
        BEGIN
            DBMS_OUTPUT.PUT_LINE('EMPNO  ENAME    STARTDATE  JOB         ' ||
            'SAL        ');
            DBMS_OUTPUT.PUT_LINE('-----  -------  ---------  ---------   ' ||
            '---------');
    
            FOR r_emp IN emp_cur LOOP
                i := i + 1;
                emp_arr(i) := (r_emp.empno, r_emp.ename,
                    (r_emp.startdate, r_emp.job, r_emp.sal));
            END LOOP;
    
            FOR i IN 1 .. emp_arr.COUNT LOOP
                DBMS_OUTPUT.PUT_LINE(emp_arr(i).empno || '   ' ||
                    RPAD(emp_arr(i).ename,8) || ' ' ||
                    TO_CHAR(emp_arr(i).salhist.startdate,'DD-MON-YY') || '  ' ||
                    RPAD(emp_arr(i).salhist.job,10) || ' ' ||
                    TO_CHAR(emp_arr(i).salhist.sal,'99,999.99'));
            END LOOP;
        END;
    END;

    Note that in the declaration of the TYPE emprec_typ IS RECORD data structure in the package body, the salhist field is of the SALHIST_TYP composite type that is created by the CREATE TYPE salhist_typ statement.

    The associative array definition TYPE emp_arr_typ IS TABLE OF emprec_typ references the record type data structure emprec_typ. The data structure includes the salhist field of the SALHIST_TYP composite type.

    The following example shows how to call the package stored procedure that loads the array from a join of the emp and jobhist tables and displays the array content.

    EXEC emp_salhist.fetch_emp(7788);
    
    EMPNO  ENAME    STARTDATE  JOB         SAL
    -----  -------  ---------  ---------   ---------
    7788   SCOTT    19-APR-87  CLERK        1,000.00
    7788   SCOTT    13-APR-88  CLERK        1,040.00
    7788   SCOTT    05-MAY-90  ANALYST      3,000.00
    
    polar-spl Procedure successfully completed