Oracle 11g case-insensitive searching

[WolfShade]

Hello, all,

I am using Oracle 11g, do not have admin access or authority to make any changes to configuration, and would like to have case-insensitive searches of varchar2 and CLOB columns.

I have tried putting the following before my SELECT query:

ALTER SESSION SET NLS_SORT = BINARY_CI;
ALTER SESSION SET NLS_COMP = LINGUISTIC;

This results in an error message: ORA-00922; missing or invalid option.

Also, the method I'm using for searching the CLOB is case-sensitive:

SELECT * /* I don't use * in production, I'm using it here for brevity */
FROM table1
WHERE dbms_lob.instr(columnA,'searchWord') >= 1

Any advice appreciated.

V/r,

^ _ ^

[EddieLotter]

I'm not familiar with Oracle, but see if you can do something similar to the following:

where LCase(columnA) like '%searchword%'

Cheers

Eddie

[WolfShade]

This will work, but at a huge cost.  Forcing Oracle to convert all text to lowercase is CPU intensive.  And, I'm not sure that would work with CLOB columns.

V/r,

^ _ ^

[WolfShade]

Been doing more Google searching on this subject, and I'm not finding much.  A lot of sites indicate the solution that gives me the "mising or invalid option" error message is the best, but it's not working, for me.

Any other suggestions?

V/r,

^ _ ^