how to create a domain using SqlPlus

For user defined datatypes, Oracle has CREATE TYPE statement.
i.e.

CREATE TYPE HotelNum AS OBJECT
(HotelNumber CHAR(4));
CREATE TABLE Hotel(HotelNo HotelNum,
                HotelName VARCHAR (20) NOT NULL);
When you want to insert HotelNo value into  table Hotel, you will have to do,
INSERT INTO hotel VALUES (HotelNum('1234'),'The Great Motel');
For more information you can search the Oracle on-line documentation,
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage

Thanks I also have another question

how would you do this in sqlplus (syntax wise)
CREATE TABLE Room(
                   RoomNum  SMALLINT CHECK (VALUE BETWEEN 1 AND 100)
                  );

Thanks

You have to specify the name of the column.
CREATE TABLE Room(
                  RoomNum  SMALLINT CHECK (RoomNum  BETWEEN 1 AND 100));

Just out of curiocity which database are you using mainly (not Oracle)?

I am using oracle
i am used to mysql and the syntax is different into sqlplus

This is what i have so far:
i have all this in a file (test.sql)

CREATE TYPE HotelNumber AS OBJECT
(HotelNumber INTEGER NOT NULL);

CREATE TYPE Name_Of_Hotel AS OBJECT
(Name_Of_Hotel CHAR(50) NOT NULL);

CREATE TYPE Hotel_City AS OBJECT
(Hotel_City  CHAR(50) NOT NULL);

CREATE TYPE GuestNumber AS OBJECT
(GuestNumber INTEGER NOT NULL);

CREATE TYPE Name_Guest AS OBJECT
(Name_Guest CHAR(50) NOT NULL);

CREATE TYPE Address_Guest AS OBJECT
(Address_Guest CHAR(50) NOT NULL);



CREATE TABLE Hotel(
                   HotelNo HotelNumber NOT NULL,
                   HotelName Name_Of_Hotel,
                   City Hotel_City,
                   PRIMARY KEY(HotelNo)
                  );


CREATE TABLE Guest(
                  GuestNo GuestNumber NOT NULL,
                  GuestName Name_Guest NOT NULL,
                  GuestAddress Address_Guest NOT NULL,
                  PRIMARY KEY(GuestNo)
                  );
but when i do start test.sql i get:
38
39 .....

it does not work because if i drop the tables, it tells me the table dont exist

why ?

I am using oracle
i am used to mysql and the syntax is different into sqlplus

This is what i have so far:
i have all this in a file (test.sql)

CREATE TYPE HotelNumber AS OBJECT
(HotelNumber INTEGER NOT NULL);

CREATE TYPE Name_Of_Hotel AS OBJECT
(Name_Of_Hotel CHAR(50) NOT NULL);

CREATE TYPE Hotel_City AS OBJECT
(Hotel_City  CHAR(50) NOT NULL);

CREATE TYPE GuestNumber AS OBJECT
(GuestNumber INTEGER NOT NULL);

CREATE TYPE Name_Guest AS OBJECT
(Name_Guest CHAR(50) NOT NULL);

CREATE TYPE Address_Guest AS OBJECT
(Address_Guest CHAR(50) NOT NULL);



CREATE TABLE Hotel(
                   HotelNo HotelNumber NOT NULL,
                   HotelName Name_Of_Hotel,
                   City Hotel_City,
                   PRIMARY KEY(HotelNo)
                  );


CREATE TABLE Guest(
                  GuestNo GuestNumber NOT NULL,
                  GuestName Name_Guest NOT NULL,
                  GuestAddress Address_Guest NOT NULL,
                  PRIMARY KEY(GuestNo)
                  );
but when i do start test.sql i get:
38
39 .....

it does not work because if i drop the tables, it tells me the table dont exist

why ?

I am using oracle
i am used to mysql and the syntax is different into sqlplus

This is what i have so far:
i have all this in a file (test.sql)

CREATE TYPE HotelNumber AS OBJECT
(HotelNumber INTEGER NOT NULL);

CREATE TYPE Name_Of_Hotel AS OBJECT
(Name_Of_Hotel CHAR(50) NOT NULL);

CREATE TYPE Hotel_City AS OBJECT
(Hotel_City  CHAR(50) NOT NULL);

