ORA-00904 when inserting/querying data from tables

Question

I'm getting the flowing error:

Error starting at line 1 in command:
INSERT INTO driver (registration, make, model, gvw, year, body) VALUES('4585 AW','ALBION','RIEVER',20321,1963, ' ');
Error at Command Line:1 Column:53
Error report:
SQL Error: ORA-00904: "BODY": invalid identifier
00904. 00000 -  "%s: invalid identifier"

When I do the following

INSERT INTO driver 
(registration, make, model, gvw, year) 
VALUES
('4585 AW','ALBION','RIEVER',20321,1963, ' ');

So I temporally deleted the body data and then give the error

Error starting at line 1 in command:
INSERT INTO driver (registration, make, model, gvw, year) VALUES('4585 AW','ALBION','RIEVER',20321,1963)
Error at Command Line:1 Column:53
Error report:
SQL Error: ORA-00904: "YEAR": invalid identifier
00904. 00000 -  "%s: invalid identifier"

I have columns both called year and body, yet I'm getting errors. How can I fix this?

http://stackoverflow.com/questions/1162381/how-do-i-escape-a-reserved-word-in-oracle — Laurence, Nov 10 '12 at 23:46
so how do i get around this, I'm fairly new to ocracle and sql — Michael, Nov 10 '12 at 23:56
@MitchWheat more like OP running into Oracle's case sensitive nature when identifiers are wrapped in quotes — Sathyajith Bhat, Nov 11 '12 at 06:16

score 10 · Answer 1 · answered Nov 11 '12 at 06:15

Seems like you specified the column names under quotes in your DDL.

Oracle considers identifiers under quotes as a case sensitive, so creating a table with a column name as "body" or "year" will not resolve body or year in your query, because when you omit the quotes, it considers the upper case variant.

So when you have this:

 create table driver_case (registration varchar2(60), 
                      make varchar2(60),
                      model varchar2(60),
                      gvw number,
                      "year" number, 
                      "body" varchar2(60));

and then try to do

select year, body from driver_case

then Oracle tries to find column "YEAR", "BODY" (remember, without an identifier without quotes gets converted to upper case) which is not the same as "year", "body" in your table.

Your solutions?

Don't mention column names under quotes in the DDL
If you disregard the above point, then you must mention the column names under quotes in all your DML statements.

I demonstrate the above point in this SQL Fiddle

score 0 · Answer 2 · edited Mar 19 '18 at 13:33

0

Try this:

INSERT
    WHEN city='palanpur' THEN INTO Ashish 
    When city='Aburoad' THEN INTO abhi
SELECT no, name
    FROM arpit;

edited Mar 19 '18 at 13:33

Impulse The Fox

2,638
2
27
52

answered Mar 19 '18 at 12:39

Ashish Kumar jain

1

1

What does that mean? – Nico Haase Mar 19 '18 at 13:00

ORA-00904 when inserting/querying data from tables

2 Answers2

Linked