Geek Answers Handbook

Paste query gives ORA-01722: invalid number error

I created a stock table with the following 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 string into this using the following query:

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

and it gives ORA-01722: invalid number error , I know why the error occurs, but I can’t understand what I did wrong

product table:

 product_id ..number , product_name .. varchar2

adding pic

enter image description here

+4
source share
2 answers

product_id is the primary key of product and is the column number . 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 implicitly defined as number columns that reference the product_id column from product . Since product_name defined as number , you cannot 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 where it belongs. It violates the basic rules of normalization for storing the same information in several places. I would strongly suspect that color and memory should exist in the product table, and not in the stock table.

Your DDL seems to be valid syntax (quite shocking), but it is very, very unusual. I have been doing Oracle for many years, and I have never seen anyone create a table this way. It seems valid, but it is going to reset all who should support your application after you. It would be much wiser 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) );
+5
source

The definition for references indicates that your syntax is invalid: http://psoug.org/definition/REFERENCES.htm .

How to find out which column in the products should it fit? You have to say it.

Go to one of the tables you created using this method and script, and you will see that you need to specify a column. You will not find creation scripts without a column in the links

Your DDL does not match the screenshot. The screenshot has Category_ID , and your DDL is not working.

If you ran this INSERT example without a list of the target column versus a screenshot of the table, this explains your error.

Never encode an insert without a list of target columns. He offers errors. (not to mention that it looks like you are lazy)

+1
source

More articles:


All Articles