CREATE TYPE - User-Defined Types - SQL Server to Oracle Migration

In SQL Server you can use CREATE TYPE statement to create a user-defined type (UDT) as an alias for a system data type.

You can optionally specify DEFAULT, NOT NULL and CHECK constraint. The UDT can be used in a primary or unique constraint in SQL Server.

In Oracle you can also use CREATE TYPE statement to create a user-defined type, but it is create as an object, not alias. You have to use specific syntax to assign values to UDT or specify DEFAULT in CREATE TABLE, and the UDT cannot be used in a primary or unique key in Oracle.

Data Type Alias in SQL Server

Let's create user-defined data types in SQL Server as follows:

SQL Server:

  -- Aliases for INT NOT NULL and VARCHAR(100)
  CREATE TYPE id FROM INT NOT NULL;
  CREATE TYPE location FROM VARCHAR(100);

Now you can use them in a table and insert values the same way you can use the system data types:

SQL Server:

  CREATE TABLE person
  (
     id id PRIMARY KEY,
     location location DEFAULT 'n/a'
  );
 
  INSERT INTO person VALUES (1, 'Paris, France');
  # 1 row(s) affected

User-Defined Data Types in Oracle

Now let's create the same user-defined data types in Oracle:

Oracle:

  -- Types are created as objects now
  CREATE TYPE id AS OBJECT (id NUMBER(10));
  /
  CREATE TYPE location AS OBJECT (location VARCHAR2(100));
  /

Note that Oracle does not allow you to use a user-defined datatype in a primary or unique key, so you have to use a system type instead :

Oracle:

  CREATE TABLE person
  (
     id id PRIMARY KEY,
     location location
  );
  # ERROR at line 3:
  # ORA-02329: column of datatype ADT cannot be unique or a primary key
 
  -- Use the system type for ID column, and specific syntax for DEFAULT
  CREATE TABLE person
  (
     id NUMBER(10) NOT NULL PRIMARY KEY,
     location location DEFAULT location('n/a')
  );
  # Table created.

Then you have also to change the syntax of INSERT statements for UDT columns:

Oracle:

  INSERT INTO person VALUES (1, 'Paris, France');
  # ERROR at line 1:
  # ORA-00932: inconsistent datatypes: expected ORA.LOCATION got CHAR
 
  -- You have to use UDT(value)
  INSERT INTO person VALUES (1, location('Paris, France'));
  # 1 row created

Database and SQL Migration Tools

About SQLines

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - October 2013.

You could leave a comment if you were logged in.