Hello Everybody!

I am new to Oracle and am trying to create a Script with multiple tables. However, it seems that 2 of my CREATE TABLE statements come up with the error message 'Ora-00907: Missing Right Parenthesis. I don't understand what I am doing wrong, since I have followed the book exactly.

Below is my code:

CREATE TABLE ProbOfficers
( ProbID NUMBER(5),
Last VARCHAR2(15),
First VARCHAR2(10),
Street VARCHAR2(30),
State CHAR(2),
Zip CHAR(5,0),
Phone CHAR(10,0),
Pager# CHAR(10),
Email VARCHAR(30),
Status CHAR (1) DEFAULT 'A');

CREATE TABLE Officers
( OfficerID NUMBER(8,0),
Last VARCHAR2(15),
First VARCHAR2(10),
Precinct CHAR(4),
Badge VARCHAR(14),
Phone CHAR(10,0),
Status CHAR(1) DEFAULT 'A');

Thanks,
WDGirlie

show what exactly is "CHAR(5,0)"?

BTW - Consider using only VARCHAR2 for strings

It's what my book says to do. When you use the CHAR it says you can use a scale for your number of characters. So CHAR(5,0) means only 5 characters, but with a scale of 0.

I am just following the book syntax. I have used that same thing in my other CREATE TABLE statements and they went through just fine.

>I have used that same thing in my other CREATE TABLE statements and they went through just fine.
Please post proof.

I don't know to which book you refer, but for SQL you should be using manual in URL below
Contents

Here is my entire script:

CREATE TABLE Aliases
( AliasID NUMBER(6),
CriminalID NUMBER(6,0),
Alias VARCHAR2(20) );

CREATE TABLE Criminals
( CriminalID NUMBER(6,0),
Last VARCHAR2(15),
First VARCHAR2(10),
Street VARCHAR2(30),
State CHAR(2),
Zip CHAR(5),
Phone CHAR(10),
VStatus CHAR(1) DEFAULT 'N',
PStatus CHAR(1) DEFAULT 'N' );

CREATE TABLE Crimes
( CrimeID NUMBER(9,0),
CriminalID NUMBER(6,0),
Classification CHAR(1) DEFAULT 'U',
DateCharged DATE,
DateRecorded DATE DEFAULT SYSDATE,
Status CHAR(2),
HearingDate DATE,
AppealCutDate DATE );

CREATE TABLE Sentences
( SentenceID NUMBER(6),
CriminalID NUMBER(6),
Type CHAR(1),
ProbID NUMBER(5),
StartDate DATE,
EndDate DATE,
Violations NUMBER(3) );

CREATE TABLE ProbOfficers
( ProbID NUMBER(5),
Last VARCHAR2(15),
First VARCHAR2(10),
Street VARCHAR2(30),
State CHAR(2),
Zip CHAR(5,0),
Phone CHAR(10,0),
Pager# CHAR(10),
Email VARCHAR(30),
Status CHAR (1) DEFAULT 'A');

CREATE TABLE CrimeCharges
( ChargeID NUMBER(10,0),
CrimeID NUMBER(9,0),
CrimeCode NUMBER(3,0),
ChargeStatus CHAR(2),
FineAmount NUMBER(7,2),
CourtFee NUMBER(7,2),
AmountPaid NUMBER(7,2),
PayDueDate DATE);

CREATE TABLE CrimeOfficers
( CrimeID NUMBER(9,0),
OfficerID NUMBER(8,0) );

CREATE TABLE Officers
( OfficerID NUMBER(8,0),
Last VARCHAR2(15),
First VARCHAR2(10),
Precinct CHAR(4),
Badge VARCHAR(14),
Phone CHAR(10,0),
Status CHAR(1) DEFAULT 'A');

CREATE TABLE Appeals
( AppealID NUMBER(5),
CrimeID NUMBER(9,0),
FilingDate DATE,
HearingDate DATE,
Status CHAR(1) DEFAULT 'P' );

