1

Why does the following statement

select * from expertrule where rule_id = '1';

Return this error

ORA-00904: "RULE_ID": invalid identifier

While

select * from expertrule where "rule_id" = '1';

works!

The same goes for every column of the table (and only this table). All the columns in this particular table require quotes surrounding their names. Why?

  CREATE TABLE "TEST"."expertrule" 
   (    "rule_id" NUMBER(*,0), 
    "name" VARCHAR2(256 BYTE), 
    "description" VARCHAR2(256 BYTE), 
    "ruleaccess" VARCHAR2(4000 BYTE), 
    "ruledisable" VARCHAR2(4000 BYTE), 
    "userssn" VARCHAR2(256 BYTE), 
    "departmentid" VARCHAR2(256 BYTE), 
    "active" CHAR(1 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TESTTABLESPACE" ;
  • 2
    Never use double quotes when creating objects! It will only lead to pain. Just an observation too... Be careful when dealing with SSNs - don't store them unless you absolutely have to – Philᵀᴹ Apr 24 '14 at 10:34
7

You created your column with double quotes, so now Oracle requires you to always refer to this column using identifiers and respecting the case. If you remove the double quotes, Oracle always transforms the name to upper case, so without them it is case insensitive.

This will work if you created the column without identifiers:

where rulE_id
where RULE_ID

Also when creating a table, quotes are not required, unless you use reserved words.

  • Thanks! It was an automatically generated script, I'll take better care next time. – Karlth Apr 24 '14 at 11:41
  • 1
    Just a note for future visitors: I've also had this problem in the past with tables created through ODBC (export from Access...) – Colin 't Hart Apr 24 '14 at 11:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.