CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloperidentifier
user2074685
user2074685

Reputation: 29

Invalid identifier when selecting from table with fieldnames in lower case

I imported data from a csv file and created a new table. The first row was the header.

When I am trying to run some simple queries to these tables I get an ORA-00904 invalid identifier error.

The only difference between usual tables and the tables with imported data is that the fieldnames in the latter are in lower case, whereas in usual tables they are always in uppercase.

Could that be a reason for the error?

Upvotes: 1

Views: 2070

Answers (2)

A.B.Cade
A.B.Cade

Reputation: 16915

When you use the "" in your DDL then you make your column names case sensitive.

so if the DDL is like this:

CREATE TABLE "DWH"."TEST_PROBA" ( "credit_num" VARCHAR2(11 BYTE) NOT NULL ENABLE, 
"term_acc_open_date" DATE NOT NULL ENABLE, 
"date_final_pmt_exp" DATE, "dpd_1" VARCHAR2(5 BYTE) NOT NULL ENABLE, 
"dpd_2" VARCHAR2(5 BYTE) NOT NULL ENABLE, 
"dpd_3" VARCHAR2(5 BYTE) NOT NULL ENABLE, 
"dpd_4" VARCHAR2(5 BYTE) NOT NULL ENABLE, 
etc

Then you need to query like this:

SELECT "credit_num", "term_acc_open_date", etc...
FROM "TEST_PROBA"

Upvotes: 3

Ben
Ben

Reputation: 1927

If you create column names in lower case (which can only be done using double-quotes), you have to reference them that way as well.

Due to the way Oracle upper-cases unquoted identifiers, you have to surround your column names with double-quotes everywhere you use them. Quick example using your provided DDL:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE "TEST_PROBA" (
  "credit_num" VARCHAR2(11 BYTE) NOT NULL ENABLE
, "term_acc_open_date" DATE NOT NULL ENABLE
, "date_final_pmt_exp" DATE
, "dpd_1" VARCHAR2(5 BYTE) NOT NULL ENABLE
)
/

INSERT INTO test_proba (
  "credit_num"
, "term_acc_open_date"
, "date_final_pmt_exp"
, "dpd_1"
)
VALUES (
  '12341234123'
, SYSDATE
, SYSDATE+1
, 'foo'
)
/

COMMIT
/

Query 1:

SELECT "credit_num"
FROM test_proba

Results:

|  CREDIT_NUM |
---------------
| 12341234123 |

Note that SELECT "credit_num" works while SELECT credit_num does not, and yields the ORA-00904 error as you mention above.

Upvotes: 1

Related Questions