How to solve this "Identifier too long" error?

sachitad · #1 (**permalink**) 01-01-12, 08:20

Code:

INSERT ALL
   INTO product VALUES (3230, 11, "Speed=2.2ghz hardrive=160GB Processor=Dual Core Screen Size=14icnhes", "Laptop", 25000)
   INTO product VALUES (6689, 10, "Laser printer with single input", "Laser printer", 10000)
   INTO product VALUES (191,  13, "Optical Mouse", "Mouse", 500)
   INTO product VALUES (1314, 12, "Speed=2.2ghz hardrive=500GB Processor=Core I5 Screen Size=15icnhes", "PC", 50000)
   INTO product VALUES (1315, 12, "Speed=2.8ghz hardrive=500GB Processor=Core I5 Screen Size=17icnhes", "Laptop", 60000)
   INTO product VALUES (131,  12, "2GB Flash Drive", "USB drives", 500)
   INTO product VALUES (4432, 14, "Speed=2.4ghz hardrive=250GB Processor=Core I5 Screen Size=16icnhes", "Laptop", 60000)
   INTO product VALUES (9908, 11, "Speed=2ghz hardrive=250GB Processor=Core I7 Screen Size=13icnhes", "PC", 65000)
   INTO product VALUES (6690, 14, "Laser printer with double input", "Laser printer", 15000)
   INTO product VALUES (6691, 10, "Classic Inkjet printer", "Inkjet printer", 10000)
   INTO product VALUES (6692, 13, "Dot Printer with toner", "Dot printer", 10000)
   INTO product VALUES (6693, 10, "Dot Matrix Printer economic", "Dot Matrix printer", 10000)
   INTO product VALUES (191,  11, "3.2 MP Webcam with in-build headphone", "Webcam", 2200)
   INTO product VALUES (192,  14, "Printer Catridge", "Printer accessories", 1500)
   INTO product VALUES (193,  13, "Wireless mouse", "Mouse", 1000)
SELECT * FROM dual;

This is my insert statement. I have defined 255 varchar for prod_desc table. When I run this query I get this error::

ORA-00972: identifier is too long

anacedent · #2 (**permalink**) 01-01-12, 10:56

>INTO product VALUES
>I have defined 255 varchar for prod_desc table.

which table PRODUCT or PROD_DESC has a problem?

post CREATE TABLE statements for both tables

sachitad · #3 (**permalink**) 01-02-12, 02:07

Product table::

Code:

CREATE TABLE product (
        model_no NUMBER NOT NULL,          
	manufacturer_no NUMBER,
	product_desc VARCHAR2(255),
        product_type VARCHAR(10),
        unit_price NUMBER,
        CONSTRAINT product_pk PRIMARY KEY(model_no)
);

Trying to insert data in product table but got error:

Code:

INSERT ALL
   INTO product VALUES (3230, 11, "Speed=2.2ghz hardrive=160GB Processor=Dual Core Screen Size=14icnhes", "Laptop", 25000)
   INTO product VALUES (6689, 10, "Laser printer with single input", "Laser printer", 10000)
   INTO product VALUES (191,  13, "Optical Mouse", "Mouse", 500)
   INTO product VALUES (1314, 12, "Speed=2.2ghz hardrive=500GB Processor=Core I5 Screen Size=15icnhes", "PC", 50000)
   INTO product VALUES (1315, 12, "Speed=2.8ghz hardrive=500GB Processor=Core I5 Screen Size=17icnhes", "Laptop", 60000)
   INTO product VALUES (131,  12, "2GB Flash Drive", "USB drives", 500)
   INTO product VALUES (4432, 14, "Speed=2.4ghz hardrive=250GB Processor=Core I5 Screen Size=16icnhes", "Laptop", 60000)
   INTO product VALUES (9908, 11, "Speed=2ghz hardrive=250GB Processor=Core I7 Screen Size=13icnhes", "PC", 65000)
   INTO product VALUES (6690, 14, "Laser printer with double input", "Laser printer", 15000)
   INTO product VALUES (6691, 10, "Classic Inkjet printer", "Inkjet printer", 10000)
   INTO product VALUES (6692, 13, "Dot Printer with toner", "Dot printer", 10000)
   INTO product VALUES (6693, 10, "Dot Matrix Printer economic", "Dot Matrix printer", 10000)
   INTO product VALUES (191,  11, "3.2 MP Webcam with in-build headphone", "Webcam", 2200)
   INTO product VALUES (192,  14, "Printer Catridge", "Printer accessories", 1500)
   INTO product VALUES (193,  13, "Wireless mouse", "Mouse", 1000)
SELECT * FROM dual;

Im using oracle 10g express edition.
Another query:
Do you think I need another product_no coloum in product table ..Can model_no server as primary key?

flyboy · #4 (**permalink**) 01-02-12, 02:57

Maybe, you should familiarize yourself with basic Oracle SQL elements. They are described in SQL Language Reference book, which is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/

String literals are enclosed in single quotes ('), while identifiers (table/view/column/... names) are either not enclosed at all (e.g. table name PRODUCT in your code) or, when containing special characters, they shall be enclosed in double quotes (").
So, "Speed=2.2ghz hardrive=160GB Processor=Dual Core Screen Size=14icnhes" is supposed to be identifier (column name), however it is impossible as they are limited to be 30 characters long. It is not present in DUAL table anyway. But this string shall represent a string literal, shall not it? If so, just enclose it in correct quotes.

And yes, your feedback (in all threads) is appreciated even when you solve the problem.

Quote:

Originally Posted by sachitad

Another query:
Do you think I need another product_no coloum in product table ..Can model_no server as primary key?

It depends on the system, which is represented by this data. You may read about some points e.g. in these thread on AskTom:
http://asktom.oracle.com/pls/asktom/...40000346704229
http://asktom.oracle.com/pls/asktom/...18800346339210
If it is unique (each value represents at most one row), mandatory (NOT NULL) and immutable (will never change), you may use it as primary key. In fact, you already did it.

sachitad · #5 (**permalink**) 01-02-12, 06:13

Thank you so much! Working now! Thnx for the reference too!!
I am new to Oracle.

Internet Services

Web Development

Digital Marketing

Consumer Tech