CREATE TABLE CrimeCodes
( CrimeCode NUMBER(3,0),
CodeDescription VARCHAR(30) );

BTW I am new to using forums, so I'm not sure what exactly I should include in my posts.

>it seems that 2 of my CREATE TABLE statements come up with the error message 'Ora-00907: Missing Right Parenthesis.
>I don't understand what I am doing wrong, since I have followed the book exactly.

the 2 tables that throw error are the only 2 tables that use CHAR(5,0) incorrect syntax.
Either fix the syntax or live with the error. Either choice is OK for me.

what is difference between CHAR, CHAR2 & VARCHAR2 datatypes?

Okay, I'm sorry. I did not realize that. Thanks for the help. I do appreciate it.

>Zip CHAR(5,0),
>Phone CHAR(10,0),

not as above, but as below

Zip CHAR(5),
Phone CHAR(10),

what is difference between CHAR, CHAR2 & VARCHAR2 datatypes?

VARCHAR2 is variable-length character, max is 4000.

CHAR is fixed character length, max is 2000.

CHAR2 is not defined in my book.

Wow, looking into my book made me realize what a total idiot I was being. I mixed it with the NUMBER datatype.

I do apologize for that. Programming has never been my strong point, and I do seem to notice that I tend to mix certain things up.

I wonder how that string looks like.

Hello Again!

I seem to be having the same error code for the same tables as my original post. However, now I have 2 more tables. I corrected the original issue, but now it's still coming up as 'Missing Pararenthesis'. Can some tell me what is wrong with my syntax? Here it is:

CREATE TABLE Criminals
( CriminalID NUMBER(6,0),
Last VARCHAR2(15),
First VARCHAR2(10),
Street VARCHAR2(30),
City VARCHAR2(20),
State CHAR(2),
Zip CHAR(5),
Phone CHAR(10),
VStatus CHAR(1) DEFAULT 'N',
PStatus CHAR(1) DEFAULT 'N',
CONSTRAINT Criminals_CriminalID_pk PRIMARY KEY (CriminalID),
CONSTRAINT Criminals_VStatus_ck CHECK (VStatus IN 'N','Y'),
CONSTRAINT Criminals_PStatus_ck CHECK (PStatus IN 'N','Y') );

CREATE TABLE ProbOfficers
( ProbID NUMBER(5),
Last VARCHAR2(15),
First VARCHAR2(10),
Street VARCHAR2(30),
State CHAR(2),
Zip CHAR(5),
Phone CHAR(10),
Pager# CHAR(10),
Email VARCHAR2(30),
Status CHAR (1) DEFAULT 'A',
CONSTRAINT ProbOfficers_ProbID_pk PRIMARY KEY (ProbID),
CONTSRAINT ProbOfficers_Status_ck CHECK (Status IN 'A','I') );

CREATE TABLE Officers
( OfficerID NUMBER(8,0),
Last VARCHAR2(15),
First VARCHAR2(10),
Precinct CHAR(4),
Badge VARCHAR2(14),
Phone CHAR(10),
Status CHAR(1) DEFAULT 'A',
CONSTRAINT Officers_OfficerID_pk PRIMARY KEY (OfficerID),
CONSTRAINT Officers_Status_ck CHECK (Status IN 'A','I') );

CREATE TABLE Appeals
( AppealID NUMBER(5),
CrimeID NUMBER(9,0),
FilingDate DATE,
HearingDate DATE,
Status CHAR(1) DEFAULT 'P',
CONSTRAINT Appeals_AppealID_pk PRIMARY KEY (AppealID),
CONSTRAINT Appeals_Status_ck CHECK (Status IN 'P','A','D') );


Please let me know!

Thanks,

WDGirlie

it works for me

So it's probably the program I am using. At least I know I did that right.

Thanks.

Reality check required

Just a hint ...

You should consider reading a book about Oracle (instead of whatever database your's is about)