insert query is giving ORA-01722: invalid number error

Question

I created a stock table with the below query

CREATE TABLE stock
  (
    product_id REFERENCES product ,
    product_name REFERENCES product ,
    color    VARCHAR2(20) ,
    memory   VARCHAR2(20) ,
    in_stock NUMBER(3)
  );

I tried to insert a row into this using the below query:

insert into stock values(8881,'Nexus 4','Black','2GB-16GB',3);

and it is giving ORA-01722: invalid number error , I know why the error comes , but I can't figure out what wrong I have done

product table :

product_id ..number , product_name .. varchar2

adding a pic

just do one thing to find the error : insert into stock values except in_stock ..and let me know if error :) — Tharif
insert into stock(product_id , product_name, color , memory) values(8881,'Nexus 4','Black','2GB-16GB') .. it is giving same error invalid number — amol singh
The create table statement you posted isn't valid syntax. The first two columns are missing data types and the foreign key constraints aren't valid syntax. What is the actual definition of the stock table? — Justin Cave
@utility did that ..same error and even tried to add only product_name , color and memory ..still same error — amol singh

Justin Cave Justin Cave · Accepted Answer · 2015-08-04T05:36:17

product_id is the primary key of product and is a number column. When you define a table

CREATE TABLE stock
  (
    product_id REFERENCES product ,
    product_name REFERENCES product ,
    color    VARCHAR2(20) ,
    memory   VARCHAR2(20) ,
    in_stock NUMBER(3)
  );

both product_id and product_name are being implicitly defined as number columns that reference the product_id column from product. Since product_name is defined as a number, you can't insert the string "Nexus 4".

It makes no sense to have a product_name column in the stock table. It already exists in the product table which is where it belongs. It violates basic rules of normalization to store the same information in multiple locations. I would strongly suspect that color and memory ought to exist in the product table as well rather than in the stock table.

Your DDL appears to be valid syntax (rather shockingly) but it is very, very unusual. I've been dealing with Oracle for many years and I've never seen anyone create a table this way. It appears to be valid but it's going to throw off anyone that has to support your application after you. It would be much more reasonable to be explicit

CREATE TABLE stock
  (
    product_id NUMBER REFERENCES product( product_id ) ,
    product_name VARCHAR2(40) , -- This shouldn't be in this table
    color    VARCHAR2(20) , -- Nor should this
    memory   VARCHAR2(20) , -- Nor should this
    in_stock NUMBER(3)
  );

insert query is giving ORA-01722: invalid number error

2 Answers