SQL ORA-01722: invalid number

user_8937934 0
Viewed 121

Having trouble while using Oracle Application Express. I'm currently learning Database management in college and using APEX. All customer information was created using a generator so no personal information is in here.

The issue I'm having is caused when I try to run the script to insert information into the walks table. I get an invalid number error. 

INSERT INTO WALKS
VALUES (TIMESTAMP '2018-3-12 20:42:43', TIMESTAMP '2018-3-12 21:42:43', '13-JUN-18', 'San Antonio', 'TX', 1, 537028782);
INSERT INTO WALKS
VALUES (TIMESTAMP '2018-2-13 17:51:45', TIMESTAMP '2018-2-13 18:51:45', '10-OCT-18', 'San Antonio', 'TX', 2, 388191065);

The Create Table I used was

CREATE TABLE Walks
(StartTime TIMESTAMP NOT NULL,
EndTime TIMESTAMP,
WalkDate INTEGER NOT NULL,
City VARCHAR2(15) NOT NULL,
State VARCHAR2(2) NOT NULL,
CustomerID INTEGER NOT NULL,
WalkerID VARCHAR2(9) NOT NULL,
CONSTRAINT Walk_Cust_fk FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
CONSTRAINT Walk_Wlkr_fk FOREIGN KEY (WalkerID) REFERENCES Walkers(SocSecNum),
CONSTRAINT Walk_pk PRIMARY KEY (CustomerID, WalkerID));

CustomerID is an integer NOT NULL and WalkerID is a varchar2(9). If that helps at all.

Just can't figure out when when I run this I get this error. Any help is appreciated.

sql database
1 Answers

You have declared WalkDate as an integer. I assume you mean date:

CREATE TABLE Walks (
    StartTime TIMESTAMP NOT NULL,
    EndTime TIMESTAMP,
    WalkDate INTEGER NOT NULL,
    City VARCHAR2(15) NOT NULL,
    State VARCHAR2(2) NOT NULL,
    CustomerID INTEGER NOT NULL,
    WalkerID VARCHAR2(9) NOT NULL,
    CONSTRAINT Walk_Cust_fk FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    CONSTRAINT Walk_Wlkr_fk FOREIGN KEY (WalkerID) REFERENCES Walkers(SocSecNum),
    CONSTRAINT Walk_pk PRIMARY KEY (CustomerID, WalkerID)   
);

I would recommend writing the INSERT as:

INSERT INTO WALKS (StartTime, EndTime, WalkDate, City, State, CustomerId, WalkerId)
    VALUES (TIMESTAMP '2018-3-12 20:42:43', TIMESTAMP '2018-3-12 21:42:43', DATE '2018-06-13', 'San Antonio', 'TX', 1, 537028782);

The use of the DATE keyword means that you don't have to rely on system date format settings. Listing all the columns is simply a best practice when using INSERT.

user_11440350
Related