brainmassage brainmassage - 18 days ago 3
SQL Question

Why do I get invalid number from this query?

I get "ORA-01722: invalid number" from this query:

INSERT INTO OLD_MATCHES
(ID, LEAGUE, WEEK, MATCH_DATE, HOME, AWAY, HOME_GOALS, AWAY_GOALS, HOME_BET, DRAW_BET, AWAY_BET, LEAGUE_SEASON)
VALUES
(OLD_MATCHES_SEQ.NEXTVAL, 240, 11, '26-02-2016', 'Chelsea', 'Arsenal', 3, 0, 2, 3, 4, 'Premier league');


I looked at this explanation at Oracle FAQ:

An ORA-01722 ("invalid number") error occurs when an attempt is made to convert a character string into a number, and the string cannot be converted into a valid number. Valid numbers contain the digits '0' through '9', with possibly one decimal point, a sign (+ or -) at the beginning or end of the string, or an 'E' or 'e' (if it is a floating point number in scientific notation). All other characters are forbidden.

But it didn't make sense to me.

sequence script:

CREATE SEQUENCE FOOTBALL.OLD_MATCHES_SEQ
START WITH 12
MAXVALUE 999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;


Table script:

CREATE TABLE FOOTBALL.OLD_MATCHES
(
ID NUMBER NOT NULL,
LEAGUE NUMBER,
WEEK NUMBER,
MATCH_DATE DATE,
HOME VARCHAR2(100 BYTE),
AWAY VARCHAR2(100 BYTE),
HOME_GOALS NUMBER,
AWAY_GOALS NUMBER,
HOME_BET NUMBER(5,2),
AWAY_BET NUMBER(5,2),
DRAW_BET NUMBER(5,2),
LEAGUE_SEASON NUMBER
)

Answer

It's nothing to do with the sequence. According to your table definition, league_season is a number field; you're trying to insert a string, 'Premier league'.

You might need to change the table definition, but it looks more likely you're expecting to have a season number in there, probably a year:

INSERT INTO OLD_MATCHES 
(ID, LEAGUE, WEEK, MATCH_DATE, HOME, AWAY, HOME_GOALS, AWAY_GOALS, 
  HOME_BET, DRAW_BET, AWAY_BET, LEAGUE_SEASON)
VALUES
(OLD_MATCHES_SEQ.NEXTVAL, 240, 11, TO_DATE('26-02-2016', 'DD-MM-YYYY'),
  'Chelsea', 'Arsenal', 3, 0, 2, 3, 4, 2015);

Also '26-02-2016' is not a date, it's a string. Either use an ANSI date literal like date '2016-02-26', or use to_date() with a suitable format mask as I have above. Don't rely on implicit conversion and NLS settings. That isn't causing this error, but it will cause you a problem one day.

Comments