Re: Bind Variables ignoring Index on 10.2.0.4

>
> With the risk of making a stupid remark (I don't know anything about
> NLS_SORT in create_index):
> Is there any reason why you do (NLSSORT("sys_id",'nls_sort=''BINARY_CI'''))
> (double quotes, case DOES matter here)
> and not
> (NLSSORT(SYS_ID,'nls_sort=''BINARY_CI'''));?? (no double quotes, case
> doesn't matter now)
> To my knowledge (but again...) a column name in lower char and double quotes
> does not resolve to the actual column.
>
> I may be wrong, but your remark about a typo made me wonder....
>
> BTW Same goes for "BINARY_CI": why those double quotes?
>
> Shakespeare

No worries at all about pointing out a stupid error on my part. Wouldn't be the least bit shocked if that isn't the underlying problem, and if it is I'll throw a party celebrating my own stupidity as long as it fixes the problem :).

The reason sys_id is being quoted is that the actual column name is (lower case) sys_id.

This is an old mySQL app that got ported to Oracle and the column names are all lower cased there since thats "more or less" the norm in mySQL land.

Hence when it came to oracle, the column names were, unfortunately still lower cased, and hence the necessity of quoting all references to them, which makes working with them on the CLI rather obnoxious.

SQL> describe sys_trigger;
 Name Null? Type

• --------

  ---

  sys_id NOT NULL CHAR(32) name VARCHAR2(40) trigger_class VARCHAR2(40) state NUMBER(38) next_action TIMESTAMP(6) document VARCHAR2(40) document_key CHAR(32) job_id CHAR(32) job_context VARCHAR2(4000) calendar CHAR(32) log NUMBER(1) maintenance NUMBER(1) trigger_type NUMBER(38) repeat TIMESTAMP(6) run_time TIMESTAMP(6) run_dayofweek NUMBER(38) run_dayofmonth NUMBER(38) run_weekinmonth NUMBER(38) run_month NUMBER(38) application VARCHAR2(40) script CLOB system_id VARCHAR2(100) claimed_by VARCHAR2(100) sys_updated_by VARCHAR2(40) sys_updated_on TIMESTAMP(6) sys_created_by VARCHAR2(40) sys_created_on TIMESTAMP(6) sys_mod_count NUMBER(38)