CREATE DOMAIN Statement - Sybase SQL Anywhere to Oracle Migration

Sybase SQL Anywhere CREATE DOMAIN statement creates a user-defined data type with a range, optional DEFAULT, NOT NULL and CHECK constraint. A domain is an alias for a built-in data type.

Quick Example:

   -- Define a type
   CREATE DOMAIN addr VARCHAR(90);
 
   -- Use it in a table
   CREATE TABLE location (address addr);
 
   -- Insert data into user-defined type (no changes in INSERT syntax)
   INSERT INTO location VALUES ('Kansas City');

See CREATE DOMAIN statement for more details.

Conversion to Oracle

Conversion summary:

denotes syntax or functional differences, or more strict restrictions
no syntax conversion required

	Sybase SQL Anywhere	Oracle
Statement	CREATE DOMAIN	CREATE TYPE
Data Types	Data type mapping is required. More...
Use in CREATE TABLE	column_name type_name
Use in INSERT	column_value	type_name(column_value)

Last Update: Sybase SQL Anywhere 12.0 and Oracle 11g

In Oracle, you can use CREATE TYPE statement to create a user-defined data type.

Oracle:

   -- Define a type
   CREATE TYPE addr AS OBJECT (addr VARCHAR2(90));
   /

Note that CREATE TYPE defines an object, not an alias for a built-in data type. This means that you have to specify not only the type name but field name as well. In our example, there is a single field addr.

Besides the statement syntax, you also need to change data types, see Data Type Mapping.

Then you can use the type in a table, and the syntax is the same as in Sybase SQL Anywhere:

Oracle:

   -- Use addr type in a table
   CREATE TABLE location (address addr);

To insert data into a type in Oracle, you have to explicitly specify the type name, so Sybase SQL Anywhere and Oracle INSERT syntax is different:

Oracle:

    -- You cannot insert data into UDT using regular syntax
    INSERT INTO location VALUES ('Kansas City');
    -- ERROR at line 1:
    -- ORA-00932: inconsistent datatypes: expected ORA.ADDR got CHAR
 
    -- Insert data into user-defined type specifying type name in VALUES list
    INSERT INTO location VALUES (addr('Kansas City'));
    -- 1 row created.

Conversion Examples

Converting CREATE DOMAIN from Sybase SQL Anywhere to Oracle:

Sybase SQL Anywhere:

   -- Define a type
   CREATE DOMAIN addr VARCHAR(90);
 
   -- Use it in a table
   CREATE TABLE location (address addr);
 
   -- Insert data into user-defined type 
   INSERT INTO location VALUES ('Kansas City');

Oracle:

   -- Define a type
   CREATE TYPE addr AS OBJECT (addr VARCHAR2(90));
   /
 
   -- Use it in a table
   CREATE TABLE location (address addr);
 
   -- Insert data into user-defined type 
   INSERT INTO location VALUES (addr('Kansas City'));