CODEWITHSUNDEEP
sqloracle-database
Vikash Sahu
Vikash Sahu

Reputation: 21

I'm trying to create a simple table but it's giving me an error

CREATE TABLE City
(
      Id    INT(11),        
      Name  CHAR(35),
      CountryCode  CHAR(3),
      District  CHAR(20),
      Population   INT(11)
  );

SELECT * FROM City;

It shows the error as :

ERROR at line 3: 
Id INT(11), 
ORA-00907: missing right parenthesis 
6 Rotterdam NLD Zuid-Holland 593321 
3878 Scottsdale USA Arizona 202705 
3965 Corona USA California 124966 
3973 Concord USA California 121780 
3977 Cedar Rapids USA Iowa 120758 
3982 Coral Springs USA Florida 117549 
4054 Fairfield USA California 92256 
4058 Boulder USA Colorado 91238 
4061 Fall River USA Massachusetts 90555

Upvotes: 2

Views: 70

Answers (3)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

The actual error is :

INT(11)

In Oracle, an INTEGER is an ANSI SQL data type which refers to numeric values which have only an integer portion and no floating point or decimal part. That is, an INTEGER will only store whole numbers.

So, you could create your table if you do not mention any precision or scale.

For example,

SQL> CREATE TABLE City
  2  (
  3        Id    INT,
  4        Name  CHAR(35),
  5        CountryCode  CHAR(3),
  6        District  CHAR(20),
  7        Population   INT
  8    );

Table created.

SQL>

Now coming to few observations:

Do not use CHAR data type. a CHAR type always blank pads the resulting string out to a fixed width, we discover rapidly that it consumes maximum storage both in the table segment and any index segments. That would be bad enough, but there is another important reason to avoid CHAR/NCHAR types: they create confusion in applications that need to retrieve this information (many cannot find their data after storing it). The reason for this relates to the rules of character string comparison and the strictness with which they are performed.

Very well explained by Tom Kyte here.

I would suggest:

CREATE TABLE city
  (
    id          NUMBER (11) PRIMARY KEY,
    nm          VARCHAR2(35),
    countrycode VARCHAR2(3),
    district    VARCHAR2(20),
    population  NUMBER(11)
  );

Upvotes: 3

bob dylan
bob dylan

Reputation: 1498

Read the following for a list of datatypes and their properties

http://www.techonthenet.com/oracle/datatypes.php

The following will work:

CREATE TABLE City
(
      Id    NUMBER(11),        
      Name  VARCHAR2(35),
      CountryCode  VARCHAR2(3),
      District  VARCHAR2(20),
      Population   NUMBER(11)
  );

Upvotes: 0

The Reason
The Reason

Reputation: 7973

CREATE TABLE City( 
  city_id            NUMBER PRIMARY KEY,
  Name               VARCHAR2(30),
  CountryCode        VARCHAR2(40),
  District           VARCHAR2(20),
  Population         NUMBER
)

Here is your table all work fine Fiddle

Upvotes: 0

Related Questions