2 Replies Latest reply: Oct 17, 2009 2:05 PM by P.Forstmann RSS

    What is the syntax for CREATE DOMAIN in Oracle10g?

    wyfwong
      Does anyone know how to convert the following SQL3 CREATE DOMAIN statements to equivalent Oracle10g Object-relational statements?
      CREATE DOMAIN RoomType AS CHAR(1)
      CHECK(VALUE IN (‘S’, ‘F’, ‘D’));
      CREATE DOMAIN HotelNumbers AS HotelNumber
      CHECK(VALUE IN (SELECT hotelNo FROM Hotel));
      CREATE DOMAIN RoomPrice AS DECIMAL(5, 2)
      CHECK(VALUE BETWEEN 10 AND 100);
      CREATE DOMAIN RoomNumber AS VARCHAR(4)
      CHECK(VALUE BETWEEN ‘1’ AND ‘100’);
      CREATE DOMAIN HotelNumber AS CHAR(4);
      CREATE DOMAIN GuestNumber AS CHAR(4);
      CREATE DOMAIN BookingDate AS DATETIME
      CHECK(VALUE > CURRENT_DATE);
        • 1. Re: What is the syntax for CREATE DOMAIN in Oracle10g?
          728511
          i'm with the same problem.

          i tried to do something like that:

          CREATE OR REPLACE TYPE my_type AS OBJECT (
          my_type CHAR(11));

          this works fine, but when i try to create a table with that type, oracle 10g notice me a 'unknown type error':

          CREATE TABLE my_table (
          id NUMBER(10),
          name my_type);

          any idea?
          • 2. Re: What is the syntax for CREATE DOMAIN in Oracle10g?
            P.Forstmann
            Try:
            SQL> select * from v$version;
            
            BANNER
            ----------------------------------------------------------------
            Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
            PL/SQL Release 10.2.0.1.0 - Production
            CORE     10.2.0.1.0     Production
            TNS for Linux: Version 10.2.0.1.0 - Production
            NLSRTL Version 10.2.0.1.0 - Production
            
            SQL> 
            SQL> drop table my_table;
            
            Table supprimee.
            
            SQL> 
            SQL> create or replace type my_type as object
              2  ( my_col char(11))
              3  /
            
            Type cree.
            
            SQL> 
            SQL> create table my_table(
              2  id number(10),
              3  name my_type)
              4  /
            
            Table creee.
            
            SQL> 
            SQL> desc my_table;
             Nom                            NULL ?   Type
             ----------------------------------------- -------- ----------------------------
             ID                                  NUMBER(10)
             NAME                                  MY_TYPE
            
            SQL> 
            SQL> insert into my_table values(1, my_type('ONE'));
            
            1 ligne creee.
            
            SQL> select * from my_table;
            
                 ID
            ----------
            NAME(MY_COL)
            --------------------------------------------------------------------------------
                  1
            MY_TYPE('ONE         ')
            
            
            SQL>