Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1965
  • Last Modified:

how to create a domain using SqlPlus

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
xdizen
Asked:
xdizen
1 Solution
 
XxJayHindxXCommented:
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
 
xdizenAuthor Commented:
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
 
XxJayHindxXCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
xdizenAuthor Commented:
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
 
xdizenAuthor Commented:
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
 
xdizenAuthor Commented:
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
 
XxJayHindxXCommented:
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
 
DonFreemanCommented:
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
 
xdizenAuthor Commented:
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
 
XxJayHindxXCommented:
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
 
patelgokulCommented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now