CREATE 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.

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.

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

Parameters

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