?
Solved

how to create a domain using SqlPlus

Posted on 2003-02-21
12
Medium Priority
?
1,924 Views
Last Modified: 2012-08-13
Hi all
I am trying to do the following in Sqlplus on Oracle:

CREATE DOMAIN HotelNumber CHAR (4);

CREATE TABLE Hotel(
                  HotelNo HotelNumber,
                  HotelName VARCHAR (20) NOT NULL,
                  ....);

The only problem: there is no CREATE DOMAIN in SqlPlus
I know i need to specify the domain next to each field in the table but i do not know the correct syntax

can anyone help ?

Thanks
0
Comment
Question by:xdizen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 

Expert Comment

by:XxJayHindxX
ID: 7994284
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
0
 

Author Comment

by:xdizen
ID: 7994995
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
0
 

Expert Comment

by:XxJayHindxX
ID: 7995082
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)?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:xdizen
ID: 7995117
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 ?


0
 

Author Comment

by:xdizen
ID: 7995142
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 ?


0
 

Author Comment

by:xdizen
ID: 7995153
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 ?


0
 

Expert Comment

by:XxJayHindxX
ID: 7995457
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.
0
 
LVL 1

Expert Comment

by:DonFreeman
ID: 7995504
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.
0
 

Author Comment

by:xdizen
ID: 7995745
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


0
 

Accepted Solution

by:
XxJayHindxX earned 280 total points
ID: 7997325
You made a real good decision by not implementing types. It would've made things unnecessarily
complicated when there was no need for it. Also remember you can never make the type you create
the primary key in Oracle (as of Oracle 9.2)
About Oracle not accepting your nested query, well the rule is that you cannot put queries/subqueries
in your check constraints. And in your
case you have already made the smart move and implemented that same logic by trying to make
Hotelnum a foreign key. The syntax to create the foreign key is slightly wrong as you should
reference the table, so
CREATE TABLE Room(
RoomNumber SMALLINT CHECK(RoomNumber BETWEEN 1 AND 100),
HotelNum   CHAR(4)  NOT NULL,
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),
CONSTRAINT fk_room FOREIGN KEY(HotelNum) REFERENCES hotel);

As a good practice you should always name your constraints so that if the constraint fails you
have an idea on which column to look for the error. If you do not want any action then simply do not
specify it at all. Also, there is no ON UPDATE CASCADE in Oracle so you write a trigger instead, that updates the changes in the tables that have the foreign key.
0
 
LVL 3

Expert Comment

by:patelgokul
ID: 9927424
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
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question