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