How To Create Type In Oracle

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.