Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2012
    Posts
    12

    Unanswered: SQL Error: ORA-01722: invalid number

    I am getting the "error SQL Error: ORA-01722: invalid number"
    From what ive read it occurs when you try to insert a string into a numeric column. However I have double checked and I am not doing that.

    My create table is,

    CREATE TABLE Rental(
    RentalID NUMERIC(3,0),
    BookingDate DATE DEFAULT SYSDATE NOT NULL,
    BookingMethod NVARCHAR2(15) NOT NULL,
    Checkout DATE NOT NULL,
    RentDays NUMERIC(2,0),
    Checkin DATE,
    DueDate DATE,
    OdometerIn NUMERIC(6,0),
    FuelLevel VARCHAR2(4),
    Damage CHAR(10),
    Payment_ID NUMERIC(1,0),
    Rego CHAR(6),
    CustomerID NUMERIC(4,0),
    InsuranceID CHAR(1),
    CONSTRAINT Rental_RentalID_pk PRIMARY KEY (RentalID),
    CONSTRAINT Rental_RentalID_ck CHECK (RentalID BETWEEN 0 AND 1000),
    CONSTRAINT Rental_Checkin_ck CHECK (Checkin <= Checkout),
    CONSTRAINT Rental_FuelLevel_ck CHECK (RentalID IN ('1,0.75,0.5,0.25,0')),
    CONSTRAINT Rental_Damage_ck CHECK (Damage IN ('Y','N')),
    CONSTRAINT Rental_Payment_ID_ck CHECK (REGEXP_LIKE (Payment_ID,'[1-9]')),
    CONSTRAINT Rental_Rego_ck CHECK (REGEXP_LIKE (Rego,'[0-9][0-9][0-9][A-Z][A-Z][A-Z]')),
    CONSTRAINT Rental_CustomerID_ck CHECK (CustomerID BETWEEN 999 AND 10000), --This is to create a Between check constraint to help maintain database integrity
    CONSTRAINT Rental_InsuranceID_ck CHECK (REGEXP_LIKE (InsuranceID,'[A-Z]'))
    );



    And the insert command is

    INSERT INTO Rental VALUES (2,'27-Jul-06','In-Store', '20-Aug-09', '7','27-Aug-09','27-Aug-09','10649','0.5','N','2','114KKJ','1000','N' );

    And the error message is

    Error starting at line 267 in command:
    INSERT INTO Rental VALUES (RentalID_seq.NEXTVAL,'27-Jul-06','In-Store', '20-Aug-09', '7','27-Aug-09','27-Aug-09','10649','0.5','N','2','114KKJ','1000','N' )
    Error report:
    SQL Error: ORA-01722: invalid number
    01722. 00000 - "invalid number"
    *Cause:
    *Action:

    I also have Foreign keys referencing the table, would this have any impact?

    Any help greatly appreciated.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    CONSTRAINT Rental_FuelLevel_ck CHECK (RentalID IN ('1,0.75,0.5,0.25,0')),
    Is it for FuelLevel?


    Another issue is that "IN ('1,0.75,0.5,0.25,0')" might be "IN ('1' , '0.75' , '0.5' , '0.25' , '0')".

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Other my questions are...
    (1) Why you inserted character strings into NUMERIC columns except RentalID.
    Though it is not error if the character strings conform to NUMERIC format,
    but why?

    (2) Why FuelLevel is VARCHAR2 and not NUMERIC?
    CHECK constraint suggests(if my guess in previous post was right) FuelLevel contains only numeric values.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I suspected that cause of "ORA-01722: invalid number" might be in conversion to numeric from '1,0.75,0.5,0.25,0' to compare with RentalID.

  5. #5
    Join Date
    Sep 2012
    Posts
    12
    Thanks for looking it over,
    I changed to this

    CONSTRAINT Rental_FuelLevel_ck CHECK (RentalID IN ('1','0.75','0.5','0.25','0')), and got the error msg

    Error starting at line 267 in command:
    INSERT INTO Rental VALUES (2,'27-Jul-06','In-Store', '20-Aug-09', 7,'27-Aug-09','27-Aug-09',10649,'0.5','N',2,'114KKJ',1000,'N' )
    Error report:
    SQL Error: ORA-02290: check constraint (SYSTEM.RENTAL_FUELLEVEL_CK) violated
    02290. 00000 - "check constraint (%s.%s) violated"
    *Cause: The values being inserted do not satisfy the named check

    *Action: do not insert values that violate the constraint.


    And to answer your questions

    1. was fiddling around to make it work, my numeric entries didn't initially have ''

    2. I made it a string because it can only be one of 5 values so i figured it wouldn't be an issue

    Thanks again

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    RentalID was 2.
    It is NOT IN ('1' , '0.75' , '0.5' , '0.25' , '0').

  7. #7
    Join Date
    Sep 2012
    Posts
    12
    Ok so that was this issue, and
    CONSTRAINT Rental_FuelLevel_ck CHECK (FuelLevel IN ('1','0.75','0.5','0.25','0')), fixed it

    I had two other constraint problems but they were easy fixed.
    Thank you very much, the help was greatly appreciated

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >CONSTRAINT Rental_FuelLevel_ck CHECK (FuelLevel IN ('1','0.75','0.5','0.25','0')), fixed it
    in Oracle strings are enclosed using single quote marks while NUMBER do not require them.
    You should NEVER rely upon implicit datatype conversion; as is done in top line above.
    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.

Posting Permissions

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