CREATE TYPE GuestNumber AS OBJECT
(GuestNumber INTEGER NOT NULL);

CREATE TYPE Name_Guest AS OBJECT
(Name_Guest CHAR(50) NOT NULL);

CREATE TYPE Address_Guest AS OBJECT
(Address_Guest CHAR(50) NOT NULL);



CREATE TABLE Hotel(
                   HotelNo HotelNumber NOT NULL,
                   HotelName Name_Of_Hotel,
                   City Hotel_City,
                   PRIMARY KEY(HotelNo)
                  );


CREATE TABLE Guest(
                  GuestNo GuestNumber NOT NULL,
                  GuestName Name_Guest NOT NULL,
                  GuestAddress Address_Guest NOT NULL,
                  PRIMARY KEY(GuestNo)
                  );
but when i do start test.sql i get:
38
39 .....

it does not work because if i drop the tables, it tells me the table dont exist

why ?

In your sql script you cannot put ";" You have to put "/"
Create test.sql as below.

CREATE TYPE HotelNumber AS OBJECT
(HotelNumber INTEGER)
/
CREATE TYPE Name_Of_Hotel AS OBJECT
(Name_Of_Hotel CHAR(50))
/
CREATE TYPE Hotel_City AS OBJECT
(Hotel_City  CHAR(50))
/
CREATE TYPE GuestNumber AS OBJECT
(GuestNumber INTEGER)
/
CREATE TYPE Name_Guest AS OBJECT
(Name_Guest CHAR(50))
/
CREATE TYPE Address_Guest AS OBJECT
(Address_Guest CHAR(50))
/
CREATE TABLE Hotel(
                  HotelNo HotelNumber NOT NULL,
                  HotelName Name_Of_Hotel,
                  City Hotel_City,
                  PRIMARY KEY(HotelNo)
                 )
/
CREATE TABLE Guest(
                 GuestNo GuestNumber NOT NULL,
                 GuestName Name_Guest NOT NULL,
                 GuestAddress Address_Guest NOT NULL,
                 PRIMARY KEY(GuestNo)
                 )
/
To run the test.sql you can also just type, @test
at the sql*plus prompt (you do not need to specify .sql).
Also the idea of creating a different type for every column sounds unpractical to me, so I suggest you create a type Hotel that has all the related columns. i.e.,
Create TYPE hotel AS OBJECT
(HotelNumber INTEGER,
 Name_Of_Hotel CHAR(50),
 Hotel_City  CHAR(50))
/
CREATE TYPE tbl_hotel AS TABLE OF hotel
/
To see a practical example I strongly recommend you check
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c11ordb.htm#427515
You need to have username/passwd to access this site, but the registration is free, AND THE ACCESS TO THE SITE IS THE MOST USEFUL.

According to "Advanced SQL Programming" , Scott Urman, pg 621 it says, "Unlike record fields, the attributes of an object type can not be constrained to be NOT NULL, initialized to a default value, or declared using %TYPE.

NEDSSARD-DFREEMAN> CREATE TYPE HotelNumber AS OBJECT
  2  (HotelNumber INTEGER);
  3  /

Type created.

Thanks for your help
Just a few more things and i ll give you the points
I decided to get rid off the type since it makes the database more complicated, i do not need alias i decided to declare my variables as i go along
now i am trying to create this tables but sqlplus does not accept my nested query

CREATE TABLE Room(
RoomNumber SMALLINT CHECK(RoomNumber BETWEEN 1 AND 100),
HotelNum   CHAR(4)  NOT NULL  CHECK (HotelNum IN (SELECT HotelNumber FROM
Hotel)),
RoomType   CHAR(1)  NOT NULL  CHECK (RoomType IN ('S','D', 'F')
Price     DECIMAL(3,2)  NOT NULL  CHECK (Price BETWEEN 1 AND 100),
PRIMARY KEY (RoomNumber, HotelNum),
FOREIGN KEY (HotelNum) REFERENCES hotelNum
ON DELETE NO ACTION ON UPDATE CASCADE
);
 any idea on how to change the syntax ?

thanks

No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: XxJayHindxX {http:#7994284}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

patelgokul
EE Cleanup Volunteer