Creating User Defined Types in Oracle

You can roll your own types in Oracle (and Postgres and mysql also) that include member variables and procedures though the procedures won't be portable. Here is how to create and use a type that has no procedures:

create or replace type MYUDT as object
(
  -- Author  : HELMUT
  -- Created : 8/20/2008 10:36:10 AM
  -- Purpose : example user defined type
 
  -- Attributes
  SILLY NUMBER,
  FOO VARCHAR2(200)
 
  -- Member functions and procedures
 
)

Create a table like normal:

-- Create table
create table DUMMYTABLE
(
  USERTYPECOLUMN MYUDT
)
;

You can then insert into the table like so:

INSERT INTO DUMMYTABLE (USERTYPECOLUMN) VALUES ( MYUDT(10,'Obamarama') );

and read from the table like so:

SELECT USERTYPECOLUMN.FOO FROM DUMMYTABLE WHERE USERTYPECOLUMN.SILLY > 5;