Results 1 to 15 of 15

Thread: Ora-00907 Help

  1. #1
    Join Date
    Feb 2012
    Posts
    7

    Red face Unanswered: Ora-00907 Help

    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

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> 
    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');SQL>   2    3    4    5    6    7    8    9   10   11  
    Zip CHAR(5,0),
              *
    ERROR at line 7:
    ORA-00907: missing right parenthesis
    show what exactly is "CHAR(5,0)"?

    BTW - Consider using only VARCHAR2 for strings
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2012
    Posts
    7
    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.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >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
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Feb 2012
    Posts
    7
    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.
    Last edited by WDGirlie; 02-08-12 at 00:31. Reason: Forgot Something

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >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?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Feb 2012
    Posts
    7
    Okay, I'm sorry. I did not realize that. Thanks for the help. I do appreciate it.

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >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?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Feb 2012
    Posts
    7
    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.

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by WDGirlie
    So CHAR(5,0) means only 5 characters, but with a scale of 0.
    I wonder how that string looks like.

  11. #11
    Join Date
    Feb 2012
    Posts
    7
    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
    Last edited by WDGirlie; 02-15-12 at 14:56. Reason: Add code

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    it works for me
    Code:
     1  CREATE TABLE Criminals
      2  ( CriminalID NUMBER(6,0),
      3  Last VARCHAR2(15),
      4  First VARCHAR2(10),
      5  Street VARCHAR2(30),
      6  City VARCHAR2(20),
      7  State CHAR(2),
      8  Zip CHAR(5),
      9  Phone CHAR(10),
     10  VStatus CHAR(1) DEFAULT 'N',
     11  PStatus CHAR(1) DEFAULT 'N',
     12  CONSTRAINT Criminals_CriminalID_pk PRIMARY KEY (CriminalID),
     13  CONSTRAINT Criminals_VStatus_ck CHECK (VStatus IN ('N','Y')),
     14* CONSTRAINT Criminals_PStatus_ck CHECK (PStatus IN ('N','Y')))
    SQL> /
    
    Table created.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  13. #13
    Join Date
    Feb 2012
    Posts
    7
    So it's probably the program I am using. At least I know I did that right.

    Thanks.

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Reality check required
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  15. #15
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    Just a hint ...

    You should consider reading a book about Oracle (instead of whatever database your's is about)
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •