Oracle 11g invalid identifier error

Question

This query works in Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi but not Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

select * 
  from inb_dosya_turu dosya_turu
 where dosya_turu.adi like 'S%'
   or dosya_turu.id in (select 350 from dual)

What is the problem?

Does the table inb_dosya_turu exist and does it have the columns adi varchar2 and id number? — Ben
@muratto12: Does it give an error message? If yes, what message? Or does it return unexpected data? If yes, what data do you expect and what do you get? Please be more specific. — Codo
In what way does it not work? Please describe the behaviour, including any error messages. — APC
Why this or dosya_turu.id in (select 350 from dual) and why not or dosya_turu.id = 350 or or dosya_turu.id in (350)? Just curious — AnBisw
There's nothing wrong with these queries. There's something you're missing in your description, I'll bet. Changing the "or" into "and" should make no difference to whether you get ORA-00904 or not. — Jeffrey Kemp

muratto12 muratto12 · Accepted Answer · 2012-09-21T05:27:53

I realized that this problem occurs because of language setting. The script for language settings is below. After I upgrade 11.2.0.3, the script has started to work correctly. I changed the query to be queried in SCOTT schema

alter session set nls_sort = 'TURKISH_AI';
alter session set nls_comp = 'linguistic';

select * from SCOTT.BONUS bonusa
where bonusa.ename like 'S%'
or bonusa.JOB in (select 350 from dual)

Oracle 11g invalid identifier error

1 Answers