René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

create type [Oracle SQL]

The create type creates a specification for either

Object types

create or replace type base-type-name is object (....);
create or replace type derived-type-name under base-type-name(....);

create or replace type .... not final
create or replace type .... final;

create or replace type .... not instantiable;
create or replace type .... instantiable;

Varray type

create or replace some_type_name as varray (n) of some_other_type_name;

Nested type

create or replace some_type_name as table of some_other_type_name;

Examples

set feedback off;

create or replace type A as object (
  x_ number, 
  y_ varchar2(10),
  member procedure to_upper
);
/

create type body A is
  member procedure to_upper is 
  begin
    y_ := upper(y_);
  end;
end;
/


create or replace type AList as table of A;
/

create or replace type B as object (
  l_ char(2),
  a_ AList,
  member procedure to_upper
);
/

create type body B is
  member procedure to_upper is
    i number;
  begin
    i := a_.first;
    while i is not null loop
      a_(i).to_upper; 
      i:=a_.next(i);
    end loop;
  end;
end;
/

declare
  b1 B;
  b2 B;
  b3 B;
begin
  b1 := B('fr',AList(A('1','un'  ),A('2','deux')));
  b2 := B('de',AList(A('1','eins'),A('2','zwei')));
  b3 := B('en',AList(A('1','one' ),A('2','two' )));


  b2.to_upper();

  dbms_output.put_line(b2.a_(2).y_);
end;
/


drop type B;

drop type AList;

drop type A;

Requirements

In order to create user defined types the objects option is required.