CREATE TYPE v17

Suggest edits

Name

CREATE TYPE — Define a new user-defined type, The new type can be an object type, a collection type (a nested table type or a varray type), or a composite type.

Synopsis

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>

Where 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

CREATE TYPE defines a new data type. The types that you can create are an object type, a nested table type, a varray type, or a composite type. Nested table and varray types belong to the category of types known as collections.

Composite types aren't compatible with Oracle databases. However, SPL programs can access composite types along with other types.

Parameters

name

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

DEFINER | CURRENT_USER

Specifies whether to use the privileges of the object type owner (DEFINER) or of the current user executing a method in the object type (CURRENT_USER) to determine whether access is allowed to database objects referenced in the object type. DEFINER is the default.

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's 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's 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's being created.

FINAL

NOT FINAL

For an object type, specifies whether you can derive a subtype from the object type. FINAL (you can't derive a subtype from the object type) is the default.

For method_spec, specifies whether you can override the method in a subtype. NOT FINAL (you can override the method in a subtype) is the default.

INSTANTIABLE

NOT INSTANTIABLE

For an object type, specifies whether you can create an object instance of this object type. INSTANTIABLE (you can create an instance of this object type) is the default. If you specify NOT INSTANTIABLE, then you must specify NOT FINAL as well. If method_spec for any method in the object type contains the NOT INSTANTIABLE qualifier, then the object type must be defined with NOT INSTANTIABLE and NOT FINAL following the closing parenthesis of the object type specification.

For method_spec, specifies whether the object type definition provides an implementation for the method. INSTANTIABLE (the CREATE TYPE BODY command for the object type provides the implementation of the method) is the default. If you specify NOT INSTANTIABLE, then the CREATE TYPE BODY command for the object type can't contain the implementation of the method.

OVERRIDING

If you spcify OVERRIDING, method_spec overrides an identically named method with the same number of identically named method arguments with the same data types, in the same order, and the same return type (if the method is a function) as defined in a supertype.

MEMBER

STATIC

Specify MEMBER if the subprogram operates on an object instance. Specify STATIC if the subprogram operates independently of any particular object instance.

proc_name

The name of the procedure to create.

SELF [ IN | IN OUT ] name

For a member method, there is an implicit, built-in parameter named SELF whose data type is that of the object type being defined. SELF refers to the object instance that's currently invoking the method. You can explicitly declare SELF 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 you don't explicitly declare SELF, its parameter mode defaults to IN OUT for member 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’s arguments. The argument types can be a base data type or a user-defined type such as a nested table or an object type. Don't specify a length for any base type. For example, specify VARCHAR2, not VARCHAR2(10).

DEFAULT value

Supplies a default value for an input argument if you don't supply one in the method call. You can't specify DEFAULT for arguments with modes IN OUT or OUT.

func_name

The name of the function to create.

rettype

The return data type, which can be any of the types listed for argtype. Don't specify a length for rettype.

maxsize

The maximum number of elements permitted in the varray.

Examples

Creating an object type

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 object type emp_obj_typ that includes 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 object type dept_obj_typ that includes 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 budget_tbl_typ of data type NUMBER(8,2):

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

Creating and using a composite type

This example uses a composite type accessed from an anonymous block.

This example creates the composite type:

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

This code 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

This example uses a composite type accessed from a user-defined record type declared in a package body.

The following creates the composite type:

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

The following defines the package specification:

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

The following defines the package body:

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;

In the declaration of the TYPE emprec_typ IS RECORD data structure in the package body, the salhist field is defined with the SALHIST_TYP composite type 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 that includes the field salhist defined with the SALHIST_TYP composite type.

This example invokes the package procedure that loads the array from a join of the emp and jobhist tables and then displays the array content:

EXEC emp_salhist.fetch_emp(7788);
Output
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

EDB-SPL Procedure successfully completed

Notes

For packages only, you can include a composite type in a user-defined record type declared with the TYPE IS RECORD statement in the package specification or package body. This nested structure isn't permitted in other SPL programs such as functions, procedures, and triggers.

In the CREATE TYPE command, if you include a schema name, then the type is created in the specified schema. Otherwise it's created in the current schema. The name of the new type can't match any existing type in the same schema unless you want to update the definition of an existing type. In that case, use CREATE OR REPLACE TYPE.

You can't use the OR REPLACE option to add, delete, or modify the attributes of an existing object type. Use the DROP TYPE command to first delete the existing object type. You can use the OR REPLACE option to add, delete, or modify the methods in an existing object type.

You can use the PostgreSQL form of the ALTER TYPE ALTER ATTRIBUTE command to change the data type of an attribute in an existing object type. However, the ALTER TYPE command can't add or delete attributes in the object type.

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

See also

CREATE TYPE BODY, DROP TYPE

← Prev

CREATE TRIGGER

↑ Up

Compatible SQL commands

Next →

CREATE TYPE BODY